1.创建数据泵目录
查看是否存在目录
select * from dba_directories;
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)
