oracle 導(dǎo)出導(dǎo)入表
2021-10-22 16:59:47

? 導(dǎo)出多個表

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

導(dǎo)入多個表

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

?

不導(dǎo)出某些表

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中執(zhí)行 是轉(zhuǎn)義字符

導(dǎo)入剛才的

?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：用于排除導(dǎo)入/導(dǎo)出某些對象

include：用于指定導(dǎo)入/導(dǎo)出某些對象

?

2、參數(shù)語法

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系統(tǒng)中需要在雙引號“ ”前加入轉(zhuǎn)義字符：\'' ?\''

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

?

3、參數(shù)使用舉例

（1）導(dǎo)入導(dǎo)出指定表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）導(dǎo)入導(dǎo)出數(shù)據(jù)時排除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）導(dǎo)入導(dǎo)出指定存儲過程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）導(dǎo)入導(dǎo)出數(shù)據(jù)時排除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）導(dǎo)入導(dǎo)出指定定時任務(wù)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）導(dǎo)入導(dǎo)出時排除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）導(dǎo)入導(dǎo)出所有的表對象
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）導(dǎo)入導(dǎo)出時排除所有表對象
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;

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

?