DataPump Import (IMPDP) With REMAP_TABLE Fails With ORA-31604 In 11g Or With ORA-00942 In 12c When Loading POST_TABLE_ACTION (Doc ID 2224305.1)

Last updated on JANUARY 18, 2017

Applies to:

Oracle Database - Enterprise Edition - Version 11.2.0.1.0 and later
Information in this document applies to any platform.

Symptoms

When importing (impdp) a table with remap_table, ORA-31604 and other errors are caused at POST_TABLE_ACTION phase.

In 11gR2, the reported errors are:

...
Processing object type TABLE_EXPORT/TABLE/POST_TABLE_ACTION
ORA-39126: Worker unexpected fatal error in KUPW$WORKER.LOAD_MD_TRANSFORMS []
ORA-31604: invalid transform NAME parameter "MODIFY" for object type
POST_TABLE_ACTION in function ADD_TRANSFORM

ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.KUPW$WORKER", line 8996

----- PL/SQL Call Stack -----
object line object
handle number name
0xcaac6288 20462 package body SYS.KUPW$WORKER
0xcaac6288 9028 package body SYS.KUPW$WORKER
0xcaac6288 14078 package body SYS.KUPW$WORKER
0xcaac6288 16366 package body SYS.KUPW$WORKER
0xcaac6288 3956 package body SYS.KUPW$WORKER
0xcaac6288 9725 package body SYS.KUPW$WORKER
0xcaac6288 1775 package body SYS.KUPW$WORKER
0xcc815688 2 anonymous block

ORA-39097: Data Pump job encountered unexpected error -1427
ORA-39065: unexpected master process exception in DISPATCH
ORA-01427: single-row subquery returns more than one row

Job "DPTEST"."SYS_IMPORT_TABLE_01" stopped due to fatal error at 12:29:17

 
Similar issue is hit in 12c although error messages are different:

$ impdp dptest/dptest tables=DPTEST.T1 dumpfile=exp2.dmp directory=tmp_dir remap_table=t1:t2

Import: Release 12.1.0.1.0 - Production on Fri Nov 8 12:24:42 2016
Copyright (c) 1982, 2016, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Master table "DPTEST"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "DPTEST"."SYS_IMPORT_TABLE_01": dptest/******** tables=DPTEST.T1 dumpfile=exp2.dmp directory=tmp_dir remap_table=t1:t2
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "DPTEST"."T2" 0 KB 0 rows
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Processing object type TABLE_EXPORT/TABLE/POST_TABLE_ACTION
ORA-39083: Object type POST_TABLE_ACTION failed to create with error:
ORA-00942: table or view does not exist
Failing sql is:
BEGIN
SYS.DBMS_SNAPSHOT_UTL.SYNC_UP_LOG('DPTEST','T1');
END;
Job "DPTEST"."SYS_IMPORT_TABLE_01" completed with 1 error(s) at Fri Nov 8 12:24:53 2016 elapsed 0 00:00:07

The table encountering this is a master table of materialized view and this has a materialized view log.

This issue can be easily reproduced in any environment by executing the following steps:

 

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms