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_dirdumpfile=aaq1.dmptables=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.dmp3、expdp voice/oracle@orcl TABLES=busicall,busiexcute,business,keyinfo dumpfile=expdp.dmp DIRECTORY=data_pump_dir4、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_dirdumpfile=aaq1.dmptables=busicall,busiexcute,business,keyinfo QUERY = 'business:"where busiid =100"'7、expdp voice/oracle@orcl directory=data_pump_dirdumpfile=aaq1.dmptables=busicall,busiexcute,business,keyinfo QUERY = 'business:"where endtime < sysdate"'8、expdp voice/oracle@orcl directory=data_pump_dirdumpfile=aaq1.dmptables=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=replacetable_exists_action=replacetable_exists_action=appendtable_exists_action=skiptable_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_dirDUMPFILE=data.dmpTABLES=employees,job_historyQUERY=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
页:
[1]