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


and then

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?

2 Answers 2


No, it is not a good approach, there are a lot of problems, e.g.

  1. Doing a data import after the indexes are created will slow down the import considerable.
  2. the import will fire all insert triggers. That may slow down the import, too, and it may have unwanted side effects
  3. 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"

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.

  1. export the schema and exclude the large tables. his task is independent from the size of the two large tables
  2. 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)

CONTENT=ALL  # this is the default, so it must not be specified

second export (table exports without data)


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

schema import:


table import:

  • Hello, I am dealing with the same issue. Right now I am using the QUERY parameter, but having a problem with this one. The tables holding the data I want to exclude are large tables with a lot of partitions, and this makes the export procedure very-very slow, because it goes over all the partitions, although it exports 0 rows from each one of them. I'm talking about hours, and it's a procedure that should run every night. Is there any way around this?
    – Mark Kemel
    Nov 26, 2019 at 8:39
  • 1
    @MarkKemel I added a method that uses the EXCLUDE parameter. BBut I am surprised that the method that uses the query takes long because I would expect that the Oracle optimizer realizes that he has not to scan the table to search for records satisfying "where 0=1"
    – miracle173
    Nov 26, 2019 at 18:19
  • Worked like a charm! Thanks a bunch!
    – Mark Kemel
    Nov 28, 2019 at 7:50

While importing in step 4 do not EXCLUDE REF_CONSTRAINTS but rather IMPORT all metadata including constraints and before step 5 disable the constraints and start data import.

You may also need to disable triggers (if any) if you only think that it would not affect your database for faster import.

Hope this helps !!

  • you cannot disable trigger for faster Import. If triggeres are needed and you disable them you will get data inconsistencies.
    – miracle173
    Apr 12, 2018 at 12:41
  • Thanks for your input. I have updated my answer saying that triggers should only be disabled you only think that it would not affect your database. Apr 15, 2018 at 5:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.