Data Pump Parameter-REMAP_TABLE


Data Pump Parameter-REMAP_TABLE

This parameter allows a table to be renamed during the import operations performed using the TRANSPORTABLE method. It can also be used to alter the base table name used during PARTITION_OPTIONS imports. The syntax is shown below.

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

An example is shown below.

SQL> select * from ram;

        NO
———-
         1

SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
—————————— ——- ———-
BONUS                          TABLE
DEPT                           TABLE
EMP                            TABLE
RAM                            TABLE
SALE                           TABLE
SALE_SALES_Q1                  TABLE
SALE_SALES_Q2                  TABLE
SALGRADE                       TABLE

8 rows selected.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@oracle11g ~]$ expdp scott/tiger dumpfile=ramtest.dmp logfile=ramlog.log  tables=ram directory=TEST_DIR

Export: Release 11.2.0.3.0 – Production on Mon Aug 31 16:52:08 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting “SCOTT”.”SYS_EXPORT_TABLE_01″:  scott/******** dumpfile=ramtest.dmp logfile=ramlog.log tables=ram directory=TEST_DIR
Estimate in progress using BLOCKS method…
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported “SCOTT”.”RAM”                               5.007 KB       1 rows
Master table “SCOTT”.”SYS_EXPORT_TABLE_01″ successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:
  /u01/dump/ramtest.dmp
Job “SCOTT”.”SYS_EXPORT_TABLE_01″ successfully completed at 16:52:18

[oracle@oracle11g ~]$
[oracle@oracle11g ~]$ impdp scott/tiger  dumpfile=ramtest.dmp logfile=ramlog.log remap_table=scott.ram:sita directory=TEST_DIR

Import: Release 11.2.0.3.0 – Production on Mon Aug 31 16:53:27 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table “SCOTT”.”SYS_IMPORT_FULL_01″ successfully loaded/unloaded
Starting “SCOTT”.”SYS_IMPORT_FULL_01″:  scott/******** dumpfile=ramtest.dmp logfile=ramlog.log remap_table=scott.ram:sita directory=TEST_DIR
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported “SCOTT”.”SITA”                              5.007 KB       1 rows
Job “SCOTT”.”SYS_IMPORT_FULL_01″ successfully completed at 16:53:31

[oracle@oracle11g ~]$ sqlplus scott/tiger

SQL*Plus: Release 11.2.0.3.0 Production on Mon Aug 31 16:53:38 2015

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
—————————— ——- ———-
BONUS                          TABLE
DEPT                           TABLE
EMP                            TABLE
RAM                            TABLE
SALE                           TABLE
SALE_SALES_Q1                  TABLE
SALE_SALES_Q2                  TABLE
SALGRADE                       TABLE
SITA                           TABLE

9 rows selected.

SQL> select * from sita;

        NO
———-
         1

Existing tables are not renamed, only tables created by the import.

  • August 31, 2015 | 13 views