DATA PUMP: EXCLUDE / INCLUDE TABLE IMPORT

EXCLUDING TABLES DURING DATA_PUMP IMPORT:

impdp USERNAME/PASSWORD schemas=USERNAME directory=backup dumpfile=full.dmp EXCLUDE=TABLE:\"IN \(\'TABLE1\',\'TABLE2\',\'TABLE3\',\'TABLE4\',\'TABLE5\',\'TABLE6\'\)\"
impdp ultimus/ultimus schemas=ultimus directory=db_back dumpfile=AFT_EOD.dmp EXCLUDE=TABLE:\"IN \(\'IMG_SIGNATORY_IMAGE\',\'IMG_SIGNATORY_IMAGE_DEL_LOG\',\'IMG_SIGNATORY_INFO\',\'IMG_SIGNATORY_VERIFY_LOG\',\'IMG_BACH_CLG_HIST_IN\',\'IMG_BRM_INST\',\'IMG_NFT_AUTH_LOG\'\)\"

EXCLUDING TABLES DURING DATA_PUMP IMPORT – USING “LIKE ” COMMAND:

impdp USERNAME/PASSWORD schemas=USERNAME directory=backup dumpfile=full.dmp EXCLUDE=TABLE:\"like 'IMG_%%'\" EXCLUDE=TABLE:\"IN \(\'EMP\',\'DEPT\'\)\"

INCLUDING TABLES DURING DATA_PUMP IMPORT:

impdp USERNAME/PASSWORD schemas=USERNAME directory=backup dumpfile=full.dmp INCLUDE=TABLE:\"IN \(\’EMP\’, \’DEP\’\)\"

The OLD Subquery TRICK:
EXCLUDE :
expdp directory=DB_BACK dumpfile=full.dmp EXCLUDE=table:\”in \(select table_name from all_tables where table_name like \’IMG_%\’ or table_name like \’%HIST%\’\)\”

 

INCLUDE :expdp directory=DB_BACK dumpfile=full.dmp INCLUDE=table:\”in \(select table_name from all_tables where table_name like \’IMG_%\’ or table_name like \’%HIST%\’\)\”

Advertisements

About Sher khan

Senior Oracle DBA, Oracle 10g , Oracle 11g OCE ,Oracle 12C and Oracle 11g OPN certified Specialist, OCP Certified in Oracle 9i,10g , 11g and 12C. I am working in Oracle technology since 2004. Currently working in U.A.E Email: sher487@hotmail.com
This entry was posted in Oracle backup & recovery. Bookmark the permalink.

One Response to DATA PUMP: EXCLUDE / INCLUDE TABLE IMPORT

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s