[Oracle] Oracle data pump的妙用和技巧

1. Oracle 10g里面对分区表export

Wildcards in table/partition names in expdp tables parameter are not supported by 10g expdp:

I:\>expdp directory=TEMP dumpfile=part.dmp logfile=part.log tables="RANGE_SALES:SALES_%_1998"
 
Export: Release 10.2.0.4.0 - Production on Tuesday, 22 May, 2012 14:05:17
 
Copyright (c) 2003, 2007, Oracle.  All rights reserved.
 
Username: scott@orcl
Password:
 
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
UDE-00014: invalid value for parameter, 'tables'.

So if you are using 10g expdp you have to list partitions:

I:\>expdp directory=TEMP dumpfile=part.dmp logfile=part.log tables="RANGE_SALES:SALES_Q1_1998,RANGE_SALES:SALES_Q2_1998,RANGE_SALES:SALES_Q3_1998,RANGE_SALES:SALES_Q4_1998"
 
Export: Release 10.2.0.4.0 - Production on Tuesday, 22 May, 2012 14:19:01
 
Copyright (c) 2003, 2007, Oracle.  All rights reserved.
 
Username: scott@orcl
Password:
 
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SCOTT"."SYS_EXPORT_TABLE_01":  scott/********@orcl directory=TEMP dump
file=part.dmp logfile=part.log tables=RANGE_SALES:SALES_Q1_1998,RANGE_SALES:SALES_Q2_1998,RANGE_SALES:SALES_Q3_1998,RANGE_SALES:SALES_Q4_1998
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 0 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "SCOTT"."RANGE_SALES":"SALES_Q1_1998"           0 KB       0 rows
. . exported "SCOTT"."RANGE_SALES":"SALES_Q2_1998"           0 KB       0 rows
. . exported "SCOTT"."RANGE_SALES":"SALES_Q3_1998"           0 KB       0 rows
. . exported "SCOTT"."RANGE_SALES":"SALES_Q4_1998"           0 KB       0 rows
Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:
  C:\TEMP\PART.DMP
Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at 14:19:12

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
11g expdp does support wildcards:

I:\>C:\app\oracle\product\11.2.0\dbhome_1\BIN\expdp directory=TEMP dumpfile=part.dmp logfile=part.log tables="RANGE_SALES:SALES_%_1998"
 
Export: Release 11.2.0.1.0 - Production on Tue May 22 14:10:21 2012
 
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
 
Username: scott@orcl
Password:
 
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SCOTT"."SYS_EXPORT_TABLE_01":  scott/********@orcl directory=TEMP dump
file=part.dmp logfile=part.log tables=RANGE_SALES:SALES_%_1998
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 0 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "SCOTT"."RANGE_SALES":"SALES_Q1_1998"           0 KB       0 rows
. . exported "SCOTT"."RANGE_SALES":"SALES_Q2_1998"           0 KB       0 rows
. . exported "SCOTT"."RANGE_SALES":"SALES_Q3_1998"           0 KB       0 rows
. . exported "SCOTT"."RANGE_SALES":"SALES_Q4_1998"           0 KB       0 rows
Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:
  C:\TEMP\PART.DMP
Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at 14:10:38


I:\>

>>>>>>>>>>>>>>>>>>>>>>>>Project Sample<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

SQL>  create directory dpump_dir as 'G:\dmpdir';

Directory created.

SQL>  grant read ,write on directory dpump_dir to D_INSIGHT;


Grant succeeded.

>>>>>>export HIS_STORE table <<<<<<<<<<<<<<<<<<<<<<
expdp  drtt/XXXX@DRTT  DIRECTORY=dpump_dir DUMPFILE=STORE%U.dmp  tables=

(HIS_STORE:HISSTORE201212,HIS_STORE:HISSTORE201211,HIS_STORE:HISSTORE201210,HIS_STORE:HISSTORE201209,HIS

_STORE:HISSTORE201208,HIS_STORE:HISSTORE201207,HIS_STORE:HISSTORE201206,HIS_STORE:HISSTORE201205,HIS_ST

ORE:HISSTORE201204,HIS_STORE:HISSTORE201203,HIS_STORE:HISSTORE201202,HIS_STORE:HISSTORE201201,HIS_STOR

E:HISSTORE201112,HIS_STORE:HISSTORE201111,HIS_STORE:HISSTORE201110,HIS_STORE:HISSTORE201109,HIS_STORE:HI

SSTORE201108,HIS_STORE:HISSTORE201107) FILESIZE=2G   LOGFILE=Store_exp.log  parallel=4


expdp  drtt/XXXX@DRTT  DIRECTORY=dpump_dir DUMPFILE=Customer%U.dmp  tables=

(HIS_CUSTOMER:CUSTOMER201212,HIS_CUSTOMER:CUSTOMER201211,HIS_CUSTOMER:CUSTOMER201210,HIS_CUSTOME

R:CUSTOMER201209,HIS_CUSTOMER:CUSTOMER201208,HIS_CUSTOMER:CUSTOMER201207,HIS_CUSTOMER:CUSTOMER2

01206,HIS_CUSTOMER:CUSTOMER201205,HIS_CUSTOMER:CUSTOMER201204,HIS_CUSTOMER:CUSTOMER201203,HIS_CUS

TOMER:CUSTOMER201202,HIS_CUSTOMER:CUSTOMER201201,HIS_CUSTOMER:CUSTOMER201112,HIS_CUSTOMER:CUSTO

MER201111,HIS_CUSTOMER:CUSTOMER201110,HIS_CUSTOMER:CUSTOMER201109,HIS_CUSTOMER:CUSTOMER201108,HIS

_CUSTOMER:CUSTOMER201107) FILESIZE=2G   LOGFILE=CUST_exp.log  parallel=4


D:\>impdp d_insight/XXXX@dinsight   DIRECTORY=dpump_dir DUMPFILE=STORE%U.dmp remap_schema=drtt:d_insight  remap_tablespace=TBS_MASTER:USERS logfile=storedmp.log parallel=4

D:\>impdp d_insight/XXXX@dinsight   DIRECTORY=dpump_dir DUMPFILE=Customer%U.dmp remap_schema=drtt:d_insight  remap_tablespace=TBS_MASTER:USERS logfile=customerdmp.log parallel=4

tbs_name: TBS_MASTER