ORACLE进阶(一) 通过EXPDP IMPDP命令实现导dmp

版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/sunhuaqiang1/article/details/78885523

ORACLE进阶(一) 通过EXPDP IMPDP命令实现导dmp

前言

  关于dmp文件我们用的还是比较多的,dmp文件它是作为oracle导入和导出表使用的文件格式,今天就将dmp文件导出和导入进行学习。

准备工具

FileZilla、SecureCRT
其中,FileZilla主要用于查看生成的dmp文件与日志文件;SecureCRT主要用于执行shell脚本。

Expdp文件导出

  dmp文件导出用的比较多的一般是三种,他们分别是:导出整个数据库实例下的所有数据、导出指定用户的所有表、导出指定表。
  相关参数以及导出示例:
1. DIRECTORY
  指定转储文件和日志文件所在的目录

DIRECTORY=directory_object

  Directory_object用于指定目录对象名称.需要注意,目录对象是使用CREATE DIRECTORY语句建立的对象,而不是OS目录

Expdp scott/tiger DIRECTORY= DMP  DUMPFILE=a.dump
create or replace directory dmp as 'd:/dmp'
expdp zftang/zftang(@zftang) directory=dmp  dumpfile=test.dmp content=metadata_only
  1. CONTENT
      该选项用于指定要导出的内容.默认值为ALL
CONTENT={ALL | DATA_ONLY | METADATA_ONLY}

  当设置CONTENT为ALL时,将导出对象定义及其所有数据.为DATA_ONLY时,只导出对象数据,为METADATA_ONLY时,只导出对象定义。

expdp zftang/zftang@zftang directory=dmp dumpfile=test.dmp content=metadata_only
expdp zftang/zftang@zftang directory=dmp  dumpfile=test.dmp content=data_only
  1. DUMPFILE
      用于指定转储文件的名称,默认名称为expdat.dmp
DUMPFILE=[directory_object:]file_name [,….]

  Directory_object用于指定目录对象名,file_name用于指定转储文件名.需要注意,如果不指定directory_object,导出工具会自动使用DIRECTORY选项指定的目录对象。

expdp zftang/zftang@zftang directory=dmp  dumpfile=test1.dmp

数据泵工具导出的步骤:
1、创建DIRECTORY

create directory dir_dp as 'D:/oracle/dir_dp'; 

2、授权

Grant read,write on directory dir_dp to zftang;

–查看目录及权限

SELECT privilege, directory_name, DIRECTORY_PATH FROM user_tab_privs t, all_directories d
 WHERE t.table_name(+) = d.directory_name ORDER BY 2, 1;

3、执行导出

expdp zftang/zftang@fgisdb schemas=zftang directory=dir_dp dumpfile =expdp_test1.dmp logfile=expdp_test1.log;

连接到: Oracle Database 10g Enterprise Edition Release 10.2.0.1
With the Partitioning, OLAP and Data Mining options
启动 “ZFTANG”.”SYS_EXPORT_SCHEMA_01”: zftang/**@fgisdb sch
ory=dir_dp dumpfile =expdp_test1.dmp logfile=expdp_test1.log; */

备注

1、directory=dir_dp必须放在前面,如果将其放置最后,会提示

  • ORA-39002: 操作无效

  • ORA-39070: 无法打开日志文件。

  • ORA-39087: 目录名 DATA_PUMP_DIR; 无效
    2、在导出过程中,DATA DUMP 创建并使用了一个名为SYS_EXPORT_TABLE_01的对象,此对象就是DATA DUMP导出过程中所用的JOB名字,如果在执行这个命令时如果没有指定导出的JOB名字那么就会产生一个默认的JOB名字,如果在导出过程中指定JOB名字就为以指定名字出现。
    如下改成:
expdp zftang/zftang@fgisdb schemas=zftang directory=dir_dp dumpfile =expdp_test1.dmp logfile=expdp_test1.log,job_name=my_job1;

3、导出语句后面不要有分号,否则如上的导出语句中的job表名为‘my_job1;’,而不是my_job1。因此导致expdp zftang/zftang attach=zftang.my_job1执行该命令时一直提示找不到job表。

数据泵导出的各种模式

1、 按表模式导出:

expdp zftang/zftang@fgisdb 
tables=zftang.b$i_exch_info,zftang.b$i_manhole_info dumpfile 
=expdp_test2.dmp logfile=expdp_test2.log directory=dir_dp 
job_name=my_job

2、按查询条件导出

expdp zftang/zftang@fgisdb  tables=zftang.b$i_exch_info dumpfile =expdp_test3.dmp logfile=expdp_test3.log directory=dir_dp job_name=my_job query='"where rownum<11"'

3、按表空间导出

Expdp zftang/zftang@fgisdb dumpfile=expdp_tablespace.dmp tablespaces=GCOMM.DBF logfile=expdp_tablespace.log directory=dir_dp job_name=my_job

4、导出方案

Expdp zftang/zftang DIRECTORY=dir_dp DUMPFILE=schema.dmp SCHEMAS=zftang,gwm

5、导出整个数据库

expdp zftang/zftang@fgisdb dumpfile =full.dmp full=y logfile=full.log directory=dir_dp job_name=my_job

Impdp文件导入

1、按表导入

p_street_area.dmp文件中的表,此文件是以gwm用户按schemas=gwm导出的:

impdp gwm/gwm@fgisdb  dumpfile =p_street_area.dmp 
logfile=imp_p_street_area.log directory=dir_dp tables=p_street_area 
job_name=my_job

2、按用户导入(可以将用户信息直接导入,即如果用户信息不存在的情况下也可以直接导入)

impdp gwm/gwm@fgisdb schemas=gwm dumpfile =expdp_test.dmp logfile=expdp_test.log directory=dir_dp job_name=my_job

3、不通过expdp的步骤生成dmp文件而直接导入的方法

--从源数据库中向目标数据库导入表p_street_area
impdp gwm/gwm directory=dir_dp NETWORK_LINK=igisdb tables=p_street_area logfile=p_street_area.log  job_name=my_job
igisdb是目的数据库与源数据的链接名,dir_dp是目的数据库上的目录

4、更换表空间

采用remap_tablespace参数

–导出gwm用户下的所有数据
expdp system/orcl directory=data_pump_dir dumpfile=gwm.dmp SCHEMAS=gwm

注:如果是用sys用户导出的用户数据,包括用户创建、授权部分,用自身用户导出则不含这些内容
–以下是将gwm用户下的数据全部导入到表空间gcomm(原来为gmapdata表空间下)下

impdp system/orcl directory=data_pump_dir dumpfile=gwm.dmp remap_tablespace=gmapdata:gcom

注:EXP、IMP与EXPDP、IMPDP的区别

  EXP和IMP是客户端工具程序,它们既可以在客户端使用,也可以在服务端使用。
  EXPDP和IMPDP是服务端的工具程序,他们只能在ORACLE服务端使用,不能在客户端使用。
  IMP只适用于EXP导出的文件,不适用于EXPDP导出文件;IMPDP只适用于EXPDP导出的文件,而不适用于EXP导出文件。
  expdp或impdp命令时,可暂不指出用户名/密码@实例名 as 身份,然后根据提示再输入,如:

expdp schemas=scott dumpfile=expdp.dmp DIRECTORY=dpdata1;

导出导入可能遇到的坎

导入过程中碰到的两个问题:

(1) 中文字符集转换

  • KUP-11007: conversion error loading table “TEST”.”T_PSR” ORA-12899: 列REASON_CODE 的值太大 (实际值: 21, 最大值: 20)
  • KUP-11009: data for row: REASON_CODE :
    0X’BABDBFD5C6F7C8DDC1BFCFDED6C6’
      这里涉及到了字符集转换的问题,中文在GBK字符集中占2位,但在UTF-8字符集中占3位,所以在GBK中保存小于20个字符的情况下,导入到了UTF-8的库中,就可能因为需要额外的字符空间导致超出字段长度定义,报了ORA-12899的错误。

(2) 主外键关联

  • ORA-31693: Table data object “TEST”.”T_ITE” failed to load/unload and is being skipped due to error:
  • ORA-29913: error in executing ODCIEXTTABLEFETCH callout
  • ORA-02291: integrity constraint (TEST.FK_ITE_REF_PSR) violated
    parent key not found

  由于有些表之间是存在主外键关联的,expdp导出的时候选择了data_only仅导出数据,impdp导入的时候会因未插入主键记录而插入外键记录,出现ORA-02291的错误,对于这种情况可以选择先禁止主外键关联,导入后再恢复关联。
操作顺序:
(a) 导入前,执行如下SQL找到需要禁止的外键关联
select ‘ALTER TABLE ‘||TABLE_NAME||’ DISABLE CONSTRAINT ‘||constraint_name||’;’from user_constraints WHERE
CONSTRAINT_TYPE=’R’;
(b) 执行(a)的结果SQL
(c) 导入后,执行如下SQL找到需要恢复的外键关联
select ‘ALTER TABLE ‘||TABLE_NAME||’ ENABLE NOVALIDATE CONSTRAINT ‘||constraint_name||’;’ from user_constraints WHERE
CONSTRAINT_TYPE=’R’;
  NOVALIDATE参数不会验证已存储的数据,但未来再插入的记录则会遵循主外键关联的关系。

总结

  • 使用10g以上版本提供的expdp/impdp数据泵导入导出工具,较以往的exp/imp工具,无论是在参数的可选择性上,还是速度和压缩比上,都有了不小的改进,提供更为方便快速的数据导入导出方法给我们。
  • 导入导出可能碰到最多的问题,字符集转换算是其中之一,要明确导入导出数据对字符集的依赖程度,才能确保数据导入导出的正确。
  • 对于有主外键关联的数据,如果选择data_only仅导出数据,那么可在导入前禁止约束,这样导入过程不会受到主外键关联的影响,导入后可以恢复约束,保证约束的正确。

删除外键约束

ALTER TABLE test_sub DROP CONSTRAINT main_id_cons;

1、ORACLE数据库中的外键约束名都在表user_constraints中可以查到。其中constraint_type=’R’表示是外键约束。
2、启用外键约束的命令为:alter table table_name enable constraint constraint_name
3、禁用外键约束的命令为:alter table table_name disable constraint constraint_name
4、然后再用SQL查出数据库中所以外键的约束名:
select ‘alter table ‘||table_name||’ enable constraint ‘||constraint_name||’;’ from user_constraints where constraint_type=’R’

实例讲解

一、ipspkgextstus 表定期清理

ipspkgextstus表清理超过半年的数据,清理方式为①替换表名ipspkgextstus为ipspkgextstusbak②truncate表ipspkgextstus③将ipspkgextstusbak表中部分数据复原至ipspkgextstus(建议半年),复原范围为select a.* from ipspkgextstus a where exists(select 1 from onlnsrvflwtab o where a.flwid=o.flwid and o.crttm >’2017-01-01 00:00:00’),红字部分按实际情况替换④ipspkgextstusbak表保留但不备份。
实现步骤:

  • 1.expdp ipspkgextstus.dmp

  • 2.expdp ipspkgextstus_next_half_year.dmp

  • 3.impdp ipspkgextstus table_exists_action=truncate
!/usr/bin/ksh

程序名称: 重建IPSPKGEXTSTUS,通过导出导入DMP形式

expdp clms01/clms01 DUMPFILE=IPSPKGEXTSTUS.dmp directory=ORABAK  TABLES=IPSPKGEXTSTUSLOGFILE=IPSPKGEXTSTUS-exp.LOG 
CONTENT=DATA_ONLY  EXCLUDE=STATISTICS  
JOB_NAME=MY_JOB_IPSPKGEXTSTUS

SecureCRT操作界面如下:
这里写图片描述
  根据expdp语句中设置的directory参数,查询相应数据表,即可查看到相应的服务器地址。
SELECT * FROM DBA_DIRECTORIES d where d.directory_name=’ORABAK’
这里写图片描述
  根据服务器地址,在FileZilla工具中即可查看到相应位置的dmp与log文件。
FileZilla操作界面如下:
这里写图片描述
  在此可查看到生成的dmp与log文件。
  生成的log日志文件如下图所示:
这里写图片描述
  log日志文件主要记录了当前数据库的版本信息,执行的dmp语句及执行相关情况。
  通过查看log日志,可知该导出dmp文件无错误提示。
  另外,可通过查询数据记录条数进行大致校验。从日志记录中可以看到涉及的数据记录数为97760条。统计对应的数据表记录数结果如下:
这里写图片描述
  错误处理:
UDE-00014:invalid value for parameter,’query’.
expdp clms01/clms01

DUMPFILE=IPSPKGEXTSTUS_NEXT_HALF_YEAR.dmp directory=ORABAK TABLES=IPSPKGEXTSTUS LOGFILE=IPSPKGEXTSTUS_EXP_NEXT_HALF_YEAR.LOG
CONTENT=DATA_ONLY EXCLUDE=STATISTICS QUERY=\’WHERE exists (select 1 from
onlnsrvflwtab where ipspkgextstus.FLWID=onlnsrvflwtab.FLWID and onlnsrvflwtab.crttm >\”2017-06-30 00:00:00\”)\’
这里写图片描述
expdp clms01/clms01 DUMPFILE=IPSPKGEXTSTUS_NEXT_HALF_YEAR.dmp directory=ORABAK TABLES=IPSPKGEXTSTUS LOGFILE=IPSPKGEXTSTUS_EXP_NEXT_HALF_YEAR.LOG
CONTENT=DATA_ONLY EXCLUDE=STATISTICS QUERY=IPSPKGEXTSTUS:’WHERE exists (select 1
from onlnsrvflwtab where ipspkgextstus.FLWID\=onlnsrvflwtab.FLWID and onlnsrvflwtab.crttm >\”2017-06-30 00:00:00\”)’
继续出现问题:
这里写图片描述
Query条件写得有问题。特殊字符均需转译!
expdp clms01/clms01 DUMPFILE=IPSPKGEXTSTUS_NEXT_HALF_YEAR.dmp directory=ORABAK TABLES=IPSPKGEXTSTUS LOGFILE=IPSPKGEXTSTUS_EXP_NEXT_HALF_YEAR.LOG
CONTENT=DATA_ONLY EXCLUDE=STATISTICS QUERY=IPSPKGEXTSTUS:\”WHERE exists (select 1 from onlnsrvflwtab where ipspkgextstus.FLWID\=onlnsrvflwtab.FLWID and onlnsrvflwtab.crttm >\’2017-06-30 00:00:00\’)\”
这里写图片描述
执行之后,还是有问题。继续努力!
ORA-31693、ORA-00904
  继续尝试修改
expdp clms01/clms01 DUMPFILE=IPSPKGEXTSTUS_NEXT_HALF_YEAR.dmp
directory=ORABAK TABLES=IPSPKGEXTSTUS
LOGFILE=IPSPKGEXTSTUS_EXP_NEXT_HALF_YEAR.LOG CONTENT=DATA_ONLY
EXCLUDE=STATISTICS QUERY=IPSPKGEXTSTUS:\”WHERE exists (select 1 from onlnsrvflwtab
where flwid=onlnsrvflwtab.flwid and onlnsrvflwtab.crttm >\’2017-06-30 00:00:00\’)\”
这里写图片描述
select count(*) from ipspkgextstus a where exists(select 1 from onlnsrvflwtab o where a.flwid=o.flwid and o.crttm >’2017-06-30 00:00:00’)
这里写图片描述
  通过与相应的sql语句统计结果相比,可知以上expdp语句并不对。Query参数条件并未执行。继续尝试修改!
  取消使用exists,取而代之使用in。
expdp clms01/clms01 DUMPFILE=IPSPKGEXTSTUS_NEXT_HALF_YEAR.dmp
directory=ORABAK TABLES=IPSPKGEXTSTUS
LOGFILE=IPSPKGEXTSTUS_EXP_NEXT_HALF_YEAR.LOG CONTENT=DATA_ONLY
EXCLUDE=STATISTICS QUERY=IPSPKGEXTSTUS:\”WHERE flwid IN (select flwid from
onlnsrvflwtab where crttm >\’2017-06-30 00:00:00\’)\”
这里写图片描述
  Binggo正常了,继续进行导入dmp文件操作。
impdp clms01/clms01 directory=ORABAK dumpfile=IPSPKGEXTSTUS_NEXT_HALF_YEAR.dmp
LOGFILE=IPSPKGEXTSTUS_IMP_NEXT_HALF_YEAR.LOG CONTENT=DATA_ONLY
tables=IPSPKGEXTSTUS table_exists_action=truncate
这里写图片描述
  重新查表核对记录数。
这里写图片描述
  结果一致,实现操作。

二、cmbonlnsrvparm 表清理

cmbonlnsrvparm表清理超过半年的数据,清理方式为①删除onlnsrvndparmreltab表外键②替换表名cmbonlnsrvparm为cmbonlnsrvparmbak③truncate表cmbonlnsrvparm④将cmbonlnsrvparmbak表中的部分数据复原至cmbonlnsrvparm(建议一年),复原范围为select a.* from cmbonlnsrvparm a where exists(select 1 from onlnsrvflwtab o,onlnsrvndparmreltab r where a.parmid=r.parmid and o.flwid = r.flwid and o.crttm >’2017-01-01 00:00:00’),红字部分按实际情况替换⑤cmbonlnsrvparmbak表保留但不备份。
查看onlnsrvndparmreltab表外键sql语句:
select u.* from user_constraints u where u.table_name=’ONLNSRVNDPARMRELTAB’;
这里写图片描述
  由于constraint_type=’R’表示是外键约束,可以看到该表外键已被删除。
实现步骤:
1.select u.* from user_constraints u where
u.table_name=’ONLNSRVNDPARMRELTAB’;
2.expdp cmbonlnsrvparm.dmp
3.expdp cmbonlnsrvparm _one_year.dmp
4.impdp cmbonlnsrvparm table_exists_action=truncate
这里写图片描述
  执行后的校验方法是查看处理日志、对比处理的数据条数。

后期制作shell脚本

IPSPKGEXTSTUS_EXP.sh
!/usr/bin/ksh

程序名称: 重建IPSPKGEXTSTUS,通过导出DMP形式

导出IPSPKGEXTSTUS全量
expdp clms01/clms01 DUMPFILE=IPSPKGEXTSTUS_FULL.dmp directory=ORABAK
TABLES=IPSPKGEXTSTUS LOGFILE=IPSPKGEXTSTUS_FULL.LOG CONTENT=DATA_ONLY
EXCLUDE=STATISTICS

导出IPSPKGEXTSTUS半年
expdp clms01/clms01 DUMPFILE=IPSPKGEXTSTUS_NEXT_HALF_YEAR.dmp directory=ORABAK
TABLES=IPSPKGEXTSTUS LOGFILE=IPSPKGEXTSTUS_EXP_NEXT_HALF_YEAR.LOG
CONTENT=DATA_ONLY EXCLUDE=STATISTICS QUERY=IPSPKGEXTSTUS:\”WHERE flwid IN (select
flwid from clms01.onlnsrvflwtab where crttm >\’2017-06-30 00:00:00\’)\”

IPSPKGEXTSTUS_IMP.sh
!/usr/bin/ksh

程序名称: 重建IPSPKGEXTSTUS,通过导入DMP形式

导入IPSPKGEXTSTUS半年

impdp clms01/clms01 directory=ORABAK dumpfile=IPSPKGEXTSTUS_NEXT_HALF_YEAR.dmp 
LOGFILE=IPSPKGEXTSTUS_IMP_NEXT_HALF_YEAR.LOG CONTENT=DATA_ONLY 
tables=IPSPKGEXTSTUS table_exists_action=truncate

注:

1.导出scott用户下的emp,dept两张表

expdp scott/tiger@orcl tables=emp,dept dumpfile=expdp.dmp directory=dump_dir;

2.从scott用户中把表dept和emp导入到system用户中

impdp system/manager@orcl directory=dump_dir dumpfile=expdp.dmp tables=scott.dept,scott.emp remap_schema=scott:system;

参考资料

ORACLE官网

这里写图片描述

这里写图片描述

展开阅读全文

没有更多推荐了,返回首页