Errors ORA-2373 ORA-904 When Running DataPump Import (IMPDP) With REMAP_SCHEMA And TABLE_EXISTS_ACTION=REPLACE (Doc ID 1406213.1)

Last updated on JULY 23, 2013

Applies to:

Oracle Database - Enterprise Edition - Version 11.1.0.6 to 11.2.0.1 [Release 11.1 to 11.2]
Information in this document applies to any platform.
***Checked for relevance on 23-Jul-2013***

Symptoms

The example below describes the issue:

connect / as sysdba

-- create two users TEST1 and TEST2:
create user test1 identified by test1;
create user test2 identified by test2;
grant dba to test1, test2 ;

-- first create the table T_DISCREPANCY in schema TEST2
connect test2/test2

CREATE TABLE T_DISCREPANCY
(
   C1 NUMBER(14,3) NOT NULL,
   C2 NUMBER(2,0)  NOT NULL
);

-- create a table with the same name T_DISCREPANCY
-- but with another structure in schema TEST1
connect test1/test1

CREATE TABLE T_DISCREPANCY
(
   C1 NUMBER(14,3) NOT NULL,
   C2 NUMBER(2,0)  NOT NULL,
   C3 CHAR(1 BYTE) DEFAULT 'N'
);

-- populate the table T_DISCREPANCY from schema TEST1
insert into T_DISCREPANCY values (1, 1, 'A');
insert into T_DISCREPANCY values (2, 2, 'B');
insert into T_DISCREPANCY values (3, 3, 'C');
commit;


Now use datapump expdp to export the schemas TEST1 and TEST2:

Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "TEST1"."SYS_EXPORT_SCHEMA_01": test1/******** dumpfile=tc.dmp directory=dptest schemas=test1,test2
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCACT_SCHEMA
. . exported "TEST1"."T_DISCREPANCY"     5.968 KB      3 rows
. . exported "TEST2"."T_DISCREPANCY"         0 KB      0 rows
Master table "TEST1"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for TEST1.SYS_EXPORT_SCHEMA_01 is:
/tmp/tc.dmp
Job "TEST1"."SYS_EXPORT_SCHEMA_01" successfully completed at 11:08:05


Run datapump impdp to import with the option REMAP_SCHEMA=TEST1:TEST2 TABLE_EXISTS_ACTION=REPLACE:

Import: Release 11.1.0.7.0 - Production on Friday, 06 January, 2012 11:08:33

Copyright (c) 2003, 2007, Oracle. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "TEST1"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "TEST1"."SYS_IMPORT_FULL_01": test1/******** dumpfile=tc.dmp directory=dptest remap_schema=TEST1:TEST2 table_exists_action=replace
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"TEST2" already exists
ORA-31684: Object type USER:"TEST2" already exists
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
ORA-31693: Table data object "TEST2"."T_DISCREPANCY" failed to load/unload and is being skipped due to error:
ORA-02354: error in exporting/importing data
ORA-02373: Error parsing insert statement for table "TEST2"."T_DISCREPANCY".
ORA-00904: "C3": invalid identifier
. . imported "TEST2"."T_DISCREPANCY"       0 KB      0 rows
Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCACT_SCHEMA
Job "TEST1"."SYS_IMPORT_FULL_01" completed with 3 error(s) at 11:08:43


The errorstack generated for error ORA-904 shows:

----- Error Stack Dump -----
ORA-00904: "C3": invalid identifier
----- Current SQL Statement for this session (sql_id=gxjy0nd4mx7ap) -----
INSERT /*+ SYS_DL_CURSOR */ INTO RELATIONAL("TEST2"."T_DISCREPANCY") ("C1","C2","C3")
VALUES
(NULL,NULL,NULL)
----- PL/SQL Stack -----
----- PL/SQL Call Stack -----
object line object
handle number name
0x24b1cecc 54 package body SYS.KUPD$DATA_INT
0x27b7793c 2374 package body SYS.KUPD$DATA
0x27b7e0f4 13489 package body SYS.KUPW$WORKER
0x27b7e0f4 4247 package body SYS.KUPW$WORKER
0x27b7e0f4 8546 package body SYS.KUPW$WORKER
0x27b7e0f4 1545 package body SYS.KUPW$WORKER
0x24f8a828 2 anonymous block

Changes

The error is generated by the order used in the source database to create a table with the same name in different schemas. In the example above,when the table T_DISCREPANCY is first created in TEST2 schema and then in TEST1 schema, the error is raised at impdp level.

If you change the order of creating objects, e.g first create the table in schema TEST1 and then in schema TEST2, then the import works correctly:

-- first create the table T_DISCREPANCY in TEST1 schema and then in TEST2 schema
connect test1/test1

CREATE TABLE T_DISCREPANCY
(
   C1 NUMBER(14,3) NOT NULL,
   C2 NUMBER(2,0)  NOT NULL,
   C3 CHAR(1 BYTE) DEFAULT 'N'
);

insert into T_DISCREPANCY values (1, 1, 'A');
insert into T_DISCREPANCY values (2, 2, 'B');
insert into T_DISCREPANCY values (3, 3, 'C');
commit;

connect test2/test2

CREATE TABLE T_DISCREPANCY
(
   C1 NUMBER(14,3) NOT NULL,
   C2 NUMBER(2,0)  NOT NULL
);


Run DataPump export (expdp):

Export: Release 11.1.0.7.0 - Production on Friday, 06 January, 2012 11:11:30
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "TEST1"."SYS_EXPORT_SCHEMA_01": test1/******** dumpfile=tc1.dmp directory=dptest schemas=test1,test2
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCACT_SCHEMA
. . exported "TEST1"."T_DISCREPANCY"     5.976 KB     4 rows
. . exported "TEST2"."T_DISCREPANCY"         0 KB     0 rows
Master table "TEST1"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for TEST1.SYS_EXPORT_SCHEMA_01 is:
  /temp/tc1.dmp
Job "TEST1"."SYS_EXPORT_SCHEMA_01" successfully completed at 11:12:24


Run the import with REMAP_SCHEMA=TEST1:TEST2 TABLE_EXISTS_ACTION=REPLACE:

Import: Release 11.1.0.7.0 - Production on Friday, 06 January, 2012 11:12:46
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "TEST1"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "TEST1"."SYS_IMPORT_FULL_01": test1/******** dumpfile=tc1.dmp directory=dptest remap_schema=TEST1:TEST2 table_exists_action=replace
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"TEST2" already exists
ORA-31684: Object type USER:"TEST2" already exists
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "TEST2"."T_DISCREPANCY"      5.976 KB     4 rows
. . imported "TEST2"."T_DISCREPANCY"          0 KB     0 rows
Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCACT_SCHEMA
Job "TEST1"."SYS_IMPORT_FULL_01" completed with 2 error(s) at 11:12:52


=> The tables are imported as expected

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