Oracle Data Pump – Export All Data Except One Table Structure

expdpimpdporacle

we use to make a copy of schemas from one database and move it to another for testing using this par file …

DIRECTORY=EXPORT
DUMPFILE=QQS_PROD.DMP
LOGFILE=QQS_PROD.LOG
SCHEMAS=QQSDATA_PROD,QQSREPOSITORY_PROD,QQS_CENTRAL_PROD,QQS_BD_PROD,QQS_QD_PROD
FLASHBACK_TIME=SYSTIMESTAMP
EXCLUDE=TABLE:"IN ('SYS_HISTORY')"

then create the CONFIG_HISTORY with a script on the target database!

is there any way to NOT EXCLUDE=TABLE:"IN ('SYS_HISTORY')" and export the structure only of this table?

can I use something like this:

INCLUDE=TABLE:"IN ('SYS_HISTORY')"
QUERY=SYS_HISTORY:"where CON_ID < 0"

note: there is no CON_ID with value less than 0, it will return nothing when CON_ID < 0!

Best Answer

You probably can try something like,

DIRECTORY=EXPORT
DUMPFILE=QQS_PROD.DMP
LOGFILE=QQS_PROD.LOG
SCHEMAS=QQSDATA_PROD,QQSREPOSITORY_PROD,QQS_CENTRAL_PROD,QQS_BD_PROD,QQS_QD_PROD
FLASHBACK_TIME=SYSTIMESTAMP
query=SYS_HISTORY:"where 1=2"

This way, the metadata about table SYS_HISTORY will be copied but no data. Note: since you are not prefixing table name (to exclude data from), with schema name, this filter will apply to this table in each schema specified.

Related Question