Wednesday, January 07, 2009

DataPump: UDE-00019: 'tables' parameter list is too long.

This is due to bug in Oracle <11. When you are using parameter TABLES=, it cannot be longer than 4000 characters. To ommit this you can create table containing list of all the tables you want to export :

CREATE TABLE expdp_tab (owner VARCHAR2(30), object_name VARCHAR2(128), object_type VARCHAR2(19));

INSERT INTO expdp_tab VALUES ('schema','table_name','TABLE');
....
commit;

and then replace TABLES=(...) in parfile with this:
INCLUDE=TABLE:"IN (SELECT OBJECT_NAME FROM EXPDP_TAB)"

Keep in mind one important thing. When you are running direct impdp from source database to local using NETWORK_LINK parameter, table with list of tables needs to be created on source database, if you create this on local, you will get error like this:
ORA-39001: invalid argument value
ORA-39071: Value for INCLUDE is badly formed.
ORA-00942: table or view does not exist
ORA-06512: at "SYS.KUPM$MCP", line 2614