Expdp/Impdp逻辑工具使用说明

作者: 番茄人 | 来源:发表于2019-07-30 14:30 被阅读1次

Expdp/Impdp逻辑工具使用说明
expdp/impdp命令解析
oracle12c windows下面执行impdp 报：未找
expdp & impdp
Oracle expdp/impdp
ORACLE expdp impdp
Oracle数据泵常用命令
ORACLE数据泵
2018-12-22
impdp和expdp使用总结

本文记录了自己日常导数据常用的参数和遇到的问题的解决办法,
逻辑备份需要不能作为热备，但在日常工作中测试，或数据迁移中扮演着一定的角色。
Expdp/Impdp与传统exp/imp的比较：

expdp是服务端程序，需要在数据库服务器上执行，exp是客户端程序，exp需要关注网络传输
expdp读取数据块，exp是转换成SQL
expdp可以加并行
expdp是直接路径读，exp是要通过SGA

expdp

查看expdp的帮助信息，有些选项名称不记得的时候，-help就可以很方便查看，跟Linux帮助信息命令同理。

[oracle@testdb ~]$ expdp -help

The Data Pump export utility provides a mechanism for transferring data objects
between Oracle databases. The utility is invoked with the following command:

   Example: expdp scott/tiger DIRECTORY=dmpdir DUMPFILE=scott.dmp

You can control how Export runs by entering the 'expdp' command followed
by various parameters. To specify parameters, you use keywords:

   Format:  expdp KEYWORD=value or KEYWORD=(value1,value2,...,valueN)
   Example: expdp scott/tiger DUMPFILE=scott.dmp DIRECTORY=dmpdir SCHEMAS=scott
               or TABLES=(T1:P1,T1:P2), if T1 is partitioned table

USERID must be the first parameter on the command line.

------------------------------------------------------------------------------

The available keywords and their descriptions follow. Default values are listed within square brackets.

ATTACH
Attach to an existing job.
For example, ATTACH=job_name.

CLUSTER
Utilize cluster resources and distribute workers across the Oracle RAC.
Valid keyword values are: [Y] and N.

COMPRESSION
Reduce the size of a dump file.
Valid keyword values are: ALL, DATA_ONLY, [METADATA_ONLY] and NONE.
...  剩余的略过

expdp的路径:

查看当前数据库存在的directory路径:

SQL> select * from dba_directories;
OWNER                          DIRECTORY_NAME                           DIRECTORY_PATH
------------------------------ ---------------------------------------- --------------------------------------------------
SYS                            ORACLE_OCM_CONFIG_DIR                    /u01/app/oracle/product/11.2.0/dbhome_1/ccr/state
SYS                            DATA_PUMP_DIR                            /u01/app/oracle/admin/orcldb/dpdump/
SYS                            XMLDIR                                   /ade/b/2125410156/oracle/rdbms/xml

创建自定义的导出导入路径：

[oracle@testdb ~]$ mkdir /oradata/datapump

SQL> create directory datapump as '/oradata/datapump';

Directory created.

SQL> col OWNER for a30;
SQL> col DIRECTORY_NAME for a40;
SQL> col DIRECTORY_PATH for a50;
SQL> select * from dba_directories;

OWNER                          DIRECTORY_NAME                           DIRECTORY_PATH
------------------------------ ---------------------------------------- --------------------------------------------------
SYS                            ORACLE_OCM_CONFIG_DIR                    /u01/app/oracle/product/11.2.0/dbhome_1/ccr/state
SYS                            DATA_PUMP_DIR                            /u01/app/oracle/admin/orcldb/dpdump/
SYS                            DATAPUMP                                 /oradata/datapump
SYS                            XMLDIR                                   /ade/b/2125410156/oracle/rdbms/xml

给用户授权：

SQL> grant read,write on directory datapump to User1;
Grant succeeded.

# 如果有resource，connect的权限，用户可以导出自身的数据：
[oracle@testdb datapump]$cat >expdp_user1.par
userid="user1/user1"
directory=DATAPUMP
dumpfile=expdp_user1.dmp
logfile=expdp_user1.log                                                     
CLUSTER=N
schemas=(user1)

expdp parfile=expdp_user1.par
# 导出整个数据库需要权限: exp_full_database
SQL> grant exp_full_database to user1;
Grant succeeded.

按用户导数据

cat >expdp_user1.par <<EOF
userid="/ as sysdba"
directory=datapump
job_name=job_expdp_user1
dumpfile=expdp_user1%u.dmp
logfile=expdp_user1.log                                                     
cluster=n
schemas=(
user1
)
parallel=4
filesize=500m
COMPRESSION=all
EOF

[oracle@testdb datapump]$ nohup expdp parfile=expdp_user1.par &

Starting "SYS"."JOB_EXPDP_USER1":  /******** AS SYSDBA parfile=expdp_user1.par 
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
. . exported "USER1"."TEST"                              5.023 KB       1 rows
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

命令行选项说明[参数里面的内容大小写不敏感]:

# 建议在数据库服务器上后台操作:
[oracle@testdb datapump]$ nohup expdp parfile=expdp_user1.par &

首先，强烈建议使用parfile参数,将需要用到的参数整合到一个par文件中，不止美观，也可以留下记录。
userid            -- 能登上数据库服务器就直接使用sysdba用户
directory       --指定数据泵的目录路径，生成的dmp文件也在该目录下
job_name     --数据泵任务的名称,因为是sys用户，所以名称为"SYS"."JOB_EXPDP_USER1"
dumpfile       --dmp文件的名称,使用并行导出到多个文件，需要用到%u
logfile           --记录整个导出过程的日志文件
cluster          --RAC集群必须指定为N,否则数据库不知道从哪个实例导出，会报错
schema         --指定要导出的用户名
parallel   --并行度，取决服务器的cpu个数,生产繁忙时不要导出大数据量
filesize   --指定每个dmp文件的大小，不指定，dmp文件的大小会不统一。
COMPRESSION=all   --是否要进行压缩，压缩比率为1:7左右，本地磁盘空间不足或需要跨网络传输时，建议压缩，否则不建议，会消耗服务器一些性能，降低了导出导入的效率。

当你进行生产库迁移的时候，大家通常关心的问题是
"当前的进度怎么样，大概要多久可以完成?",
那么这时候就需要用到attach选项，查看当前任务大概的情况：

[oracle@testdb ~]$ expdp attach=JOB_EXPDP_USER1

Export: Release 11.2.0.1.0 - Production on Mon Jul 29 17:26:03 2019

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Username: / as sysdba

 /******** AS SYSDBA parfile=expdp_user1.par 
  State: EXECUTING                      
  Bytes Processed: 5,144
  Percent Done: 99
  Current Parallelism: 4
  Job Error Count: 0
  Dump File: /oradata/datapump/expdp_user1%u.dmp
    size: 524,288,000
  Dump File: /oradata/datapump/expdp_user101.dmp
    size: 524,288,000
    bytes written: 4,096
  Dump File: /oradata/datapump/expdp_user102.dmp
    size: 524,288,000
    bytes written: 12,288
  
Worker 1 Status:
  Process Name: DW00
  State: EXECUTING                      
  Object Schema: USER1
  Object Name: TEST
  Object Type: SCHEMA_EXPORT/TABLE/TABLE
  Completed Objects: 1
  Total Objects: 1
  Worker Parallelism: 1
  
Worker 2 Status:
  Process Name: DW01
  State: WORK WAITING                   

Export> 

export>中，
help     查看帮助信息
status   可以查看相关进程的状态
kill_job  可以kill掉任务进程
stop_job  暂停任务
START_JOB  继续任务

在数据库层面监控任务完成进度:

SELECT sid,
       serial#,
       context,
       sofar,
       totalwork,
       ROUND(sofar / totalwork * 100, 2) "%_COMPLETE"
  FROM v$session_longops
 WHERE opname LIKE '%EXP%'
   AND totalwork != 0
   AND sofar <> totalwork;

数据泵任务的信息：

select  owner_name owr,
           job_name jbn,
           operation ope,
           job_mode jbm,
           state,degree,
           attached_sessions atts,
           datapump_sessions dats                             
 from  dba_datapump_jobs;

按用户表（TABLE）

cat >expdp_user1.par 

userid="/ as sysdba"
directory=datapump
dumpfile=expdp_user1%u.dmp
logfile=expdp_user1.log                                                     
cluster=n
tables=(
user1.t1
user1.t2
user2.t3
user2.t4
)
parallel=4

有时候，因为表数据量太大，业务实际需要一部分数据作为测试，这时候可以按表的部分查询条件进行导出操作：

cat >expdp_user1.par

userid="/ as sysdba"
directory=DATAPUMP
dumpfile=expdp_user1%U.dmp
logfile=expdp_user1.log                                                     
CLUSTER=N
COMPRESSION=all
tables=
(
User1.Table1
)
parallel=8
QUERY=(User1.Table1:" where deal_time>=to_date('2015-11-16 00:00:00','yyyy-mm-dd hh24:mi:ss') and deal_time<to_date('2015-11-17 00:00:00','yyyy-mm-dd hh24:mi:ss') ")

按整库（DATABASE）

full选项 -- Y即为整库

cat >expdp_full.par <<EOF
userid="/ as sysdba"
directory=datapump
job_name=job_expdp_user1
dumpfile=expdp_full%u.dmp
logfile=expdp_full.log                                                     
cluster=n
full=y
parallel=4
filesize=500m
EOF

expdp parfile=expdp_full.par

按表空间（TABLESPACE）

TABLESPACE --指定表空间名

userid="/ as sysdba"
directory=DATAPUMP
JOB_NAME=Job_expdp_tbs
dumpfile=expdp_tbs%u.dmp
logfile=expdp_tbs.log                                                     
CLUSTER=N
tablespaces=users
parallel=4
FILESIZE=500M

其他选项使用

INCLUDE --包括哪些对象
EXCLUDE --排除哪些对象

# 导出用户scott数据，排除表（TABLENAME1,TABLENAME2）
cat >expdp_test.par

userid="/ as sysdba"
directory=DATAPUMP
dumpfile=expdp_test.dmp
logfile=expdp_test.log                                                     
CLUSTER=N
schemas=scott
EXCLUDE=TABLE:"IN ('TABLENAME1','TABLENAME2')"


# 只导用户scott的索引
cat >expdp_scott_index.par

userid="/ as sysdba"
directory=DATAPUMP
dumpfile=expdp_scott_index.dmp
logfile=expdp_scott_index.log                                                     
CLUSTER=N
schemas=scott
INCLUDE=index

impdp

查看源库表空间信息（需要注意11G延迟段特性deferred_segment_creation）:

# 对象所在表空间信息
select  sum(bytes/1024/1024),tablespace_name
  from  dba_segments
where  segment_name in  ('') 
group  by tablespace_name;

# 导出用户或表所在的索引，分区索引等信息：
select sum(bytes/1024/1024),tablespace_name
  from dba_segments
 where segment_name in( select index_name from dba_indexes where table_name in
     ( ''))
group by tablespace_name;

按用户导入

cat >impdp_user1.par 

userid="/ as sysdba"
directory=datapump
dumpfile=expdp_user1%u.dmp
logfile=impdp_user1.log                                                     
cluster=n
remap_schemas=user1:user3
parallel=4
remap_tablespace=tbs1:tbs3
table_exists_action=replace

dumpfile --名称为源库导出的dmp文件名
remap_schemas --将源库的user1用户数据迁移 -- > 目标库的user3用户
remap_tablespace --指定源库tbs1表空间的数据 --> 目标库的表空间tbs3
table_exists_action -- replace替换, 目标导入库的用户存在相同表名，会替换掉(drop），truncate（存在表truncate），append(追加数据)

按表导入

userid="/ as sysdba"
directory=DATAPUMP
dumpfile=expdp_tablename_20160510_%u.dmp
logfile=imppdp_tablename_20160510_.log
remap_schema=(User1:User2)
remap_tablespace=
(
source_tbs1:des_tbs1
source_index1:des_index1
)
tables=(
user1.Tablename1
user1.Tablename2
)
CLUSTER=N
remap_table=(
Tablename1:Tbname2
Tablename2:Tbname2)
table_exists_action=truncate
parallel=4

# 按部分数据导入,tab1只需要导入id为1,2,3数据
cat >impdp_test_0701.par

userid="/ as sysdba"
directory=DATAPUMP
dumpfile=expdp_test.dmp
logfile=impdp_test_0701.log                                                     
CLUSTER=N
tables=(
tab1
tab2
tab3
)
remap_schema=user1:user3
table_exists_action=replace
QUERY=(user1.tab1:" where id  in (1,2,3) ")

Networklink方式导入

通过dblink的方式导入，可以跳过导出步骤，直接导入数据
Example:

# 源库操作:
create user datapump identified by datapump;
grant dba to datapump;

# 目标端操作
# 创建db_links:
create database link datapump_link connect to datapump identified by datapump using '(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = xxx.xx.xx.xx)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcldb)
    )
  )';
# 检查db_link是否可用
select sysdate from dual@datapump_link;

#  将源端user01用户导入到目标库test01用户
cat >impdp_20190101.par

userid=" / as sysdba"
network_link=datapump_link
logfile=impdp_20190101.log
SCHEMAS=user01
EXCLUDE=STATISTICS
REMAP_SCHEMA=user01:test01
PARALLEL=4

expdp/impdp问题处理

Ora-39120

导入选项为table_exists_action=truncate，报错信息Ora-39120,Metalink官方大致解决文档如下：

Impdp reports ORA-39120 and ORA-02266 when using table_exists_action=truncate (文档 ID 1432267.1)
# 1 查看表的约束信息：
select b.owner, b.table_name child_table,
       c.column_name FK_column, b.constraint_name
  from dba_constraints a, dba_constraints b, dba_cons_columns c
 where a.owner=b.r_owner
   and b.owner=c.owner
   and b.table_name=c.table_name
   and b.constraint_name=c.constraint_name
   and a.constraint_name=b.r_constraint_name
   and b.constraint_type='R'
   and a.owner='U2'
   and a.table_name='PARENT1'
   and a.CONSTRAINT_TYPE='P';

#2 针对报错的表的约束disable:

select 'alter table '||b.owner||'.'||b.table_name||' disable constraint '|| b.constraint_name ||';'
  from dba_constraints a, dba_constraints b, dba_cons_columns c
 where a.owner=b.r_owner
   and b.owner=c.owner
   and b.table_name=c.table_name
   and b.constraint_name=c.constraint_name
   and a.constraint_name=b.r_constraint_name
   and b.constraint_type='R'
   and a.owner='U2'
   and a.table_name='PARENT1'
   and a.CONSTRAINT_TYPE='P';

#3 重新导入报错的表

#4 启用约束enable:

select 'alter table '||b.owner||'.'||b.table_name||' enable constraint '|| b.constraint_name||';'
  from dba_constraints a, dba_constraints b, dba_cons_columns c
 where a.owner=b.r_owner
   and b.owner=c.owner
   and b.table_name=c.table_name
   and b.constraint_name=c.constraint_name
   and a.constraint_name=b.r_constraint_name
   and b.constraint_type='R'
   and a.owner='U2'
   and a.table_name='PARENT1'
   and a.CONSTRAINT_TYPE='P';

Expdp/Impdp逻辑工具使用说明
本文记录了自己日常导数据常用的参数和遇到的问题的解决办法,逻辑备份需要不能作为热备，但在日常工作中测试，或数据迁移...
expdp/impdp命令解析
简介：expdp/impdp数据泵是数据库服务端工具，在数据库客户端不可用。１、expdp，导出命令使用模板：...
oracle12c windows下面执行impdp 报：未找
impdp system/manager DIRECTORY=dpdata1 DUMPFILE=expdp.dmp...
expdp & impdp
1.使用expdp要先在数据库中创建directory,并给相应的用户read,write权限. SQL>crea...
Oracle expdp/impdp
Oracle Data Pump (expdp, impdp) 是 Oracle 10g 之后中新增的一个功能，用...
ORACLE expdp impdp
Oracle Data Pump Data Pump Export Create directory object...
Oracle数据泵常用命令
导读：expdp和impdp是oracle数据库之间移动数据的工具，本文简单总结了数据泵的常用命令，希望对大家有帮...
ORACLE数据泵
数据泵使用EXPDP和IMPDP时应该注意的事项： EXP和IMP是客户端工具程序，它们既可以在客户端使用，也可以...
2018-12-22
二、使用impdp/expdp导入导出表 1、导出数据库语句（exdmp 用户名/密码 dumpfile=文件名...
impdp和expdp使用总结
oracle impdp/expdp 使用详解今天在项目中遇到了如下情况：备份恢复一张数据量比较大（大约200w...

Expdp/Impdp逻辑工具使用说明

expdp

expdp的路径:

按用户导数据

按用户表（TABLE）

按整库（DATABASE）

按表空间（TABLESPACE）

其他选项使用

impdp

按用户导入

按表导入

Networklink方式导入

expdp/impdp问题处理

Ora-39120

相关文章

Expdp/Impdp逻辑工具使用说明

expdp/impdp命令解析

oracle12c windows下面执行impdp 报：未找

expdp & impdp

Oracle expdp/impdp

ORACLE expdp impdp

Oracle数据泵常用命令

ORACLE数据泵

2018-12-22

impdp和expdp使用总结

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读

Oracle数据库管理之道