CODEWITHSUNDEEP
oracle11gdatapump
Raghavendra Acharya n
Raghavendra Acharya n

Reputation: 93

Dbms_datadump fails with ORA-39117 for a table with user defined type

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.

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'')');

Upvotes: 2

Views: 3255

Answers (1)

Jon Heller
Jon Heller

Reputation: 36922

This problem may have nothing to do with impdp vs DBMS_DATAPUMP. I used to have many problems importing object-relational tables. We never found a solution and the problem appeared to be "random" - it would work one day and fail another for reasons we never discovered. If I remember correctly, we guessed there were problems with object-relational dependencies and fixed the problem by manually importing a few types before the rest of the import.

Another possibility is that the import succeeded and the error message is wrong. See Doc ID 783358.1 - ORA-39117 Incorrectly Reported At Impdp Level, on support.oracle.com.

Upvotes: 1

Related Questions