Monday, September 23, 2013

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


No comments:

Post a Comment

Share your knowledge it really improves, don't show off...