ORA-39151 Reported By DataPump Import When Using REMAP_TABLE Option (Doc ID 886762.1)

Last updated on MARCH 07, 2017

Applies to:

Oracle Database - Enterprise Edition - Version 11.1.0.6 to 11.1.0.7 [Release 11.1]
Oracle Database - Enterprise Edition - Version 11.2.0.4 to 11.2.0.4 [Release 11.2]
Information in this document applies to any platform.
***Checked for relevance on 14-Mar-2013***


Symptoms

Oracle 11g introduced the REMAP_TABLE option which allows you to rename a table during DataPump Import:

REMAP_TABLE=[schema.]old_tablename[.partition]:new_tablename


However, due to unpublished bug 7561900 the REMAP_TABLE option doesn't work as expected
and ORA-39151 is reported when a schema name is specified in connection with the REMAP_TABLE option.

#> impdp tc1/tc1 directory=test_dp dumpfile=export_tables.dmp tables=tab1 remap_table=tc1.tab1:tab2

Import: Release 11.1.0.7.0 - Production on Saturday, 12 September, 2009 22:15:16

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 "TC1"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "TC1"."SYS_IMPORT_TABLE_01": tc1/******** directory=test_dp dumpfile=export_tables.dmp tables=tab1 remap_table=tc1.tab1:tab2
Processing object type TABLE_EXPORT/TABLE/TABLE
ORA-39151: Table "TC1"."TAB1" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Job "TC1"."SYS_IMPORT_TABLE_01" completed with 1 error(s) at 22:15:18


The issue will not occur if you remove the schema name specification, like:

REMAP_TABLE=old_tablename:new_tablename


#> impdp tc1/tc1 directory=test_dp dumpfile=export_tables.dmp tables=tab1 remap_table=tab1:tab2

Import: Release 11.1.0.7.0 - Production on Saturday, 12 September, 2009 22:40:48

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 "TC1"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "TC1"."SYS_IMPORT_TABLE_01": tc1/******** directory=test_dp dumpfile=export_tables.dmp tables=tab1 remap_table=tab1:tab2
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "TC1"."TAB2" 10.85 KB 5 rows
Job "TC1"."SYS_IMPORT_TABLE_01" successfully completed at 22:40:51


SQL> select owner, table_name from dba_tables
     where  owner = 'TC1' and
            table_name in ('TAB1','TAB2');

OWNER       TABLE_NAME
----------- ---------------
TC1         TAB1
TC1         TAB2

 

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