接上文Oracle Data Pump（基础使用) http://www.821121.com/?p=2086

3.进阶用法 

nohup expdp system/Windows2012  directory=HOME dumpfile=expdp_ANJOY_%U.dmp logfile=exp_ANJOY.log schemas=ANJOY job_name=exp_ANJOY COMPRESSION=all parallel=8 cluster=n  exclude=STATISTICS &

3.1.INCLUDE

INCLUDE=TABLE_DATA:IN (select partition_name from dba_tab_partitions where table_name in (‘TABLE_NAME1′,’TABLE_NAME2′…) and partition_name like ‘%_P%’ and substr(partition_name,-4,4) < to_char(sysdate,’’) and partition_name not like ‘%P2013%’)

3.2.EXCLUDE

expdp scott/tiger DIRECTORY=dump_dir DUMPFILE=halberd.dup    SCHEMAS = scott  TABLE:\"IN ('EMP', 'DEPT')\"
exclude=table,index,INDEX_STATISTICS,TABLE_STATISTICS

3.3.QUERY

QUERY=emp:\"WHERE username in ('aaa','bb')\",taba:\"WHERE rownum<5\"

3.4.PARFILE

expdp \'sys/********* as sysdba\' directory=sfdir  logfile=export.log parfile=parfile.txt

cat parfile.txt
dumpfile=export.dmp
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"
)

注：windows的parfile要用UE或者notepad++等工具编辑并保存为uft8编码，不要用windows自带的记事本！不要用windows自带的记事本！不要用windows自带的记事本！

3.5.不生成文件直接导入

impdp \'\/ as sysdba\' parfile=test.par/
cat test.par
directory=dump_dir
logfile=test.log
schemas=test
query="where create_date > last_day(add_months(sysdate,-1)) and create_date <= last_day(sysdate)"
transform=segment_attributes:n
network_link=to_aibcrm
table_exists_action=append

注：可以不生成dmp文件，直接将数据抽取到目标数据。在迁移大量数据而没有充足存储空间时，这是一个救命稻草。 最关键的点就是在目标端执行impdp的时候，使用network_link，直接从源库抽取数据

3.6.不知道密码

expdp \"/ as sysdba\" --操作系统认证

3.7.去除段属性

deferred_segment_creation=true 延迟段创建，空表默认 不分配段
TRANSFORM=segment_attributes:n:table --对表去除段属性

3.8.lob字段

ORA-39126: Workerunexpected fatal error in KUPW$WORKER.FETCH_XML_OBJECTS[INDEX:"SAPSR3"."LXE_OBJ_EXP_LANG~0"]
ORA-01555: snapshottoo old: rollback segment number 11 with name "_SYSSMU11_2268899145$"too small
ORA-06512: at"SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at"SYS.KUPW$WORKER", line 11259
解决办法：
增大undo_retention
扩大UNDO大小
将lob字段默认属性basicfiles转换为securefiles，则导出时间大大减少，Doc ID 728758.1

ora-31693 :table data object xx.xxxx failed to load/unload and is being skipped due to error;

ora-29913 :error in executing ODCIEXTTABLEPOPULATE callout

ora-22923 :amount of data specified in streaming LOB write is 0

3.9.ATTACH

nohup expdp system/oracle directory=HOME dumpfile=expdp_ANJOY_%U.dmp logfile=exp_ANJOY.log job_name=exp_ANJOY COMPRESSION=all parallel=8 cluster=n  exclude=STATISTICS   schemas=dbis  job_name=dbis &

expdp system/oracle123 attach=dbis

Export> help

The following commands are valid while in interactive mode.
Note: abbreviations are allowed.

ADD_FILE
Add dumpfile to dumpfile set.

CONTINUE_CLIENT
Return to logging mode. Job will be restarted if idle.

EXIT_CLIENT
Quit client session and leave job running.

FILESIZE
Default filesize (bytes) for subsequent ADD_FILE commands.

HELP
Summarize interactive commands.

KILL_JOB
Detach and delete job.

PARALLEL
Change the number of active workers for current job.

REUSE_DUMPFILES
Overwrite destination dump file if it exists [NO].

START_JOB
Start or resume current job.
Valid keyword values are: SKIP_CURRENT.

STATUS
Frequency (secs) job status is to be monitored where
the default [0] will show new status when available.

STOP_JOB
Orderly shutdown of job execution and exits the client.
Valid keyword values are: IMMEDIATE.

STOP_WORKER
Stops a hung or stuck worker.

TRACE
Set trace/debug flags for the current job.

3.10.existing object

解决办法：
select * from dba_datapump_jobs;

DROP TABLE OWNER_NAME.JOB_NAME;