I am looking for the best approach to "Re-Org schema" by importing tables and indexes into two different 'tablespaces'. Like to know the best process to follow:
1) One schema 'abc' (DEV) to be expdp and impdp to other schema 'abc' in other database(PROD) but want them to be in a more 'oraganised' way? Like metada and all in one tablespace and tables in other? What will be the best approach to follow?
Database: 11gR2 on UNIX
Edited by: Junr DBA on Feb 6, 2013 7:56 AM
Use the EXCLUDE clause in impdp to exclude indexes from being created.
Then use the SQLFILE clause to create a DDL file, edit it as needed to only create the indexes in the desired tablespace, then run the edited file.
One question for clarification:
expdp system/pwd directory=exp_dir dumpfile=schema_ddl.dmp SCHEMAS=SCOTT CONTENT=METADATA_ONLY
1) When we use METADATA_ONLY for content:- Does it mean we are exporting all object definitions(DDL for table,index etc etc) in that SCHEMA except data or it means it is expoting metadata from SYS or SYSTEM schema?
Edited by: DBA_Guide on Feb 6, 2013 11:32 AM
Thanks Srini for your quick responses and it helped!
so far done with the metadata_only exp and imp.
But to my surprise eventhough I mention 'REMAP_TABLESPACE=SCH1:DES1' - it still writes to DES3 instead of DES1 becasuse tables are hard coded to write to DES3.
Is there a way to re-direct them to the new tablespace?
The remap_tablespace should do the trick. All objects are hard coded to a tablespace so your situation is no different. The remap_tablespace should do exactly what you want it to do. You can see this by running the sqlfile option of import and then look at the tablespaces in the create statements.
One quick note on using sqlfile to create indexes. You don't need to do this. You can run your first impdp with:
and then your second impdp command with
It should get you exactly what you want
Hope this helps.