oracle数据泵导入导出

1.创建数据泵目录


create directory home as '/home/expdp';


2.授权




grant read,write on directory home to scott;




数据字典dba_directories






3.导出


全库导出


expdp system/oracle compression=all full=y  directory=home dumpfile=20161024full.dmp logfile=20161024.log




按表空间导出


expdp system/oracle  compression=all tablespaces=users,example directory=home dumpfile=20161024tb.dmp logfile=20161024tb.log


按用户导出


expdp system/oracle compression=all  schemas=hr,scott directory=home dumpfile=20161024us.dmp logfile=20161024us.log




按表导出


expdp system/oracle compression=all  tables=scott.emp,hr.jobs directory=home dumpfile=20161024tbs.dmp logfile=20161024tabs.log




导出表中的行


expdp system/oracle compression=all  tables=scott.emp,hr.jobs  query=hr.employees:\"where job_id\=\'it_prog\' and salary \>8000\" ,scott.emp:\"where sal\>4500\" directory=home dumpfile=201610241.dmp logfile=201610241.log




按flash_time或flashback_scn导出与闪回时间点查询相一致的的数据


expdp system/oracle compression=all flashback_time=\"systimestamp \- interval\'10\' minute\" tables=scott.emp,hr.jobs directory=home dumpfile=20161024tbs1.dmp logfile=20161024tabs1.log






导入


1.全库导入


impdp system/oracle full=y   directory=home dumpfile=201610241.dmp  logfile=20161024im.log;




2.表空间导入


impdp system/oracle  tablespaces=example directory=home dumpfile=201610241.dmp  logfile=20161024im.log;


3.用户导入


impdp system/oracle  schemas=hr,scott directory=home dumpfile=201610241.dmp  logfile=20161024im.log;


4.表导入


impdp syste/oracle tables=hr.employees,scott.test directory=home dumpfile=201610241.dmp  logfile=20161024im.log;


5.按行导入


impdp system/oracle compression=all  tables=scott.emp,hr.jobs  query=hr.employees:\"where job_id\=\'it_prog\' and salary \>8000\" ,scott.emp:\"where sal\>4500\" directory=home dumpfile=201610241.dmp logfile=201610241.log




6.remap_schema


impdp system/oracle  schemas=hr directory=home dumpfile=201610241.dmp  logfile=20161024im.log 
remap_schema=hr:hr2(将hr用户的数据导入用户hr2)


只有全导或者按用户导出的数据泵文件才会在导入时自动创建用户