DOYENSYS Knowledge Portal




We Welcome you to visit our DOYENSYS KNOWLEDGE PORTAL : Doyensys Knowledge Portal




Monday, August 31, 2015

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.

1 comment:

Sridevi K said...


Regards
Sridevi Koduru (Senior Oracle Apps Trainer Oracleappstechnical.com)
LinkedIn profile - https://in.linkedin.com/in/sridevi-koduru-9b876a8b
Please Contact for One to One Online Training on Oracle Apps Technical, Financials, SCM, SQL, PL/SQL, D2K at training@oracleappstechnical.com | +91 - 9581017828.