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