expdp query及parfile的使用

     在使用expdp导出数据时，我们可以使用query参数指定导出表的部分数据，query参数可以直接在expdp命令行中使用，也可以在parfile文件中使用

一：在命令行中使用query

1.执行命令

export ORACLE_SID=XXXXX
expdp \'sys/********* as sysdba\' directory=sfdir dumpfile=test.dmp logfile=test.log tables=scott.test1,scott.test2 query=scott.test1:\"where UA_SERIAL_ID in \(\'96\',\'26\'\)\",scott.test2:\"where FILESIZE=273899\"

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Export: Release 11.2.0.3.0 - Production on Wed Jun 3 11:22:30 2015

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 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
Starting "SYS"."SYS_EXPORT_TABLE_01":  "sys/******** AS SYSDBA" directory=sfdir dumpfile=test.dmp logfile=test.log tables=scott.test1,scott.test2 query=scott.test1:"where UA_SERIAL_ID in ('96','26')",scott.test2:"where FILESIZE=273899" 
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 2.25 GB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "SCOTT"."TEST1"                             172.5 MB 1834060 rows
. . exported "SCOTT"."TEST2"                             27.87 KB     186 rows
Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_01 is:
  /archlog/egcmc_exp/test.dmp
Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at 11:22:57

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
##注意query选项后面的内容，很多符号需要使用转译符号“\”才能保证备份命令的正常运行,如上面()括号,以及‘’单引号,""双引号都需要使用转译符进行转译

2.验证导出结果

SQL> select count(1) from scott.test1 where UA_SERIAL_ID in ('96','26');

  COUNT(1)
----------
   1834060

SQL> select count(1) from scott.test1 where FILESIZE=273899;

  COUNT(1)
----------
       186
##导出结果正确

二：expdp 使用parfile导出

##如果需要导出多个表的部分数据，那么如果用命令方式写，因为需要转译，是很麻烦的，也容易出错，这时我们可以使用parfile文件来避免大量的转译

1.首先看一下我们parfile.txt文件中的内容

cat parfile.txt(parfile可以随意命名)
tables=
(
 CHANNEL.TR_CHANNEL_ACCESS_LOG_BAK,
 scott.test1,
 scott.test2
)
query=
(
 xxxxx.TR_XXXX_ACCESS_LOG_BAK:"where CHANNEL_CODE='10689021144'",
 scott.test1:"where UA_SERIAL_ID in ('96','26')",
 scott.test2:"where FILESIZE=273899"
)

##注意 :"冒号前无空格。表名不能重复使用，表不能有别名

##如果parfile中tables或者query中表名有重复会报如下错误：

ORA-39001: invalid argument value
ORA-39035: Data filter SUBQUERY has already been specified.

2.执行expdp命令

export ORACLE_SID=XXXXX 
expdp \'sys/********* as sysdba\' directory=sfdir dumpfile=export.dmp logfile=export.log parfile=/archlog/parfile.txt

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Export: Release 11.2.0.3.0 - Production on Wed Jun 3 11:27:18 2015

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 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
Starting "SYS"."SYS_EXPORT_TABLE_01":  "sys/******** AS SYSDBA" directory=sfdir dumpfile=export.dmp logfile=export.log parfile=/archlog/parfile.txt 
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 9.982 GB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "XXXXX"."TR_xxxx_ACCESS_LOG_BAK":"ACCESS_LOG_PART11"  8.804 KB       0 rows
. . exported "XXXXX"."TR_xxxx_ACCESS_LOG_BAK":"ACCESS_LOG_PART12"  11.83 KB       8 rows
. . exported "XXXXX"."TR_xxxx_ACCESS_LOG_BAK":"ACCESS_LOG_PART10"  8.804 KB       0 rows
. . exported "SCOTT"."TEST1"                             172.5 MB 1834060 rows
. . exported "SCOTT"."TEST2"                             27.87 KB     186 rows
. . exported "XXXXX"."TR_xxxx_ACCESS_LOG_BAK":"ACCESS_LOG_PART13"  10.68 KB       5 rows
. . exported "XXXXX"."TR_xxxx_ACCESS_LOG_BAK":"ACCESS_LOG_PART14"  8.804 KB       0 rows
. . exported "XXXXX"."TR_xxxx_ACCESS_LOG_BAK":"ACCESS_LOG_PART15"  9.187 KB       1 rows
. . exported "XXXXX"."TR_xxxx_ACCESS_LOG_BAK":"ACCESS_LOG_PART16"  8.804 KB       0 rows
. . exported "XXXXX"."TR_xxxx_ACCESS_LOG_BAK":"ACCESS_LOG_PART17"  9.187 KB       1 rows
. . exported "XXXXX"."TR_xxxx_ACCESS_LOG_BAK":"ACCESS_LOG_PART18"  8.804 KB       0 rows
. . exported "XXXXX"."TR_xxxx_ACCESS_LOG_BAK":"ACCESS_LOG_PART1"      0 KB       0 rows
. . exported "XXXXX"."TR_xxxx_ACCESS_LOG_BAK":"ACCESS_LOG_PART2"      0 KB       0 rows
. . exported "XXXXX"."TR_xxxx_ACCESS_LOG_BAK":"ACCESS_LOG_PART3"      0 KB       0 rows
. . exported "XXXXX"."TR_xxxx_ACCESS_LOG_BAK":"ACCESS_LOG_PART4"      0 KB       0 rows
. . exported "XXXXX"."TR_xxxx_ACCESS_LOG_BAK":"ACCESS_LOG_PART5"      0 KB       0 rows
. . exported "XXXXX"."TR_xxxx_ACCESS_LOG_BAK":"ACCESS_LOG_PART6"      0 KB       0 rows
. . exported "XXXXX"."TR_xxxx_ACCESS_LOG_BAK":"ACCESS_LOG_PART7"      0 KB       0 rows
Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

##我们可以把expdp命令需要使用的参数都写进参数文件中，类似如下：

PARALLEL=3
cluster=no 
COMPRESSION=ALL
DUMPFILE=export_%U.dmp  
DIRECTORY=sfdir   
logfile=export.log
EXCLUDE=TRIGGER,INDEX,STATISTICS
tables=
(
 xxxxx.TR_xxxx_ACCESS_LOG_BAK,
 scott.test1,
 scott.test2
)
query=
(
 xxxxx.TR_xxxx_ACCESS_LOG_BAK:"where CHANNEL_CODE='10689021144'",
 scott.test1:"where UA_SERIAL_ID in ('96','26')",
 scott.test2:"where FILESIZE=273899"
)

然后执行如下命令

export ORACLE_SID=XXXXX
expdp \'sys/*********** as sysdba\' parfile=/archlog/parfile.txt