Oracle数据泵导出导入

yjb.gz

已于 2024-07-06 13:30:00 修改

阅读量310

点赞数

分类专栏： oracle 文章标签： oracle 数据库

于 2023-09-23 10:52:39 首次发布

本文链接：https://blog.csdn.net/JangBingYang/article/details/133199428

版权

oracle 专栏收录该内容

11 篇文章 0 订阅

订阅专栏

分享一篇数据泵参数详细说明：数据泵expdp/impdp导入导出详细说明 - 平复心态 - 博客园 (cnblogs.com)

1 数据库目录准备

以DBA角色创建目录

create directory DUMPDIRas '/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.dmp

table_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\'\"

下面是一个实际实际导出过程中的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;