分享一篇数据泵参数详细说明数据泵expdp/impdp导入导出详细说明 - 平复心态 - 博客园 (cnblogs.com)1 数据库目录准备创建逻辑目录以DBA角色创建目录create directory DUMPDIR as /dumpdir;授权目录给指定用户授予目录操作权限导入/导出操作时使用的用户grant read,write on directory dpdata to user;2 数据导出导入导出数据expdp system/********192.168.102.95:1521/orclpdb parfileexp.par命令执行目录下文件exp.par内容directorydumpdirdumpfileall%U.dmplogfileexpall.logparallel4schemasschema1,schema2,schema3.......导入数据导入对象时当对象已存在时操作会跳过所以需要删除对应的用户或删除原有对象。删除原有对象执行以下sql得到的结果SELECT drop ||a.object_type|| ||a.owner||.||a.object_name||; FROM all_objects a WHERE a.owner IN(MEDAI,EMRCP,HLYTEST,PIVAS,SURGERY,ORDADM,OUTPADM,PHARMACY,OUTPBILL,LAB,MEDREC,MRHP,INPADM,INPBILL,BLDBANK,EWELL,COMM,EIT,EXAM,MEDDOC,MEDQC,PHARM,HIS,UNICORN,EL,CM,MSG,TREATADM,NIS);导入参数中如果使用了parallel并行处理参数可能会导致数据导入数据丢失建议导入时谨慎使用parallel参数。impdp system/********10.100.11.48:1521/orclpdb parfileimp.par命令执行目录下文件imp.par内容directorydumpdirdumpfileall%U.dmptable_exists_actionreplacelogfileimpall.logparallel4schemasschema1,schema2,schema3.......操作范例【导出】expdp system/********192.168.102.95:1521/orclpdb parfileexp.par命令执行目录下文件exp.par内容directorydumpdirdumpfileall%U.dmplogfileexpall.logparallel4schemasMEDAI,EMRCP,HLYTEST,PIVAS,SURGERY,ORDADM,OUTPADM,PHARMACY,OUTPBILL,LAB,MEDREC,MRHP,INPADM,INPBILL,BLDBANK,EWELL,COMM,EIT,EXAM,MEDDOC【导入】impdp system/********10.100.11.48:1521/orclpdb parfileimp.par命令执行目录下文件imp.par内容directorydumpdirdumpfileall%U.dmplogfileimpall.logparallel4schemasMEDAI,EMRCP,HLYTEST,PIVAS,SURGERY,ORDADM,OUTPADM,PHARMACY,OUTPBILL,LAB,MEDREC,MRHP,INPADM,INPBILL,BLDBANK,EWELL,COMM,EIT,EXAM,MEDDOC当数据导入的数据出现丢失时可尝试单线程导出单个文件后导入。导出指定对象可使用EXCLUDEINCLUDE关键字指定。EXCLUDESEQUENCE,VIEW --过滤所有的SEQUENCE,VIEWEXCLUDETABLE:IN (EMP,DEPT) --过滤表对象EMP,DEPTEXCLUDESEQUENCE,VIEW,TABLE:IN (EMP,DEPT) --过滤所有的SEQUENCE,VIEW以及表对象EMP,DEPTEXCLUDEINDEX: INDX_NAME --过滤指定的索引对象INDX_NAMEINCLUDEPROCEDURE:LIKE PRC_A% --包含以PRC_A开头的所有存储过程(_ 符号代表任意单个字符)INCLUDETABLE: A --包含大于字符A的所有表对象其它常用操作符 NOT IN, NOT LIKE, , ! 等等Windows平台:expdp system/manager DIRECTORYmy_dir DUMPFILEexp_tab.dmp LOGFILEexp_tab.log SCHEMASscottINCLUDETABLE:\IN (EMP, DEPT)\在Windows平台下需要对象双引号进行转义使用转义符\Unix平台:在未使用parfile文件的情形下所有的符号都需要进行转义包括括号双引号单引号等expdp system/manager DIRECTORYmy_dir DUMPFILEexp_tab.dmp LOGFILEexp_tab.log SCHEMASscottINCLUDETABLE:\IN \EMP\,\DEP\\expdp 用户名/密码数据库实例 \ TABLES表1,表2,表3 \ DIRECTORY你的目录对象 \ DUMPFILEfull_table_export.dmp \ LOGFILEfull_table_export.log下面是一个实际实际导出过程中的parfile参数,exclude参数用于排出所有包含BAK的表对象,以及一些索引对象,使用了in,like运算符directoryDB_DUMP_DIRdumpfilefull_%U.dmplogfilefull.logexcludetable:like %BAK%,index: in (index1,index2,index3,index4,index5) ,table:in (A,B)schemasxxx导入导出指定表TABLE_A和TABLE_Bimpdp userName/password directoryDB_DUMP_DIR dumpfilefileName.DMP includetable:\in(TABLE_A,TABLE_B)\;expdp userName/password directoryDB_DUMP_DIR dumpfilefileName.DMPlogfilelogfileName.LOG includetable:\in(TABLE_A,TABLE_B)\;导入导出数据时排除TABLE_A和TABLE_Bimpdp userName/password directoryDB_DUMP_DIR dumpfilefileName.DMPexcludetable:\in(TABLE_A,TABLE_B)\;expdp userName/password directory DB_DUMP_DIR dumpfilefileName.DMPlogfilelogfileName.LOG excludetable:\in(TABLE_A,TABLE_B)\;导入导出指定存储过程PROCEDURE_A和PROCEDURE_Bimpdp userName/password directoryDB_DUMP_DIR dumpfilefileName.DMPincludeprocedure:\in(PROCEDURE_A,PROCEDURE_B)\;expdp userName/password directoryDB_DUMP_DIR dumpfilefileName.DMPlogfilelogfileName.LOG includeprocedure:\in(PROCEDURE_A,PROCEDURE_B)\;导入导出数据时排除PROCEDURE_A和PROCEDURE_Bimpdp userName/password directoryDB_DUMP_DIR dumpfilefileName.DMPexcludeprocedure:\in(PROCEDURE_A,PROCEDURE_B)\;expdp userName/password directoryDB_DUMP_DIR dumpfilefileName.DMPlogfilelogfileName.LOG excludeprocedure:\in(PROCEDURE_A,PROCEDURE_B)\;导入导出指定定时任务JOB_A和JOB_Bimpdp userName/password directoryDB_DUMP_DIR dumpfilefileName.DMP includeprocobj:\in(JOB_A,JOB_A)\;expdp userName/password directoryDB_DUMP_DIR dumpfilefileName.DMPlogfilelogfileName.LOG includeprocobj:\in(JOB_A,JOB_B)\;导入导出时排除JOB_A和JOB_Bimpdp userName/password directoryDB_DUMP_DIR dumpfilefileName.DMPexcludeprocobj:\in(JOB_A,JOB_B)\;expdp userName/password directoryDB_DUMP_DIR dumpfilefileName.DMPlogfilelogfileName.LOG excludeprocobj:\in(JOB_A,JOB_B)\;导入导出所有的表对象impdp userName/password directoryDB_DUMP_DIR dumpfilefileName.DMP includetable;expdp userName/password directoryDB_DUMP_DIR dumpfilefileName.DMP logfilelogfileName.LOG includetable;导入导出时排除所有表对象impdp userName/password directoryDB_DUMP_DIR dumpfilefileName.DMP excludetable;expdp userName/password directoryDB_DUMP_DIR dumpfilefileName.DMPlogfilelogfileName.LOG excludetable;其他对象类型导入导出同理SQL查询指定对象并生成导出指令select expdp userName/password directoryDB_DUMP_DIR dumpfilefileName.DMP logfilelogfileName.LOG excludeprocobj:\in \||chr(39)||replace(xmlagg(xmlelement(e,a.object_name,;).extract(//text())).getclobval(),;,\,\)||end\||chr(39)||\ AS object_namefrom all_objects awhere a.last_ddl_time date2024-04-09and a.status VALID--and regexp_instr(a.object_type, PACKAGE|FUNCTION|PROCEDURE|TABLE|VIEW)1and a.ownerCMand a.object_typeVIEWgroup by a.object_type;