oracle 導出導入表
2021-10-22 16:59:47

? 導出多個表

exp LSXYYSZHMRMS/******@PK99SERVICE file=d:fuhcx.dmp? tables=(fhcxgxxx,fhcxjcxx,fhcxlbxx,fhcxlpz,fhcxlxx,fhcxtjgxxx,fhcxtjxx)?

導入多個表

? ?imp userid=system/******@PK99SERVICE file=d:fuhcx.dmp? fromuser=LSXYYSZHMRMS touser=SZHMRMS2021

?

不導出某些表

expdp pankumrms/******* directory=dump_dir? dumpfile=TLFCSZHMRMS.DMP? EXCLUDE=TABLE:"IN ('BINGANFILEMX')"

expdp pkhqms2021/******@orcl directory=dump_dir dumpfile=pkhqms2021test3.DMP EXCLUDE=TABLE:"IN ('PATIENTBASEINFOZY','XYBASYJYJG')"

expdp pkhqms2021/******@orcl?schemas=pkhqms2021,pkhqmszy2021 directory=dump_dir dumpfile=pkhqms2021test3.DMP EXCLUDE=TABLE:"IN ('PATIENTBASEINFOZY','XYBASYJYJG')"

?expdp pkhqms2021/*******@orcl schemas=pkhqms2021 dumpfile=pkhqms2021test.dmp DIRECTORY=dump_dir EXCLUDE=TABLE:"IN('PATIENTBASEINFOZY')"

expdp pkhqms2021/*******@orcl schemas=pkhqms2021 dumpfile=pkhqms2021test1.dmp DIRECTORY=dump_dir EXCLUDE=TABLE:"IN('PATIENTBASEINFOZY')",EXCLUDE=TABLE:"IN('XYBASYJYJG')"

?

需要再cmd中執行 是轉義字符

導入剛才的

?impdp? pankumrms/*******@orcl directory=dump_dir dumpfile=TLFCSZHMRMS.DMP? remap_schema=pankumrms:TLSTLFCSZHMRMS

?impdp system/*****@orcl? dumpfile=20211022smartemrmed90.dmp DIRECTORY=dump_dir remap_schema=smartemr90:XCYYYEMR90,smartmed90:XCYYYHIS90

exclude：用于排除導入/導出某些對象

include：用于指定導入/導出某些對象

?

2、參數語法

exclude=[object_type]:[name_clause],[object_type]:[name_clause]

include=[object_type]:[name_clause],[object_type]:[name_clause]

?

object_type：對象類型，如：table、view、index、procedure、pakage、procobj、db_link等

name_clause：sql表達式，用于過濾對象，如：in ('TABLE_A','TABLE_B')、='TABLE_A'、like '%A'等，這些表達式需要放在雙引號“?”中

注意：若是在命令行中使用，WINDOWS系統中需要在雙引號“ ”前加入轉義字符：\'' ?\''

如：\" in ('TABLE_A','TABLE_B') \" ?或者? \" ='TABLE_A'?\"

?

3、參數使用舉例

（1）導入導出指定表TABLE_A和TABLE_B
impdp userName/password directory=J_DATA dumpfile=fileName.DMP include=table:"in('TABLE_A','TABLE_B')";

expdp userName/password directory=J_DATA dumpfile=fileName.DMP
logfile=logfileName.LOG include=table:"in('TABLE_A','TABLE_B')";

（2）導入導出數據時排除TABLE_A和TABLE_B
impdp userName/password directory=J_DATA dumpfile=fileName.DMP
exclude=table:"in('TABLE_A','TABLE_B')";

expdp userName/password directory=J_DATA dumpfile=fileName.DMP
logfile=logfileName.LOG exclude=table:"in('TABLE_A','TABLE_B')";

（3）導入導出指定存儲過程PROCEDURE_A和PROCEDURE_B
impdp userName/password directory=J_DATA dumpfile=fileName.DMP
include=procedure:"in('PROCEDURE_A','PROCEDURE_B')";

expdp userName/password directory=J_DATA dumpfile=fileName.DMP
logfile=logfileName.LOG include=procedure:"in('PROCEDURE_A','PROCEDURE_B')";

（4）導入導出數據時排除PROCEDURE_A和PROCEDURE_B
impdp userName/password directory=J_DATA dumpfile=fileName.DMP
exclude=procedure:"in('PROCEDURE_A','PROCEDURE_B')";

expdp userName/password directory=J_DATA dumpfile=fileName.DMP
logfile=logfileName.LOG exclude=procedure:"in('PROCEDURE_A','PROCEDURE_B')";

（5）導入導出指定定時任務JOB_A和JOB_B
impdp userName/password directory=J_DATA dumpfile=fileName.DMP include=procobj:"in('JOB_A','JOB_A')";

expdp userName/password directory=J_DATA dumpfile=fileName.DMP
logfile=logfileName.LOG include=procobj:"in('JOB_A','JOB_B')";

（6）導入導出時排除JOB_A和JOB_B
impdp userName/password directory=J_DATA dumpfile=fileName.DMP
exclude=procobj:"in('JOB_A','JOB_B')";

expdp userName/password directory=J_DATA dumpfile=fileName.DMP
logfile=logfileName.LOG exclude=procobj:"in('JOB_A','JOB_B')";

（7）導入導出所有的表對象
impdp userName/password directory=J_DATA dumpfile=fileName.DMP include=table;

expdp userName/password directory=J_DATA dumpfile=fileName.DMP
logfile=logfileName.LOG include=table;

（8）導入導出時排除所有表對象
impdp userName/password directory=J_DATA dumpfile=fileName.DMP exclude=table;

expdp userName/password directory=J_DATA dumpfile=fileName.DMP
logfile=logfileName.LOG exclude=table;

其他對象類型導入導出同理
?
————————————————
原文鏈接：https://blog.csdn.net/cc_0101/article/details/83010028

?