Monday, February 8, 2016

Oracle DataPump “INCLUDE” parameter and limit of 4000 characters

Today I am trying to export certain tables (202 in count) using expdp, but I got “UDE-00014: invalid value for parameter, ‘include'”:
expdp SYSTEM/PASSWORD SCHEMAS=TEST_2016_S DIRECTORY=FULLBACKUPS
DUMPFILE=TEST_2016_S.DMP LOGFILE=TEST_2016_S_.LOG
INCLUDE=TABLE:"IN('AAA','BBB','CCC','DDD',
......
'ZZZ','AAAA','BBBB','CCCC')" COMPRESSION=ALL PARALLEL=8
“include” parameter accepts only 4000 characters. A simple workaround is to use a table to hold the names of the tables/objects and use this table with include parameter:
CREATE TABLE tables_list(table_name VARCHAR2(30) );
INSERT INTO table_list ( 'AAA' );
INSERT INTO table_list ( 'BBB' );
INSERT INTO table_list ( 'CCC' );
INSERT INTO table_list ( 'CCC' );
...
...
INSERT INTO table_list ( 'ZZZ' );
INSERT INTO table_list ( 'AAAA' );
INSERT INTO table_list ( 'BBBB' );
INSERT INTO table_list ( 'CCCC' );
COMMIT;

expdp SYSTEM/PASSWORD SCHEMAS=TEST_2016_S DIRECTORY=FULLBACKUPS
DUMPFILE=TEST_2016_S.DMP LOGFILE=TEST_2016_S_.LOG include=TABLE:"IN (SELECT table_name FROM table_list)" 

1 comment:

Caesar Dutta said...

I have tried this. It is provided in http://gokhanatil.com/2011/06/oracle-datapump-include-parameter-and-limit-of-4000-chars.html as well as https://stackoverflow.com/questions/45324367/export-large-number-of-tables-using-expdp.

But received the same error ude-00014.