Objective: Explore remap_table option in impdp
reference: https://docs.oracle.com/en/database/oracle/oracle-database/19/sutil/datapump-import-utility.html#GUID-55EA54B5-BC13-48F1-AF14-485C16170274
Setup:
User:
SQL> create user remaptbl_src identified by remaptbl_src;
User created.
SQL> grant connect,create session,resource to remaptbl_src;
Grant succeeded.
SQL> create user remaptbl_tgt identified by remaptbl_tgt;
User created.
SQL> grant connect,create session,resource to remaptbl_tgt;
Grant succeeded.
SQL> grant create table to remaptbl_src,remaptbl_tgt;
Grant succeeded.
SQL> create tablespace tbspc_remaptbl_src datafile size 10m autoextend on next 10m;
Tablespace created.
SQL> create tablespace tbspc_remaptbl_tgt datafile size 10m autoextend on next 10m;
Tablespace created.
SQL> alter user remaptbl_src quota unlimited on TBSPC_REMAPTBL_SRC;
User altered.
SQL> alter user remaptbl_tgt quota unlimited on TBSPC_REMAPTBL_tgt;
User altered.
SQL>
alter user remaptbl_src default tablespace TBSPC_REMAPTBL_SRC;
alter user remaptbl_tgt default tablespace TBSPC_REMAPTBL_TGT;
connection check:
conn remaptbl_src/remaptbl_src@127.0.0.1:1521/INVENTORYPDB
conn remaptbl_tgt/remaptbl_tgt@127.0.0.1:1521/INVENTORYPDB
SQL> conn remaptbl_src/remaptbl_src@127.0.0.1:1521/INVENTORYPDB
Connected.
SQL> disc
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> conn remaptbl_tgt/remaptbl_tgt@127.0.0.1:1521/INVENTORYPDB
Connected.
SQL> disc
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL>
Table & directory setup:
conn remaptbl_src/remaptbl_src@127.0.0.1:1521/INVENTORYPDB
create table tbl1 (id number) tablespace TBSPC_REMAPTBL_SRC;
insert into tbl1 values (1);
insert into tbl1 values (2);
As system user:
SQL> create directory remaptable as '/home/oracle/dba/ImpdpRemap';
Directory created.
Expdp & verify the logfile to check if expdp finished fine:
expdp system/ora19c@127.0.0.1:1521/INVENTORYPDB \
directory=remaptable \
dumpfile=06May2024_remaptbl_01.dmp \
logfile=06May2024_remaptbl_01_expdp.log \
tables=remaptbl_src.tbl1
ls -altr /home/oracle/dba/ImpdpRemap
view /home/oracle/dba/ImpdpRemap/06May2024_remaptbl_01_expdp.log
Output:
[oracle@vcentos79-oracle-sa1 ImpdpRemap]$ expdp system/ora19c@127.0.0.1:1521/INVENTORYPDB \
> directory=remaptable \
> dumpfile=06May2024_remaptbl_01.dmp \
> logfile=06May2024_remaptbl_01_expdp.log \
> tables=remaptbl_src.tbl1
Export: Release 19.0.0.0.0 - Production on Mon May 6 17:35:21 2024
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Starting "SYSTEM"."SYS_EXPORT_TABLE_01": system/********@127.0.0.1:1521/INVENTORYPDB directory=remaptable dumpfile=06May2024_remaptbl_01.dmp logfile=06May2024_remaptbl_01_expdp.log tables=remaptbl_src.tbl1
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "REMAPTBL_SRC"."TBL1" 5.062 KB 2 rows
Master table "SYSTEM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is:
/home/oracle/dba/ImpdpRemap/06May2024_remaptbl_01.dmp
Job "SYSTEM"."SYS_EXPORT_TABLE_01" successfully completed at Mon May 6 17:35:45 2024 elapsed 0 00:00:23
[oracle@vcentos79-oracle-sa1 ImpdpRemap]$ ls -altr /home/oracle/dba/ImpdpRemap
total 180
drwxr-xr-x. 10 oracle oinstall 4096 May 6 15:52 ..
drwxr-xr-x. 2 oracle oinstall 117 May 6 16:44 Arch
drwxr-xr-x. 3 oracle oinstall 90 May 6 17:35 .
-rw-r-----. 1 oracle oinstall 176128 May 6 17:35 06May2024_remaptbl_01.dmp
-rw-r--r--. 1 oracle oinstall 1139 May 6 17:35 06May2024_remaptbl_01_expdp.log
[oracle@vcentos79-oracle-sa1 ImpdpRemap]$ grep -iE "ORA-|EXPDP-" 06May2024_remaptbl_01_expdp.log
[oracle@vcentos79-oracle-sa1 ImpdpRemap]$
Routine 1: impdp with remap_table to same schema
impdp back to the same schema as new table:
impdp system/ora19c@127.0.0.1:1521/INVENTORYPDB \
directory=remaptable \
dumpfile=06May2024_remaptbl_01.dmp \
logfile=06May2024_remaptbl_01_impdp.log \
tables=remaptbl_src.tbl1 \
remap_table=remaptbl_src.tbl1:tbl2
Expectation: the tbl1 we backed up gets loaded as tbl2 in same source schema remaptbl_src
Output:
[oracle@vcentos79-oracle-sa1 ImpdpRemap]$ impdp system/ora19c@127.0.0.1:1521/INVENTORYPDB \
> directory=remaptable \
> dumpfile=06May2024_remaptbl_01.dmp \
> logfile=06May2024_remaptbl_01_impdp.log \
> tables=remaptbl_src.tbl1 \
> remap_table=remaptbl_src.tbl1:tbl2
Import: Release 19.0.0.0.0 - Production on Mon May 6 18:33:19 2024
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Master table "SYSTEM"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TABLE_01": system/********@127.0.0.1:1521/INVENTORYPDB directory=remaptable dumpfile=06May2024_remaptbl_01.dmp logfile=06May2024_remaptbl_01_impdp.log tables=remaptbl_src.tbl1 remap_table=remaptbl_src.tbl1:tbl2
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "REMAPTBL_SRC"."TBL2" 5.062 KB 2 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "SYSTEM"."SYS_IMPORT_TABLE_01" successfully completed at Mon May 6 18:33:44 2024 elapsed 0 00:00:24
[oracle@vcentos79-oracle-sa1 ImpdpRemap]$
SQL> select table_name from user_tables;
TABLE_NAME
--------------------------------------------------------------------------------
TBL1
TBL2
SQL> select * from tbl1
2 minus
3 select * from tbl2;
no rows selected
SQL> select * from tbl2
2 minus
3 select * from tbl1
4 ;
no rows selected
SQL> show user;
USER is "REMAPTBL_SRC"
SQL>
Routine 2: impdp into a new schema with different table name
impdp system/ora19c@127.0.0.1:1521/INVENTORYPDB \
directory=remaptable \
dumpfile=06May2024_remaptbl_01.dmp \
logfile=06May2024_remaptbl_02_impdp.log \
tables=remaptbl_src.tbl1 \
remap_schema=remaptbl_src:remaptbl_tgt \
remap_tablespace=TBSPC_REMAPTBL_SRC:TBSPC_REMAPTBL_TGT \
remap_table=tbl1:tbl_tgt
Output:
[oracle@vcentos79-oracle-sa1 ImpdpRemap]$ cat 06May2024_remaptbl_02_impdp.log
;;;
Import: Release 19.0.0.0.0 - Production on Mon May 6 18:37:12 2024
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
;;;
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Master table "SYSTEM"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TABLE_01": system/********@127.0.0.1:1521/INVENTORYPDB directory=remaptable dumpfile=06May2024_remaptbl_01.dmp logfile=06May2024_remaptbl_02_impdp.log tables=remaptbl_src.tbl1 remap_schema=remaptbl_src:remaptbl_tgt remap_tablespace=TBSPC_REMAPTBL_SRC:TBSPC_REMAPTBL_TGT remap_table=tbl1:tbl_tgt
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "REMAPTBL_TGT"."TBL_TGT" 5.062 KB 2 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "SYSTEM"."SYS_IMPORT_TABLE_01" successfully completed at Mon May 6 18:37:18 2024 elapsed 0 00:00:05
[oracle@vcentos79-oracle-sa1 ImpdpRemap]$ grep -Ei "ORA-|IMPDP-" 06May2024_remaptbl_02_impdp.log
[oracle@vcentos79-oracle-sa1 ImpdpRemap]$
SQL> conn remaptbl_tgt/remaptbl_tgt@127.0.0.1:1521/INVENTORYPDB
Connected.
SQL> select table_name from user_tables; <<< before impdpd
no rows selected
SQL> select table_name from user_tables; <<< after impdp
TABLE_NAME
--------------------------------------------------------------------------------
TBL_TGT
SQL> select * from TBL_TGT;
ID
----------
1
2
SQL>
Routine 3: add complexity, now the table has a primary key on the only column available, what can it do to both the above routines?
conn remaptbl_src/remaptbl_src@127.0.0.1:1521/INVENTORYPDB
alter table remaptbl_src.tbl1 add constraint id_pk primary key (id);
expdp system/ora19c@127.0.0.1:1521/INVENTORYPDB \
directory=remaptable \
dumpfile=06May2024_remaptbl_02.dmp \
logfile=06May2024_remaptbl_02_expdp.log \
tables=remaptbl_src.tbl1
output:
[oracle@vcentos79-oracle-sa1 ImpdpRemap]$ expdp system/ora19c@127.0.0.1:1521/INVENTORYPDB \
> directory=remaptable \
> dumpfile=06May2024_remaptbl_02.dmp \
> logfile=06May2024_remaptbl_02_expdp.log \
> tables=remaptbl_src.tbl1
Export: Release 19.0.0.0.0 - Production on Mon May 6 18:48:20 2024
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Starting "SYSTEM"."SYS_EXPORT_TABLE_01": system/********@127.0.0.1:1521/INVENTORYPDB directory=remaptable dumpfile=06May2024_remaptbl_02.dmp logfile=06May2024_remaptbl_02_expdp.log tables=remaptbl_src.tbl1
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
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/TABLE
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
. . exported "REMAPTBL_SRC"."TBL1" 5.062 KB 2 rows
Master table "SYSTEM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is:
/home/oracle/dba/ImpdpRemap/06May2024_remaptbl_02.dmp
Job "SYSTEM"."SYS_EXPORT_TABLE_01" successfully completed at Mon May 6 18:48:51 2024 elapsed 0 00:00:30
rerun - routine 1:
impdp system/ora19c@127.0.0.1:1521/INVENTORYPDB \
directory=remaptable \
dumpfile=06May2024_remaptbl_02.dmp \
logfile=06May2024_remaptbl_03_impdp.log \
tables=remaptbl_src.tbl1 \
remap_table=remaptbl_src.tbl1:tbl2
Output:
[oracle@vcentos79-oracle-sa1 ImpdpRemap]$ impdp system/ora19c@127.0.0.1:1521/INVENTORYPDB \
> directory=remaptable \
> dumpfile=06May2024_remaptbl_02.dmp \
> logfile=06May2024_remaptbl_03_impdp.log \
> tables=remaptbl_src.tbl1 \
> remap_table=remaptbl_src.tbl1:tbl2
Import: Release 19.0.0.0.0 - Production on Mon May 6 18:49:21 2024
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Master table "SYSTEM"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TABLE_01": system/********@127.0.0.1:1521/INVENTORYPDB directory=remaptable dumpfile=06May2024_remaptbl_02.dmp logfile=06May2024_remaptbl_03_impdp.log tables=remaptbl_src.tbl1 remap_table=remaptbl_src.tbl1:tbl2
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "REMAPTBL_SRC"."TBL2" 5.062 KB 2 rows
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
ORA-31684: Object type CONSTRAINT:"REMAPTBL_SRC"."ID_PK" already exists
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
Job "SYSTEM"."SYS_IMPORT_TABLE_01" completed with 1 error(s) at Mon May 6 18:49:28 2024 elapsed 0 00:00:07 << this is as per oracle doc a limitation of the option
[oracle@vcentos79-oracle-sa1 ImpdpRemap]$
rerun - routine 2:
impdp system/ora19c@127.0.0.1:1521/INVENTORYPDB \
directory=remaptable \
dumpfile=06May2024_remaptbl_02.dmp \
logfile=06May2024_remaptbl_04_impdp.log \
tables=remaptbl_src.tbl1 \
remap_schema=remaptbl_src:remaptbl_tgt \
remap_tablespace=TBSPC_REMAPTBL_SRC:TBSPC_REMAPTBL_TGT \
remap_table=tbl1:tbl_tgt
Output:
[oracle@vcentos79-oracle-sa1 ImpdpRemap]$ impdp system/ora19c@127.0.0.1:1521/INVENTORYPDB \
> directory=remaptable \
> dumpfile=06May2024_remaptbl_02.dmp \
> logfile=06May2024_remaptbl_04_impdp.log \
> tables=remaptbl_src.tbl1 \
> remap_schema=remaptbl_src:remaptbl_tgt \
> remap_tablespace=TBSPC_REMAPTBL_SRC:TBSPC_REMAPTBL_TGT \
> remap_table=tbl1:tbl_tgt
Import: Release 19.0.0.0.0 - Production on Mon May 6 18:52:13 2024
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Master table "SYSTEM"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TABLE_01": system/********@127.0.0.1:1521/INVENTORYPDB directory=remaptable dumpfile=06May2024_remaptbl_02.dmp logfile=06May2024_remaptbl_04_impdp.log tables=remaptbl_src.tbl1 remap_schema=remaptbl_src:remaptbl_tgt remap_tablespace=TBSPC_REMAPTBL_SRC:TBSPC_REMAPTBL_TGT remap_table=tbl1:tbl_tgt
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "REMAPTBL_TGT"."TBL_TGT" 5.062 KB 2 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
Job "SYSTEM"."SYS_IMPORT_TABLE_01" successfully completed at Mon May 6 18:52:20 2024 elapsed 0 00:00:06
[oracle@vcentos79-oracle-sa1 ImpdpRemap]$
So how to fix routine 1:
precreate the table and add constraint with different name:
SET ECHO OFF
SET PAGESIZE 0
SET LINES 3000
SET LONG 200000
SET FEEDBACK OFF
SET HEADING OFF
SET SERVEROUTPUT ON SIZE 1000000
COLUMN txt FORMAT a2480 WORD_WRAPPED
set trimspool on
EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'PRETTY',false);
execute DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',true);
select dbms_metadata.get_ddl('TABLE','TBL1','REMAPTBL_SRC') from dual;
create table tbl2 (id number);
alter table tbl2 add constraint id_pk2 primary key (id);
impdp system/ora19c@127.0.0.1:1521/INVENTORYPDB \
directory=remaptable \
dumpfile=06May2024_remaptbl_02.dmp \
logfile=06May2024_remaptbl_01_impdp.log \
tables=remaptbl_src.tbl1 \
remap_table=remaptbl_src.tbl1:tbl2 \
table_exists_action=TRUNCATE
Output:
[oracle@vcentos79-oracle-sa1 ImpdpRemap]$ impdp system/ora19c@127.0.0.1:1521/INVENTORYPDB \
> directory=remaptable \
> dumpfile=06May2024_remaptbl_02.dmp \
> logfile=06May2024_remaptbl_01_impdp.log \
> tables=remaptbl_src.tbl1 \
> remap_table=remaptbl_src.tbl1:tbl2 \
> table_exists_action=TRUNCATE
Import: Release 19.0.0.0.0 - Production on Mon May 6 18:57:19 2024
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Master table "SYSTEM"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TABLE_01": system/********@127.0.0.1:1521/INVENTORYPDB directory=remaptable dumpfile=06May2024_remaptbl_02.dmp logfile=06May2024_remaptbl_01_impdp.log tables=remaptbl_src.tbl1 remap_table=remaptbl_src.tbl1:tbl2 table_exists_action=TRUNCATE
Processing object type TABLE_EXPORT/TABLE/TABLE
Table "REMAPTBL_SRC"."TBL2" 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 TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "REMAPTBL_SRC"."TBL2" 5.062 KB 2 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
Job "SYSTEM"."SYS_IMPORT_TABLE_01" successfully completed at Mon May 6 18:57:25 2024 elapsed 0 00:00:06
[oracle@vcentos79-oracle-sa1 ImpdpRemap]$
SQL> select * from tbl2;
ID
----------
1
2
SQL> select * from tbl1
2 minus
3 select * from tbl2;
no rows selected
SQL> select * from tbl2
2 minus
3 select * from tbl1;
no rows selected
SQL>
Common mistakes to avoid:
impdp system/ora19c@127.0.0.1:1521/INVENTORYPDB \
directory=remaptable \
dumpfile=06May2024_remaptbl_01.dmp \
logfile=06May2024_remaptbl_01_impdp.log \
-- tables=remaptbl_src.tbl1 \ ############################> skipping the table selection clause!!! if at all you did an expdp of the whole schema, this can be a disaster
remap_table=remaptbl_src.tbl1:tbl2
Conclusion:
The impdp worked as per the oracle doc.
PodCast url: https://tinyurl.com/pcremaptable
YouTube:
No comments:
Post a Comment