Oracle数据泵导出导入
分享一篇数据泵参数详细说明：数据泵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 parfile=exp.par
命令执行目录下文件exp.par内容：
directory=dumpdir
dumpfile=all%U.dmp
logfile=expall.log
parallel=4
schemas='schema1','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','MEDQ
C','PHARM','HIS','UNICORN','EL','CM','MSG','TREATADM','NIS');
导入参数中如果使用了parallel并行处理参数，可能会导致数据导入数据丢失，建议导入时谨慎使用parallel参数。
impdp system/********@10.100.11.48:1521/orclpdb parfile=imp.par
命令执行目录下文件imp.par内容：
directory=dumpdir
dumpfile=all%U.dmptable_exists_action=replace
logfile=impall.log
parallel=4
schemas='schema1','schema2','schema3'.......
- 操作范例
【导出】
expdp system/********@192.168.102.95:1521/orclpdb parfile=exp.par命令执行目录下文件exp.par内容：
directory=dumpdir
dumpfile=all%U.dmp
logfile=expall.log
parallel=4
schemas='MEDAI','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 parfile=imp.par命令执行目录下文件imp.par内容：
directory=dumpdir
dumpfile=all%U.dmp
logfile=impall.log
parallel=4
schemas='MEDAI','EMRCP','HLYTEST','PIVAS','SURGERY','ORDADM','OUTPADM','PHARMACY','OUTPBILL','LAB','MEDREC','MRHP','INPADM','INPBILL','BLDBANK','EWELL','COMM','EIT','EXAM','MEDDOC'
当数据导入的数据出现丢失时，可尝试单线程导出单个文件后导入。
- 导出指定对象
可使用EXCLUDE，INCLUDE关键字指定。
EXCLUDE=SEQUENCE,VIEW --过滤所有的SEQUENCE,VIEW
EXCLUDE=TABLE:"IN ('EMP','DEPT')" --过滤表对象EMP,DEPT
EXCLUDE=SEQUENCE,VIEW,TABLE:"IN ('EMP','DEPT')" --过滤所有的SEQUENCE,VIEW以及表对象EMP,DEPT
EXCLUDE=INDEX:"= 'INDX_NAME'" --过滤指定的索引对象INDX_NAME
INCLUDE=PROCEDURE:"LIKE 'PRC_A%'" --包含以PRC_A开头的所有存储过程(_ 符号代表任意单个字符)
INCLUDE=TABLE:"> 'A' " --包含大于字符A的所有表对象
其它常用操作符 NOT IN, NOT LIKE, <, != 等等
Windows平台:
expdp system/manager DIRECTORY=my_dir DUMPFILE=exp_tab.dmp LOGFILE=exp_tab.log SCHEMAS=scott
INCLUDE=TABLE:\"IN ('EMP', 'DEPT')\"
在Windows平台下，需要对象双引号进行转义，使用转义符\
Unix平台:
在未使用parfile文件的情形下，所有的符号都需要进行转义，包括括号，双引号，单引号等
expdp system/manager DIRECTORY=my_dir DUMPFILE=exp_tab.dmp LOGFILE=exp_tab.log SCHEMAS=scott
INCLUDE=TABLE:\"IN \'EMP\',\'DEP\'\"
expdp 用户名/密码@数据库实例 \ TABLES=表1,表2,表3 \ DIRECTORY=你的目录对象 \ DUMPFILE=full_table_export.dmp \ LOGFILE=full_table_export.log
下面是一个实际实际导出过程中的parfile参数,exclude参数用于排出所有包含BAK的表对象,以及一些索引对象,使用了in,like运算符
directory=DB_DUMP_DIR
dumpfile=full_%U.dmp
logfile=full.log
exclude=table:"like '%BAK%'",index:" in ('index1','index2','index3','index4','index5') ",table:"in ('A','B')"
schemas=xxx
导入导出指定表TABLE_A和TABLE_B
impdp userName/password directory=DB_DUMP_DIR dumpfile=fileName.DMP include=table:\"in('TABLE_A','TABLE_B')\";
expdp userName/password directory=DB_DUMP_DIR dumpfile=fileName.DMP
logfile=logfileName.LOG include=table:\"in('TABLE_A','TABLE_B')\";
导入导出数据时排除TABLE_A和TABLE_B
impdp userName/password directory=DB_DUMP_DIR dumpfile=fileName.DMP
exclude=table:\"in('TABLE_A','TABLE_B')\";
expdp userName/password directory= DB_DUMP_DIR dumpfile=fileName.DMP
logfile=logfileName.LOG exclude=table:\"in('TABLE_A','TABLE_B')\";
导入导出指定存储过程PROCEDURE_A和PROCEDURE_B
impdp userName/password directory=DB_DUMP_DIR dumpfile=fileName.DMP
include=procedure:\"in('PROCEDURE_A','PROCEDURE_B')\";
expdp userName/password directory=DB_DUMP_DIR dumpfile=fileName.DMP
logfile=logfileName.LOG include=procedure:\"in('PROCEDURE_A','PROCEDURE_B')\";
导入导出数据时排除PROCEDURE_A和PROCEDURE_B
impdp userName/password directory=DB_DUMP_DIR dumpfile=fileName.DMP
exclude=procedure:\"in('PROCEDURE_A','PROCEDURE_B')\";
expdp userName/password directory=DB_DUMP_DIR dumpfile=fileName.DMP
logfile=logfileName.LOG exclude=procedure:\"in('PROCEDURE_A','PROCEDURE_B')\";
导入导出指定定时任务JOB_A和JOB_B
impdp userName/password directory=DB_DUMP_DIR dumpfile=fileName.DMP include=procobj:\"in('JOB_A','JOB_A')\";
expdp userName/password directory=DB_DUMP_DIR dumpfile=fileName.DMP
logfile=logfileName.LOG include=procobj:\"in('JOB_A','JOB_B')\";
导入导出时排除JOB_A和JOB_B
impdp userName/password directory=DB_DUMP_DIR dumpfile=fileName.DMP
exclude=procobj:\"in('JOB_A','JOB_B')\";
expdp userName/password directory=DB_DUMP_DIR dumpfile=fileName.DMP
logfile=logfileName.LOG exclude=procobj:\"in('JOB_A','JOB_B')\";
导入导出所有的表对象
impdp userName/password directory=DB_DUMP_DIR dumpfile=fileName.DMP include=table;
expdp userName/password directory=DB_DUMP_DIR dumpfile=fileName.DMP logfile=logfileName.LOG include=table;
导入导出时排除所有表对象
impdp userName/password directory=DB_DUMP_DIR dumpfile=fileName.DMP exclude=table;
expdp userName/password directory=DB_DUMP_DIR dumpfile=fileName.DMP
logfile=logfileName.LOG exclude=table;
其他对象类型导入导出同理
SQL查询指定对象，并生成导出指令
select 'expdp userName/password directory=DB_DUMP_DIR dumpfile=fileName.DMP logfile=logfileName.LOG exclude=procobj:\"in \'||chr(39)||replace(xmlagg(xmlelement(e,a.object_name,';').extract('//text()')).getclobval(),';','\'',\''')||'end\'||chr(39)||'\"' AS object_name
from all_objects a
where a.last_ddl_time >= date
'2024-04-09'
and a.status = 'VALID'
--and regexp_instr(a.object_type, 'PACKAGE|FUNCTION|PROCEDURE|TABLE|VIEW')>=1
and a.owner='CM'
and a.object_type='VIEW'
group by a.object_type;