My Oracle Support Banner

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 FEBRUARY 25, 2019

Applies to:

Oracle Database - Enterprise Edition - Version 11.2.0.1.0 and later
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Backup Service - Version N/A and later
Information in this document applies to any platform.


NOTE: In the images and/or the document content below, the user information and data used represents fictitious data. Any similarity to actual persons, living or dead, is purely coincidental and not intended in any manner.

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:

 

Changes

 

Cause

To view full details, sign in with your My Oracle Support account.

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


In this Document
Symptoms
Changes
Cause
Solution
References

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.