Oracle full expdp/impdp but skip data in some tables I'm trying to export a schema and then import it, but there are some huge tables that I must skip all the data from (that is, I need the tables and all stuff related to them, just not the rows).

So I thought I'd do 

1) expdp SCHEMAS=X CONTENT=METADATA_ONLY DUMPFILE=meta.dmp
2) exportSCHEMAS=X CONTENT=DATA_ONLY EXCLUDE=TABLE:"IN \('FOO','BAR'\)" DUMPFILE=data.dmp


and then

4) impdp SCHEMAS=X EXCLUDE=REF_CONSTRAINT DUMPFILE=meta.dmp
5) impdp SCHEMAS=X DUMPFILE=data.dmp


The plan is in step 4 to get all the meta data, and then in step 5 import all data but the rows in the large tables FOO and BAR, 
But how can I get the foreign keys?

Is this a way to do it, or are there other ways?
No, it is not a good approach, there are a lot of problems, e.g.


Doing a data import after the indexes are created will slow down the import considerable.
the import will fire all insert triggers. That may slow down the import, too, and it may have unwanted side effects
The current values of the sequences do not match to data.


You should use the QUERY parameter. So if you want to exclude the LOG and MESSAGE table of the APP schema add

QUERY=APP.LOG:"where 0=1"
QUERY=APP.MESSAGE:"where 0=1"


to the parameter file of your export.



Another way to accomplish this task is to split this into two export. This is a little bit more complex but avoids reading table data.


export the schema and exclude the large tables. his task is independent from the size of the two large tables
export the structure of the two large tables. The fime for this task is also independent of the size of the two tables


first export (schema export with data)

DUMPFILE=schema.dmp
SCHEMAS=APP
EXCLUDE=TABLE:"in ('LOG', 'MESSAGES')
CONTENT=ALL  # this is the default, so it must not be specified


second export (table exports without data)

DUMPFILE=table.dmp
TABLES=APP.LOG,APP.MESSAGE
CONTENT=METADATA_ONLY


At first you import the schema dump, then you import the table dump. For both imports you can use the FULL=Yparameter.

schema import:

DUMPFILE=schema.dmp
FULL=Y


table import:

DUMPFILE=table.dmp
FULL=Y