export large number of tables using expdp

Question

My Environment has more than 4000 tables. I want to export only 2000 tables. I tried using par file using Include clause. But the export is failing. Details are mentioned below

Tool: EXPDP | Oracle Database version:11g R2 | Number of tables to be exported: 2000

Code used:

directory=pump
dumpfile=EXP_FULL_GOLD.dmp
logfile=EXP_FULL_GOLD.log
COMPRESSION=ALL
schemas=GOLD
include=TABLE:"IN('T1','T2','T3','T4'..'T2000')

Error: With the Partitioning, OLAP, Data Mining and Real Application Testing options ORA-39001: invalid argument value ORA-39071: Value for INCLUDE is badly formed. ORA-00920: invalid relational operator

Even I tried below code:

directory=pump 
dumpfile=EXP_FULL_GOLD.dmp 
logfile=EXP_FULL_GOLD.log 
COMPRESSION=ALL 
schemas=Gold
content=DATA_ONLY
INCLUDE=TABLE:\"IN \(SELECT tname FROM Gold.t11\)\"

Here T11 contains list of tables stored in Column tname (2000 records).

But same error occurs. Please help as I searched on Net a lot, but I am not able to find any Solution. Even for Include clause Number of Tables allowed Limit is also not available anywhere. Tried using 900 tables but failed with same error.

Regards,

Sujit

Answer 1

You can import only the tables that you want from a full export using a parfile that includes the list of tables that you want using a table mode import:-

directory=pump 
dumpfile=EXP_FULL_GOLD.dmp 
logfile=EXP_FULL_GOLD.log
transform=storage:n
exclude=statistics
tables=(
gold.t1,
gold.t2,
gold.t3,
gold.t4,
gold.t2000)

(add all the tables that you need).

You need to pre-create the gold schema if it currently does not exist. You can do that with a schema mode import if you want with exclude=table to skip all the tables.

Answer 2

The documentation describes, that the name_clause of the INCLUDE parameter

is a SQL expression used as a filter on the object names...

this implies that by using an IN list you are constrained with the general limit of 1000 elements in the list.

Even worst this link suggest that there is a limit of the length of the name_clause by 4000 chars. Exceeding it you get UDE-00014 invalid value for parameter INCLUDE

But a workaround is easy (as suggested in the link above)

1) use a parameter file - see parameter PARFILE - to aviod possible escaping issues

2) put the (unquoted) table names in a helper table and use following parameter

 INCLUDE=TABLE:"IN (select table_name from TAB_LIST)"

Answer 3

Found the solution:

SCHEMAS=GOLD
DIRECTORY=DEBUG
COMPRESSION=ALL
CONTENT=DATA_ONLY
DUMPFILE=EXP_GOLD_26Jul2017.dmp
LOGFILE=EXP_GOLD_26Jul2017.log
INCLUDE=TABLE:"IN(SELECT TNAME FROM GOLD.T11)"   

Thanks.

Answer 4

Just FYI, the parameter has a size limit of about 40000 characters. So, you will probably have to split the export into several .par files