oracle 導出導入表

  導出多個表

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

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')\";

logfile=logfileName.LOG exclude=table:\"in('TABLE_A','TABLE_B')\";

（3）導入導出指定存儲過程PROCEDURE_A和PROCEDURE_B

include=procedure:\"in('PROCEDURE_A','PROCEDURE_B')\";

logfile=logfileName.LOG include=procedure:\"in('PROCEDURE_A','PROCEDURE_B')\";

（4）導入導出資料時排除PROCEDURE_A和PROCEDURE_B

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')\";

logfile=logfileName.LOG include=procobj:\"in('JOB_A','JOB_B')\";

（6）導入導出時排除JOB_A和JOB_B

exclude=procobj:\"in('JOB_A','JOB_B')\";

logfile=logfileName.LOG exclude=procobj:\"in('JOB_A','JOB_B')\";

（7）導入導出所有的表對象

impdp userName/password directory=J_DATA dumpfile=fileName.DMP include=table;

logfile=logfileName.LOG include=table;

（8）導入導出時排除所有表對象

impdp userName/password directory=J_DATA dumpfile=fileName.DMP exclude=table;

logfile=logfileName.LOG exclude=table;

其他對象類型導入導出同理

————————————————