oracle 11g expdp/impdp 指令速查

一、創建導出數據存放目錄
如：mkdir /ap/dbbackup

二、創建directory邏輯目錄
CREATE OR REPLACE DIRECTORY DMP AS '/ap/dbbackup';
查詢所有 DIRECTORY：
    select * from all_directories where upper(directory_name) = 'xxxxx'

三、導出數據
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;