Expdp@impdp用法

一、創建導出數據存放目錄

如：mkdir /u01/dump

二、創建directory邏輯目錄

CREATE OR REPLACE DIRECTORY DATA_DUMP_DIR AS '/u01/dump';

三、導出數據

1)按用戶導

expdp mctpsa/mctpsa@ipap schemas=mctpsa dumpfile=expdp.dmp DIRECTORY=DATA_DUMP_DIR;

2)並行進程parallel

expdp mctpsa/mctpsa@ipap directory=DATA_DUMP_DIR dumpfile=mctpsa3.dmp parallel=40 job_name=mctpsa3

3)按表名導

expdp mctpsa/mctpsa@ipap TABLES=sa_user,sa_dept dumpfile=expdp.dmp DIRECTORY=DATA_DUMP_DIR;

4)按查詢條件導

expdp mctpsa/mctpsa@ipap directory=DATA_DUMP_DIR dumpfile=expdp.dmp Tables=sa_user query='WHERE id=20';

5)按表空間導

expdp system/manager DIRECTORY=DATA_DUMP_DIR DUMPFILE=tablespace.dmp TABLESPACES=mctp,mctpsa;

6)導整個資料庫

expdp system/manager DIRECTORY=DATA_DUMP_DIR DUMPFILE=full.dmp FULL=y;

四、還原數據

1)導到指定用戶下

impdp mctpsa/mctpsa DIRECTORY=DATA_DUMP_DIR DUMPFILE=expdp.dmp SCHEMAS=mctpsa;

2)改變表的owner

impdp system/manager DIRECTORY=DATA_DUMP_DIR DUMPFILE=expdp.dmp TABLES=mctpsa.dept REMAP_SCHEMA=mctpsa:system;

3)導入表空間

impdp system/manager DIRECTORY=DATA_DUMP_DIR DUMPFILE=tablespace.dmp TABLESPACES=example;

4)導入資料庫

impdp system/manager DIRECTORY=DATA_DUMP_DIR DUMPFILE=full.dmp FULL=y;

5)追加數據

impdp system/manager DIRECTORY=dpdata1 DUMPFILE=expdp.dmp SCHEMAS=system TABLE_EXISTS_ACTION=append;