expdp—impdp

exp导出方式：四张关联表根据同ID导出数据

exp mobilevoice/oracle@ORADB tables=(business,busicall,busiexcute,keyinfo) file=d:\t.dmp query='where busiid not in( select busiid from business where endtime ">" (sysdate - 90))'

expdp两种导出方式  Oracle10g中才能使用

1.使用命令行：

1、expdp voice/oracle@orcl directory=data_pump_dir  dumpfile=aaq1.dmp  tables=busicall,busiexcute,business,keyinfo QUERY = 'business:"where endtime < sysdate"','keyinfo:"where busiid in (select busiid from business where endtime < sysdate)"'


2、expdp voice/oracle schemas=voice directory=data_pump_dir dumpfile=expdp.dmp


3、expdp voice/oracle@orcl TABLES=busicall,busiexcute,business,keyinfo dumpfile=expdp.dmp DIRECTORY=data_pump_dir


4、expdp voice/oracle@orcl TABLES=busicall,busiexcute,business,keyinfo dumpfile=expdp.dmp DIRECTORY=data_pump_dir QUERY='where busiid = 100'


5、expdp voice/oracle@orcl TABLES=busicall,busiexcute,business,keyinfo dumpfile=expdp.dmp DIRECTORY=data_pump_dir QUERY='where endtime "<" sysdate'


6、expdp voice/oracle@orcl directory=data_pump_dir  dumpfile=aaq1.dmp  tables=busicall,busiexcute,business,keyinfo QUERY = 'business:"where busiid =100"'


7、expdp voice/oracle@orcl directory=data_pump_dir  dumpfile=aaq1.dmp  tables=busicall,busiexcute,business,keyinfo QUERY = 'business:"where endtime < sysdate"'


8、expdp voice/oracle@orcl directory=data_pump_dir  dumpfile=aaq1.dmp  tables=busicall,busiexcute,business,keyinfo QUERY = 'business:"where endtime < sysdate"','keyinfo:"where busiid = "' 




9、expdp voice/oracle@orcl TABLES=busicall,busiexcute,business,keyinfo dumpfile=expdp.dmp DIRECTORY=data_pump_dir QUERY='where endtime "<" sysdate'

  

impdp导入方式：

impdp mobilevoice/oracle DIRECTORY=data_pump_dir DUMPFILE=data.dmp TABLE_EXISTS_ACTION=replace
table_exists_action=replace
table_exists_action=append
table_exists_action=skip
table_exists_action=truncate

上面table_exists_action中的四种模式用来解决：
当使用IMPDP完成数据库导入时，如遇到表已存在时，Oracle提供给我们如下四种处理方式：
a.忽略（SKIP，默认行为）；
b.在原有数据基础上继续增加（APPEND）；
c.先DROP表，然后创建表，最后完成数据插入（REPLACE）；
d.先TRUNCATE，再完成数据插入（TRUNCATE）。

参考：http://www.51testing.com/?uid-116228-action-viewspace-itemid-234367

2.使用参数文件：

DIRECTORY=data_pump_dir
DUMPFILE=data.dmp
TABLES=employees,job_history
QUERY=employees:"WHERE department_id !=50 ORDER BY employee_id"
QUERY=job_history:"WHERE job_id = 'IT_PROG'"

把上面的内容保存为exp.par文件,然后执行
expdp hr/hr parfile=exp.par


参考：http://my-ora.iteye.com/blog/1133397
      http://www.2cto.com/database/201202/120126.html