Oracle DataPump “INCLUDE” parameter and limit of 4000 chars
I’ve seen a question about the INCLUDE parameter of DataPump at OTN Forums. The user needed to enter 900+ tables into include parameter but she got “UDE-00014: invalid value for parameter, ‘include'”:
|
1 2 3 4 5 |
expdp DADM/passwd DIRECTORY=DMP_DIR SCHEMAS=DADM DUMPFILE=dadm_tables_data.dmp CONTENT=DATA_ONLY INCLUDE=TABLE:"IN ('CBTRFCC','CBTFCCN','CGTGCDD','CGDSALC', ... 'CGTRSGR','CBTFCCE','CGTREPD','CPDORPG') |
Although there’s no limit on the number of objects you enter, “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:
|
1 2 3 4 5 6 7 8 9 |
CREATE TABLE list_of_tables ( tbl_name VARCHAR2(30) ); INSERT INTO list_of_tables ( 'CBTRFCC' ); INSERT INTO list_of_tables ( 'CBTFCCN' ); ... INSERT INTO list_of_tables ( 'CPDORPG' ); COMMIT; expdp DADM/passwd DIRECTORY=DMP_DIR SCHEMAS=DADM DUMPFILE=dadm_data.dmp CONTENT=DATA_ONLY include=TABLE:"IN (SELECT tbl_name FROM list_of_tables)" |