expdp/impdp的使用总结 (供查詢使用)

expdp/impdp的使用总结

1.使用expdp要先在数据库中创建directory,并给相应的用户read,write权限.
SQL>create directory expdpdir as 'd:';
SQL>grant read,write on directory expdpdir to helios;

2.常用的导出倒入用法
a) 导出导出表：
expdp helios/helios directory=expdpdir dumpfile=11.dmp tables=dep,em,cat
expdp scott/timer DUMPFILE=tmp_200703.dmp DIRECTORY=dmpdir TABLES=tmp QUERY='where status is not null'
impdp helios/helios directory=expdpdir dumpfile=11.dmp tables=dep,em,cat
impdp system/oracle directory=expdpdir dumpfile=11.dmp tables=helios.dep,helios.em,helios.cat

remap_schema=helios:system //倒入到system下面
b) 导出导出schema
expdp helios/helios directory=expdpdir dumpfile=11.dmp schemas=helios
impdp system/oracle directory=expdpdir dumpfile=11.dmp schemas=helios remap_schema=helios:system
c) 导出导出表空间
expdp helios/helios directory=expdpdir dumpfile=11.dmp tablespaces=system,rep_ts
imppdp helios/helios directory=expdpdir dumpfile=11.dmp tablespaces=system,rep_ts
d) 导出导出数据库（导出用户需要dba角色）
expdp helios/helios directory=expdpdir dumpfile=11.dmp full=y
impdp helios/helios directory=expdpdir dumpfile=11.dmp full=y