advertisements
_____________________________________________________________________________________________________________________
This
impdp parameter is used to rename the table during the import operation. These
are the main activities can be performed by this parameter.
- Rename the one or more tables into the
same schema during import
- Rename the one or more tables and
import into different schema in conjunction with remap_schema.
- 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
- 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
- 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
- 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_____________________________________________________________________________________________________________________
0 comments:
Post a Comment