oracle 中 expdp/impdp的使用方法(转)

1、创建directory

create directory dumpdir as '/data/backup';

greate read,wirte on directory dumpdir to system;

2、导出全库

expdp system/oracle directory=dumpdir dumpfile=expdp_db01_full.dmp logfile=expdp_db01_full.log full=y

expdp system/oracle directory=dumpdir dumpfile=expdp_db01_full_%U.dmp logfile=expdp_db01_full.log full=y parallel=2

3、导出指定用户

expdp system/oracle directory=dumpdir dumpfile=expdp_db01_user.dmp logfile=expdp_db01_user.log schemas=test1,test2

4、导出指定表空间

expdp system/oracle directory=dumpdir dumpfile=expdp_db01_tspache.dmp logfile=expdp_db01_tspache.log tablespaces=test,test

5、导出指定表

expdp system/oracle directory=dumpdir dumpfile=expdp_db01_tb.dmp logfile=expdp_db01_tb.log tables=test1,test2

6、按照查询条件导出

expdp system/oracle directory=dumpdir dumpfile=expdp_db01_q.dmp logfile=expdp_db01_q.log tables=test query='where id >5 and id <10'

impdp使用方法

1、创建directory



create directory dumpdir as '/data/backup';

greate read,wirte on directory dumpdir to system;

2、导入全库



impdp system/oracle directory=dumpdir dumpfile=expdp_db01_full.dmp logfile=expdp_db01_full.log full=y

impdp system/oracle directory=dumpdir dumpfile=expdp_db01_full_%U.dmp logfile=expdp_db01_full.log full=y parallel=2

3、按用户导入



impdp system/oracle directory=dumpdir dumpfile=expdp_db01_user.dmp logfile=expdp_db01_user.log schemas=test1,test2

impdp system/oracle directory=dumpdir dumpfile=expdp_db01_user.dmp logfile=expdp_db01_user.log schemas=test1,test2 remap_schema=test2:test3

4、按表空间导入

impdp system/oracle directory=dumpdir dumpfile=expdp_db01_tspache.dmp logfile=expdp_db01_tspache.log tablespaces=test1,test2

5、按表导入



impdp system/oracle directory=dumpdir dumpfile=expdp_db01_tb.dmp logfile=expdp_db01_tb.log tables=test1,test2

impdp system/oracle directory=dumpdir dumpfile=expdp_db01_tb.dmp logfile=expdp_db01_tb.log tables=test1,test2 remap_schema=system:test2

impdp test1/oracle directory=dumpdir dumpfile=expdp_db01_tb.dmp logfile=expdp_db01_tb.log tables=test1

6、按照查询条件导入

impdp system/oracle directory=dumpdir dumpfile=expdp_db01_q.dmp logfile=expdp_db01_q.log tables=test1 query='where id >5 and id <10'

其他



--查看目录

select * from dba_directories;

--查看权限

select * from dba_sys_privs where grantee='SYSTEM';

--删除不需要的目录

drop directory dumpdir ;

--获取创建表空间语句

select dbms_metadata.get_ddl('TABLESPACE','TEST') from dual;

--查看jobs

select * from dba_datapump_jobs;