> 数据库 > Oracle >

oracle数据泵笔记

1.创建目录
查询已有目录:select * from dba_directories
创建并授权:
CREATE DIRECTORY dump_dir AS '/tmp/'
grant read,write on directory dump_dir to snkData;

2.导出数据
(1)按用户导出:PARALLEL是并发度,2和4都测试过
expdp snkData/1234 DIRECTORY=dump_dir DUMPFILE=test.dmp SCHEMAS=snkData PARALLEL=2
(2)按表导出
expdp scott/tiger DIRECTORY=dump_dir DUMPFILE=tab.dmp TABLES=dept,emp logfile=exp.log;
(3)按表空间:注意用system用户
Expdp system/manager DIRECTORY=dump_dir DUMPFILE=tablespace.dmp TABLESPACES=user01,user02 logfile=/exp.log;
(4)导出整库
Expdp system/manager DIRECTORY=dump_dir DUMPFILE=full.dmp FULL=Y logfile=/exp.log;

3.导入数据
(1)导入用户数据
Impdp scott/tiger DIRECTORY=dump_dir DUMPFILE=schema.dmp SCHEMAS=scott logfile=/exp.log;
用户映射:
Impdp system/manager DIRECTORY=dump_dir DUMPFILE=schema.dmp SCHEMAS=scott REMAP_SCHEMA=scott:system logfile=/exp.log PARALLEL=2;

(2)导入表:将dept emp导出用户scott对应空间内
Impdp scott/tiger DIRECTORY=dump_dir DUMPFILE=tab.dmp TABLES=dept,emp logfile=/exp.log;

(3)导入表空间
Impdp system/manager DIRECTORY=dump_dir DUMPFILE=tablespace.dmp TABLESPACES=user01 logfile=/exp.log;

(4)导入整库
Impdp system/manager DIRECTORY=dump_dir DUMPFILE=full.dmp FULL=y logfile=/exp.log;



(责任编辑:IT)