Home » RDBMS Server » Server Utilities » Import Tables from SYSTEM Tablespace (urgent)
Import Tables from SYSTEM Tablespace (urgent) [message #72546] Tue, 15 July 2003 02:18 Go to next message
Deepak Bisht
Messages: 13
Registered: October 2001
Junior Member
I have exported Tables from Schema old_schema, created in SYSTEM tablespace and now i want to import the same tables into Schema old_schema having tablespace name Old_schema_tbs(new tablespace). for this i tried following commands.
1.I export schema(user) old_schema into data.dmp
2.I drop the schema(user) (drop old_schema cascade)
3.I create tablespace Old_schema_tbs
4.I create schema(user) in tablespace Old_schema_tbs
5.i grant dba,resource,connect to old_schema.
6.i give command alter user old_schema quota 0M on system.
7.i give command alter user old_schema quota unlimited on old_schema_tbs;
8.i export the data.dmp into same schema.
after exporting dump,i found that the tables were in system tablespace. i 'm not able to sort out this problem.Please tell me some solution.
Thanx.
Re: Import Tables from SYSTEM Tablespace (urgent) [message #72548 is a reply to message #72546] Tue, 15 July 2003 06:25 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
when u do the export, the table is exported with its existing defintions . so tables are exported with tablespace = system tablespace.
you have granted resource and dba role to the new user.
which give him right to write into any tablespace.
so, when importing, by default it import into system tablespace( read from the exported definitions).
you have do this.
1. create a new user(targetuser) and make new_tablespace as his 
    defualt tablespace;
2. do the import like following

   imp dba/password fromuser=sourceUser touser=targetuser file=somefile.dmp

-- since now, the defualt tablespace for target user
is not system tablespace, import will import into his defualt tablespace. 
-- again if there are any indexes in other tablespaces
it will be copied over to same tablespaces again beucase u have given the role resource.

Re: Import Tables from SYSTEM Tablespace (urgent) [message #72549 is a reply to message #72546] Tue, 15 July 2003 08:13 Go to previous message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
and if you just trying to move the tables from the system tablespace ( the still owned by the same user)
try this

----------------------------------------------------------------------

alte table table_name move tablespace < your_new_tablespace >;

Previous Topic: STDCALL application with C DECL Oracle Client Library
Next Topic: Sql*plus and CHR(10)
Goto Forum:
  


Current Time: Fri Jun 28 21:54:18 CDT 2024