DB version: 11.2.0.4
Platform : Oracle Linux 6.4
I am relatively new to expdp/impdp.
I want to Skip table data from being imported (or exported) for selected 60 tables. Here is my requirement.
We have a schema named SCHEMA_ABC . It has 400 tables + 300 other objects like sequences, Stored Procs, Functions, packages etc. We want to create a clone of SCHEMA_ABC named SCHEMA_XYZ , but, in SCHEMA_XYZ, we need to skip importing data for 60 tables. ie. we still need the structure of these 60 tables to be there. I would like to know if this skipping part can be done during expdp or impdp.
We tried to do the following. But Step3 didn't work. Is there a way Step2 and step3 can be done in one step ?
-- What we tried
Step1. Take expdp dump of schema SCHEMA_ABC (with data) . The result is SCHEMA_ABC.dmp
Step2. Run impdp to SCHEMA_XYZ, but skip 60 tables using EXCLUDE clause in impdp
--- For readability, I am including only 3 tables in the below example
impdp dpuser/tiger DIRECTORY=DUMP_DIR remap_schema=SCHEMA_ABC:SCHEMA_XYZ DUMPFILE= SCHEMA_ABC.dmp EXCLUDE=TABLE:\"IN\(\'EMP_DTL\',\'EMP_HRTB_MSG\',\'EDN_LOG_MESSAGES\'\)\"
Step3.Now, run impdp again with INCLUDE and CONTENT=METADATA_ONLY
impdp dpuser/tiger DIRECTORY=DUMP_DIR remap_schema=SCHEMA_ABC:SCHEMA_XYZ DUMPFILE= SCHEMA_ABC.dmp INCLUDE=TABLE:\"IN\(\'EMP_DTL\',\'EMP_HRTB_MSG\',\'EDN_LOG_MESSAGES\'\)\" CONTENT=METADATA_ONLY
As mentioned earlier, Step3 shown above didn't work. Although the output said the job "successfully completed", the table structures were not imported to SCHEMA_XYZ.Maybe INCLUDE cannot be used in this scenario. If I want to import only few tables and nothing else ( like other objects), can I use INCLUDE ?
---- Result of Step3
Starting "SYS"."SYS_IMPORT_FULL_02": "/******** AS SYSDBA" DIRECTORY=DUMP_DIR remap_schema=SCHEMA_ABC:SCHEMA_XYZ DUMPFILE=SCHEMA_ABC.dmp CONTENT=METADATA_ONLY INCLUDE=TABLE:"IN('EMP_DTL','EMP_HRTB_MSG','EDN_LOG_MESSAGES')"
Job "SYS"."SYS_IMPORT_FULL_02" successfully completed at Fri Jan 8 11:45:43 2016 elapsed 0 00:00:07
sqlplus SCHEMA_XYZ/tiger
SQL> desc EDN_LOG_MESSAGES
ERROR:
ORA-04043: object EDN_LOG_MESSAGES does not exist