REMAP TABLE OPTION IN DATAPUMP

                                 REMAP TABLE IN DATAPUMP

Remap table option is available in Oracle 11g .  While importing table you can change the table name .

Purpose

Allows you to rename tables during an import operation.

Syntax and Description

You can use either of the following syntaxes (see the Usage Notes below):

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

OR

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

You can use the REMAP_TABLE parameter to rename entire tables or to rename table partitions if the table is being departitioned.

You can also use it to override the automatic naming of table partitions that were exported.

Usage Notes

Be aware that with the first syntax, if you specify REMAP_TABLE=A.B:C, then Import assumes that A is a schema name, B is the old table name, and C is the new table name. To use the first syntax to rename a partition that is being promoted to a nonpartitioned table, you must specify a schema name.

To use the second syntax to rename a partition being promoted to a nonpartitioned table, you only need to qualify it with the old table name. No schema name is required.

Restrictions

• Only objects created by the Import will be remapped. In particular, preexisting tables will not be remapped.
• The REMAP_TABLE parameter will not work if the table being remapped has named constraints in the same schema and the constraints need to be created when the table is created.

How to Perform

Step1:  Export the EMP table

racle@racs ~]$ expdp dumpfile=d1.dmp directory=dps  tables=emp

Export: Release 11.2.0.3.0 - Production on Fri Sep 20 13:06:35 2013

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

Username: dpusr

Password:

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 "DPUSR"."SYS_EXPORT_TABLE_01":  dpusr/******** dumpfile=d1.dmp directory=dps reuse_dumpfiles=yes tables=emp

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

Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

. . exported "DPUSR"."EMP"                               8.578 KB      14 rows

Master table "DPUSR"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded

******************************************************************************

Dump file set for DPUSR.SYS_EXPORT_TABLE_01 is:

  /u01/apps/home/oracle/dp/d1.dmp

Job "DPUSR"."SYS_EXPORT_TABLE_01" successfully completed at 13:06:52

Step2:  Import   the table

as a AMKS table

[oracle@racs ~]$ impdp dumpfile=d1.dmp directory=dps tables=EMP remap_table=EMP:AMK

Import: Release 11.2.0.3.0 - Production on Fri Sep 20 11:14:38 2013

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

Username: dpusr

Password:

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 "DPUSR"."SYS_IMPORT_TABLE_02" successfully loaded/unloaded

Starting "DPUSR"."SYS_IMPORT_TABLE_02":  dpusr/******** dumpfile=d1.dmp directory=dps tables=dpusr.test remap_table=EMP:AMKS

Processing object type TABLE_EXPORT/TABLE/TABLE

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

. . imported "DPUSR"."AMKS"                               12.71 KB    1000 rows

Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

ORA-39083: Object type TABLE_STATISTICS failed to create with error:

ORA-20000: TABLE "DPUSR"."EMP" does not exist or insufficient privileges

Failing sql is:

DECLARE   c varchar2(60);   nv varchar2(1);   df varchar2(21) := 'YYYY-MM-DD:HH24:MI:SS';   s varchar2(60) := 'DPUSR';   t varchar2(60) := 'TEST';   p varchar2(1);   sp varchar2(1);   stmt varchar2(300) := 'INSERT INTO "SYS"."IMPDP_STATS" (type,version,c1,c2,c3,c4,c5,n1,n2,n3,n4,n5,n6,n7,n8,n9,n10,n11,d1,r1,r2,ch1,flags,cl1) VALUES (:1,6,:2,:3,:4,:5,:

Job "DPUSR"."SYS_IMPORT_TABLE_02" completed with 1 error(s) at 11:14:45

This is because DPUSR dont have table statistics privilege, you can exclude this at this stage.

Step3:  Now grant the privilege or exlcude the statistics 

[oracle@inatechrac1 ~]$ impdp dumpfile=d1.dmp directory=dps tables=EMP remap_table=EMP:AMKS exclude=statistics

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 "DPUSR"."SYS_IMPORT_TABLE_02" successfully loaded/unloaded
Starting "DPUSR"."SYS_IMPORT_TABLE_02":  dpusr/******** dumpfile=d1.dmp directory=dps tables=dpusr.test remap_table=test:AMKS exclude=STATISTICS
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "DPUSR"."AMKS"                              12.71 KB    1000 rows
Job "DPUSR"."SYS_IMPORT_TABLE_02" successfully completed at 11:16:27