ORACLE数据库中EXPDP/IMPDP参数INCLUDE/EXCLUDE的使用说明

mahan

1、参数作用

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;

其他对象类型导入导出同理