Importing selective data using impdp

Question

I have an entire DB to be imported as a dump into my own. I want to exclude data out of certain tables(mostly because they are huge in size and not useful). I cannot entirely exclude those tables since I need the table object per se(minus the data) and will have to re create them in my schema if I do so. Also in the absence of those table objects , various other foreign constraints defined on other tables will also fail to be imported and will need to be redefined.So I need to exclude just the data from certain tables.I want data from all other tables though.

Is there a set of parameters for impdp that can help me do so?

score 10 · Answer 1 · edited Nov 27 '17 at 16:25

10

I would make two runs at it: The first I would import metadata only:

impdp ... CONTENT=METADATA_ONLY

The second would include the data only for the tables I was interested in:

impdp ... CONTENT=DATA_ONLY TABLES=table1,table2...

edited Nov 27 '17 at 16:25

Blag

5,818
2
22
45

answered Mar 26 '09 at 16:56

DCookie

42,630
11
83
92

score 8 · Accepted Answer · edited Dec 10 '15 at 17:30

8

Definitely make 2 runs. One to create all the table objects, but instead of using tables in the second impdp run, use the exclude

impdp ... Content=data_only exclude=TABLE:"IN ('table1', 'table2')"

The other way works, but this way you only have to list the tables you don't want versus all that you want.

edited Dec 10 '15 at 17:30

NullUserException

83,810
28
209
234

answered Mar 31 '09 at 16:23

MichaelN

1,734
11
6

For some reason `exclude=TABLE:"IN ('table1', 'table2')"` didn't work for me in 11.4. I had to write: `exclude=TABLE:"IN ('table1')" exclude=TABLE:"IN ('table2')"` separately for each table – Jack Scandall Nov 28 '22 at 09:44

score 1 · Answer 3 · edited Feb 15 '18 at 16:14

Syntax:

EXCLUDE=[object_type]:[name_clause],[object_type]:[name_clause]
INCLUDE=[object_type]:[name_clause],[object_type]:[name_clause]

Examples of operator-usage:

   EXCLUDE=SEQUENCE
or EXCLUDE=TABLE:"IN ('EMP','DEPT')"
or EXCLUDE=INDEX:"= 'MY_INDX'"
or INCLUDE=PROCEDURE:"LIKE 'MY_PROC_%'"
or INCLUDE=TABLE:"> 'E'"

The parameter can also be stored in a parameter file, for example: exp.par

DIRECTORY = my_dir
DUMPFILE = exp_tab.dmp
LOGFILE = exp_tab.log
SCHEMAS = scott
INCLUDE = TABLE:"IN ('EMP', 'DEPT')"

score 1 · Answer 4 · edited Aug 11 '14 at 06:58

1

If the size of the table is big for export import the you can use "SAMPLE" parameter in expdp command to take export of table for what ever percentage you want ....

$ expdp tables=T100test DIRECTORY=expimp1 DUMPFILE=test12.dmp SAMPLE = 10;

This command will export only 10% data of the T100test table's data.

edited Aug 11 '14 at 06:58

xav

5,452
7
48
57

answered Aug 11 '14 at 06:37

vikas pandey

11
2

score 0 · Answer 5 · answered Aug 29 '18 at 12:39

0

It seems you can exclude directly when importing using impdp query parameter

impdp [...] QUERY='TABLE_NAME:"WHERE rownum = 0"'

cf : community.oracle.com

answered Aug 29 '18 at 12:39

k4cy

322
3
8

Importing selective data using impdp

5 Answers5