Geek Answers Handbook

Import sample data using impdp

I have an entire database that will be imported as a dump into mine. I want to exclude data from certain tables (mainly because they are huge in size and not useful). I cannot completely exclude these tables, since I need a table object as such (minus data), and I will have to create them in my schema if I do this. In addition, in the absence of these table objects, various other external constraints defined in other tables will also not be imported and must be redefined. Therefore, I need to exclude only data from certain tables. I need data from all other tables, though,

Is there a set of options for impdp that can help me?

+6
import oracle impdp
source share
4 answers

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

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

Another way of working, but in this way you only need to list the tables that you do not want and all that you want.

+8
source share

I would do two runs: the first I would import only metadata:

impdp ... CONTENT=METADATA_ONLY

The second will include data only for tables that interest me:

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

+9
source share

Syntax:

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

Examples of using the operator:

  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 saved in the parameter file, for example: exp.par

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

If the table size is large for export, you can use the "SAMPLE" parameter in the expdp command to export the table for what you need ...

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

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

+1
source share

More articles:


All Articles