CAUTION: REMAP_SCHEMA And TABLE_EXISTS_ACTION Used With DataPump Import Can be Very Dangerous (Doc ID 1326843.1)

Last updated on NOVEMBER 28, 2016

Applies to:

Oracle Database - Enterprise Edition - Version 10.2.0.4 and later
Information in this document applies to any platform.
***Checked for relevance on 13-Mar-2013***

Symptoms

Using REMAP_SCHEMA and TABLE_EXISTS_ACTION together during DataPump import (impdp) is extremely dangerous.

Please find the below test case to understand the issue.

-- create 3 users and grant DBA role
SQL> create user test_exp1 identified by test_exp1;
SQL> create user test_exp2 identified by test_exp2;
SQL> create user test_exp3 identified by test_exp3;
SQL> grant dba to test_exp1, test_exp2, test_exp3;


Now create two tables in schema test_exp1 and test_exp2 and insert few rows into each of them:

SQL> create table test_exp1.test_exp1_tab (col1 number);
SQL> insert into test_exp1.test_exp1_tab values (1);
SQL> insert into test_exp1.test_exp1_tab values (2);
...
SQL> insert into test_exp1.test_exp1_tab values (7);

SQL> create table test_exp2.test_exp2_tab (col1 number);
SQL> insert into test_exp2.test_exp2_tab values (1);
SQL> insert into test_exp2.test_exp2_tab values (2);
...
SQL> insert into test_exp2.test_exp2_tab values (8);

SQL> commit;


So now you have these objects/records for testing:

SQL> select owner, object_name, object_type
     from   dba_objects
     where  object_name like 'TEST_EXP%';

OWNER                          OBJECT_NAME          OBJECT_TYPE
------------------------------ -------------------- -------------------
TEST_EXP1                      TEST_EXP1_TAB        TABLE
TEST_EXP2                      TEST_EXP2_TAB        TABLE

SQL> select count(*) from TEST_EXP1.TEST_EXP1_TAB;

COUNT(*)
----------
7

SQL> select count(*) from TEST_EXP2.TEST_EXP2_TAB;

COUNT(*)
----------
8


Let's take an export of the objects above:

#> expdp directory=DIR schemas=test_exp1,test_exp2

Export: Release 10.2.0.4.0 - 64bit Production on Tuesday, 31 May, 2011 4:41:36

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

Username: / as sysdba

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYS"."SYS_EXPORT_SCHEMA_01": /******** AS SYSDBA directory=DIR
schemas=test_exp1,test_exp2
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 128 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
. . exported "TEST_EXP1"."TEST_EXP1_TAB" 4.976 KB 7 rows
. . exported "TEST_EXP2"."TEST_EXP2_TAB" 4.976 KB 8 rows
Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************

Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:  /grdbms/home/users/faisal/expdat.dmp
Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at 04:43:23


Let's now follow the different scenarios:

Case 1: Import without the option TABLE_EXISTS_ACTION and with wrong value of REMAP_SCHEMA

#> impdp directory=DIR dumpfile=expdat.dmp remap_schema=tiger:test

Import: Release 10.2.0.4.0 - 64bit Production on Tuesday, 31 May, 2011 4:44:29

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

Username: / as sysdba

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 -
64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-39146: schema "TIGER" does not exist
Master table "SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_FULL_01": /******** AS SYSDBA directory=DIR
dumpfile=expdat.dmp remap_schema=tiger:test
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"TEST_EXP2" 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
ORA-39151: Table "TEST_EXP2"."TEST_EXP2_TAB" exists. All dependent metadata
and data will be skipped due to table_exists_action of skip
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Job "SYS"."SYS_IMPORT_FULL_01" completed with 4 error(s) at 04:44:43


The test above clearly shows, with the SKIP (default) option of TABLE_EXISTS_ACTION and wrong name in REMAP_SCHEMA, no rows/tables are effected, which is good .

Case 2: Import with the option TABLE_EXISTS_ACTION and without REMAP_SCHEMA

#l> impdp directory=DIR dumpfile=expdat.dmp TABLE_EXISTS_ACTION=replace

Import: Release 10.2.0.4.0 - 64bit Production on Tuesday, 31 May, 2011 5:01:29

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

Username: / as sysdba

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 -64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_FULL_01": /******** AS SYSDBA directory=DIR
dumpfile=expdat.dmp TABLE_EXISTS_ACTION=replace
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"TEST_EXP1" already exists
ORA-31684: Object type USER:"TEST_EXP2" 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 "TEST_EXP1"."TEST_EXP1_TAB" 4.976 KB 7 rows
. . imported "TEST_EXP2"."TEST_EXP2_TAB" 4.976 KB 8 rows
Job "SYS"."SYS_IMPORT_FULL_01" completed with 2 error(s) at 05:01:44


If you don't specify a new name with REMAP_SCHEMA, the existing schema in the database gets affected, which is an expected behavior.

Case 3: Import with the option TABLE_EXISTS_ACTION and with wrong REMAP_SCHEMA

#> impdp directory=DIR dumpfile=expdat.dmp remap_schema=tiger:test TABLE_EXISTS_ACTION=truncate

Import: Release 10.2.0.4.0 - 64bit Production on Tuesday, 31 May, 2011 4:47:41

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

Username: / as sysdba

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-39146: schema "TIGER" does not exist
Master table "SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_FULL_01": /******** AS SYSDBA directory=DIR
dumpfile=expdat.dmp remap_schema=tiger:test TABLE_EXISTS_ACTION=truncate
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"TEST_EXP2" 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
ORA-39153: Table "TEST_EXP2"."TEST_EXP2_TAB" exists and has been truncated. Data will be loaded but all dependent metadata will be skipped due to table_exists_action of truncate
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "TEST_EXP1"."TEST_EXP1_TAB" 4.976 KB 7 rows
. . imported "TEST_EXP2"."TEST_EXP2_TAB" 4.976 KB 8 rows
Job "SYS"."SYS_IMPORT_FULL_01" completed with 4 error(s) at 04:47:52


Import log prints out ORA-39146: schema "TIGER" does not exist, which is correct as the export dump doesn't contain this user. But if you add by mistake a fictive user, it goes and update the tables in the destination database provided if you have the same schema name as that of the source.

So be aware:

-- if you supply the wrong user name with REMAP_SCHEMA
-- used table_exists_action = append / replace / truncate 
-- user (test_exp1) is available in the destination database

Case 4: Import with the option TABLE_EXISTS_ACTION and with correct REMAP_SCHEMA

#> impdp directory=DIR dumpfile=expdat.dmp remap_schema=TEST_EXP1:TEST_EXP3 TABLE_EXISTS_ACTION=truncate

Import: Release 10.2.0.4.0 - 64bit Production on Tuesday, 31 May, 2011 4:46:48

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

Username: / as sysdba

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_FULL_01": /******** AS SYSDBA directory=DIR dumpfile=expdat.dmp remap_schema=TEST_EXP1:TEST_EXP3 TABLE_EXISTS_ACTION=truncate
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"TEST_EXP3" already exists
ORA-31684: Object type USER:"TEST_EXP2" 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
ORA-39153: Table "TEST_EXP2"."TEST_EXP2_TAB" exists and has been truncated.
Data will be loaded but all dependent metadata will be skipped due to
table_exists_action of truncate
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "TEST_EXP3"."TEST_EXP1_TAB" 4.976 KB 7 rows
. . imported "TEST_EXP2"."TEST_EXP2_TAB" 4.976 KB 8 rows
Job "SYS"."SYS_IMPORT_FULL_01" completed with 3 error(s) at 04:47:00


Even with the correct argument in the remap_schema, the table in schema TEST_EXP2 is affected and the object of TEST_EXP1 is now exported to TEST_EXP3.

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