Data Pump impdp remap_table Option to Change the Table Name During Import


This impdp parameter is used to rename the table during the import operation. These are the main activities can be performed by this parameter.
  1. Rename the one or more tables into the same schema during import
  2. Rename the one or more tables and import into different schema in conjunction with remap_schema.
  3. When you import partitioned tables using transportable tablespace option, it automatically copied to separate tables with default table name as tablename.partionname. You can use remap_table option to override this and to rename the tables during the import.

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

  1. I have a table called emp in my schema and taking the export

$ expdp job_name=expjob tables=sthomas.emp dumpfile=exp_emp.dmp logfile=exp_emp.log directory=exp_dir

Export: Release 11.2.0.2.0 - Production on Fri Nov 23 01:35:15 2012

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

Username: / as sysdba

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
Starting "SYS"."EXPJOB":  /******** AS SYSDBA job_name=expjob tables=sthomas.emp dumpfile=exp_emp.dmp logfile=exp_emp.log directory=exp_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
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "STHOMAS"."EMP"                             8.578 KB      14 rows
Master table "SYS"."EXPJOB" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.EXPJOB is:
  /u01/zfssa/dev/backup1/export/exp_emp.dmp
Job "SYS"."EXPJOB" successfully completed at 01:35:28

  1. Example for import the table into same schema with different name with remap_table option.

impdp full=y dumpfile=exp_emp.dmp logfile=imp_exp_emp.log directory=exp_dir remap_table=emp:emp_bkup

Import: Release 11.2.0.2.0 - Production on Fri Nov 23 01:37:37 2012

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

Username: / as sysdba

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
Master table "SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_FULL_01":  /******** AS SYSDBA full=y dumpfile=exp_emp.dmp logfile=imp_exp_emp.log directory=exp_dir remap_table=emp:emp_bkup
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "STHOMAS"."EMP_BKUP"                        8.578 KB      14 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SYS"."SYS_IMPORT_FULL_01" successfully completed at 01:37:42

SQL> select * from tab where tname like 'EMP%';

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
EMP                            TABLE
EMP_BKUP                       TABLE

  1. Example for import the table into different schema with different name with remap_table and remap_schema options. The statistics import will throw error during the import as the statistics referring the original table which does not exist in the new schema. This error can be ignored.

$ impdp full=y dumpfile=exp_emp.dmp logfile=imp_exp_emp.log directory=exp_dir remap_schema=sthomas:test remap_table=emp:emp_bkup

Import: Release 11.2.0.2.0 - Production on Fri Nov 23 01:44:29 2012

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

Username: / as sysdba

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
Legacy Mode Active due to the following parameters:
Legacy Mode Parameter: "statistics=none" Location: Command Line, ignored.
Master table "SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_FULL_01":  /******** AS SYSDBA full=y dumpfile=exp_emp.dmp logfile=imp_exp_emp.log directory=exp_dir remap_schema=sthomas:test remap_table=emp:emp_bkup
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "TEST"."EMP_BKUP"                           8.578 KB      14 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
ORA-39083: Object type TABLE_STATISTICS failed to create with error:
ORA-20000: TABLE "TEST"."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) := 'TEST';   t varchar2(60) := 'EMP';   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,:6,
Job "SYS"."SYS_IMPORT_FULL_01" completed with 1 error(s) at 01:44:40


DBA Tips Data Pump Reference

0 comments:

Post a Comment

 

dba topics. Copyright 2011-16 All Rights Reserved | Site Map | Contact | Disclaimer | Google