I have a table which has column of user defined type, I tried to move the table to a different schema,
It worked fine using command line mode, however when I tries to use DBMS_DATAPUMP, it fails with ORA-39117 error, below are the filters I had used.
Database version:11.2.0.3
Command Line Mode:
bin/expdp user1/password1 directory=TEST_DIR1 include=TABLE:\"=\'TEST_TABLE_1\'\",TYPE:\"IN \(\'TEST_TYPE_1\'\)\" reuse_dumpfiles=y dumpfile=TEST.dmp logfile=expdpTEST.log
bin/impdp user1/password1 directory=TEST_DIR1 include=TABLE:\"=\'TEST_TABLE_1\'\",,TYPE:\"IN \(\'TEST_TYPE_1\'\)\" dumpfile=TEST.dmp logfile=impdpTEST.log remap_schema=USER1:USER2 TRANSFORM=oid:n
With the above command, table movement was successful. But we would like to do the same thing using pl/sql block.
===Export===
DBMS_DATAPUMP.metadata_filter (
handle => h1,
name => 'NAME_EXPR',
VALUE => 'IN(''TEST_TABLE_1'',''TEST_TYPE_1'')'
);
dbms_datapump.metadata_filter(
handle => h1, name => 'INCLUDE_PATH_EXPR', value => 'IN (''TABLE'',''TYPE'')');
===Import===
DBMS_DATAPUMP.metadata_filter (
handle => h1,
name => 'NAME_EXPR',
VALUE => 'IN(''TEST_TABLE_1'',''TEST_TYPE_1'')'
);
dbms_datapump.metadata_filter(
handle => h1, name => 'INCLUDE_PATH_EXPR', value => 'IN (''TABLE'',''TYPE'')');