IMPDP 11g Aborts After Errors ORA-942 ORA-39120 When Using Parameters CONTENT=DATA_ONLY And TABLE_EXISTS_ACTION=TRUNCATE (Doc ID 971983.1)

Last updated on JANUARY 04, 2016

Applies to:

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

Symptoms

When using IMPDP with TABLE_EXISTS_ACTION=TRUNCATE and CONTENT=DATA_ONLY, the import process of Oracle11g aborts when the table doesn't exist. In Oracle10g the import process continued with importing the rest of the dump file and only skipped the data of the missing table.

In the following scenario a TEST schema exists with the TEST1 and TEST2 tables. An export is made of this schema:

expdp system/password tables=\(TEST.TEST1, TEST.TEST2\) dumpfile=test.dmp


After this, the TEST2 table is dropped and the dump file is imported with IMPDP again.

The results for IMPDP 10.2.0.4 are:

impdp system/manager directory=dpdirectory dumpfile=test_10204.dmp full=y table_exists_action=truncate content=data_only

Import: Release 10.2.0.4.0 - 64bit Production on Wednesday, 28 October, 2009 16:53:36
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining Scoring Engine and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01": system/******** directory=dpdirectory
dumpfile=test_10204.dmp full=y table_exists_action=truncate content=data_only
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
ORA-39120: Table "TEST"."TEST2" can't be truncated, data will be skipped.
Failing error is:
ORA-942: table or view does not exist
. . imported "TEST"."TEST1" 4.953 KB 7 rows
Job "SYSTEM"."SYS_IMPORT_FULL_01" completed with 1 error(s) at 16:53:37


In this case, even though the ORA-39120 error is raised, the other tables of the dump file are imported successfully.

With IMPDP 11.1.0.7 the results are:

impdp system/manager directory=dpdirectory dumpfile=test_11107.dmp table_exists_action=truncate content=data_only

Import: Release 11.1.0.7.0 - 64bit Production on Wednesday, 28 October, 2009 14:38:09
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01": system/********
directory=dpdirectory dumpfile=test_11107.dmp table_exists_action=truncate content=data_only
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
ORA-39120: Table "TEST"."TEST2" can't be truncated, data will be skipped.
Failing error is:
ORA-942: table or view does not exist
ORA-39126: Worker unexpected fatal error in KUPW$WORKER.UPATE_TD_ROW_IMP [15]
TABLE_DATA:"SYSTEM"."TEST2"
ORA-39120: Table can't be truncated, data will be skipped. Failing error is:
.
ORA-6512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-6512: at "SYS.KUPW$WORKER", line 7845
.
----- PL/SQL Call Stack -----
object line object
handle number name
391cbdb00 18237 package body SYS.KUPW$WORKER
391cbdb00 7866 package body SYS.KUPW$WORKER
391cbdb00 17817 package body SYS.KUPW$WORKER
391cbdb00 3948 package body SYS.KUPW$WORKER
391cbdb00 8532 package body SYS.KUPW$WORKER
38bf4d3c0 1 anonymous block
38bc8fd60 1575 package body SYS.DBMS_SQL
391cbdb00 8342 package body SYS.KUPW$WORKER
391cbdb00 1545 package body SYS.KUPW$WORKER
38beeceb0 2 anonymous block


In this case tne ORA-39120 error is detected, but IMPDP aborts with an import failure and the other tables (in this case TEST1) contained in the dump file are not imported.

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