【Oracle】数据迁移工具（2）：Data Dump

Data Dump 使用命令行IMPDP/EXPDP实现导入导出表、schema、表空间及数据库。IMPDP/EXPDP命令行中可以加入以下选项，来实现更细粒度的导入导出。

IMPDP/EXPDP和IMP/EXP的区别在于：

1)  EXP 和 IMP 是客户端工具程序,它们既可以在可以客户端使用,也可以在服务端使用。

2)  EXPDP 和 IMPDP 是服务端的工具程序,他们只能在 ORACLE 服务端使用,不能在客户端使用。

3)  IMP 只适用于 EXP 导出文件,不适用于 EXPDP 导出文件;IMPDP 只适用于 EXPDP 导出文件,而不适

    用于 EXP 导出文件。

 

1.EXPDP命令行选项

1)  ATTACH

该选项用于在客户会话与已存在导出作用之间建立关联.语法如下

ATTACH=[schema_name.]job_name

Schema_name 用于指定方案名,job_name 用于指定导出作业名.注意,如果使用 ATTACH 选项,在命令

行除了连接字符串和 ATTACH 选项外,不能指定任何其他选项,示例如下:

Expdp scott/tiger ATTACH=scott.export_job

2)  CONTENT

该选项用于指定要导出的内容.默认值为 ALL

CONTENT={ALL | DATA_ONLY | METADATA_ONLY}

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

METADATA_ONLY 时,只导出对象定义

Expdp scott/tiger DIRECTORY=dump DUMPFILE=a.dump

CONTENT=METADATA_ONLY

3)  DIRECTORY

指定转储文件和日志文件所在的目录

DIRECTORY=directory_object

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

的对象,而不是 OS 目录

Expdp scott/tiger DIRECTORY=dump DUMPFILE=a.dump

建立目录:

CREATE DIRECTORY dump as 'd:dump';

查询创建了那些子目录:

SELECT * FROM dba_directories;

4)  DUMPFILE

用于指定转储文件的名称,默认名称为 expdat.dmp

DUMPFILE=[directory_object:]file_name [,….]

Directory_object 用于指定目录对象名,file_name 用于指定转储文件名.需要注意,如果不指定

directory_object,导出工具会自动使用 DIRECTORY 选项指定的目录对象

Expdp scott/tiger DIRECTORY=dump1 DUMPFILE=dump2:a.dmp

5)  ESTIMATE

指定估算被导出表所占用磁盘空间分方法.默认值是 BLOCKS

EXTIMATE={BLOCKS | STATISTICS}

设置为 BLOCKS 时,oracle 会按照目标对象所占用的数据块个数乘以数据块尺寸估算对象占用的空间,设

置为 STATISTICS 时,根据最近统计值估算对象占用空间

Expdp scott/tiger TABLES=emp ESTIMATE=STATISTICS

DIRECTORY=dump DUMPFILE=a.dump

6)  EXTIMATE_ONLY

指定是否只估算导出作业所占用的磁盘空间,默认值为 N

EXTIMATE_ONLY={Y | N}

设置为 Y 时,导出作用只估算对象所占用的磁盘空间,而不会执行导出作业,为 N 时,不仅估算对象所占用

的磁盘空间,还会执行导出操作.

Expdp scott/tiger ESTIMATE_ONLY=y NOLOGFILE=y

7)  EXCLUDE

该选项用于指定执行操作时释放要排除对象类型或相关对象

EXCLUDE=object_type[:name_clause] [,….]

Object_type 用于指定要排除的对象类型,name_clause 用于指定要排除的具体对象.EXCLUDE 和

INCLUDE 不能同时使用

Expdp scott/tiger DIRECTORY=dump DUMPFILE=a.dup EXCLUDE=VIEW

8)  FILESIZE

指定导出文件的最大尺寸,默认为 0,(表示文件尺寸没有限制)

9)  FLASHBACK_SCN

指定导出特定 SCN 时刻的表数据

FLASHBACK_SCN=scn_value

Scn_value 用于标识 SCN 值.FLASHBACK_SCN 和 FLASHBACK_TIME 不能同时使用

Expdp scott/tiger DIRECTORY=dump DUMPFILE=a.dmp

FLASHBACK_SCN=358523

10) FLASHBACK_TIME

指定导出特定时间点的表数据

FLASHBACK_TIME="TO_TIMESTAMP(time_value)"

Expdp scott/tiger DIRECTORY=dump DUMPFILE=a.dmp FLASHBACK_TIME=

"TO_TIMESTAMP('25-08-2004 14:35:00', 'DD-MM-YYYY HH24:MI:SS')"

11) FULL

指定数据库模式导出,默认为 N

FULL={Y | N}

为 Y 时,标识执行数据库导出.

12) HELP

指定是否显示 EXPDP 命令行选项的帮助信息,默认为 N

当设置为 Y 时,会显示导出选项的帮助信息.

Expdp help=y

13) INCLUDE

指定导出时要包含的对象类型及相关对象

INCLUDE = object_type[:name_clause] [,… ]

14) JOB_NAME

指定要导出作用的名称,默认为 SYS_XXX

JOB_NAME=jobname_string

15) LOGFILE

指定导出日志文件文件的名称,默认名称为 export.log

LOGFILE=[directory_object:]file_name

Directory_object 用于指定目录对象名称,file_name 用于指定导出日志文件名.如果不指定

directory_object.导出作用会自动使用 DIRECTORY 的相应选项值.

Expdp scott/tiger DIRECTORY=dump DUMPFILE=a.dmp logfile=a.log

16) NETWORK_LINK

指定数据库链名,如果要将远程数据库对象导出到本地例程的转储文件中,必须设置该选项.

17) NOLOGFILE

该选项用于指定禁止生成导出日志文件,默认值为 N.

18) PARALLEL

指定执行导出操作的并行进程个数,默认值为 1

19) PARFILE

指定导出参数文件的名称

PARFILE=[directory_path] file_name

20) QUERY

用于指定过滤导出数据的 where 条件

QUERY=[schema.] [table_name:] query_clause

Schema 用于指定方案名,table_name 用于指定表名,query_clause 用于指定条件限制子句.QUERY

选项不能与 CONNECT=METADATA_ONLY,EXTIMATE_ONLY,TRANSPORT_TABLESPACES 等选项同时使

用.

Expdp scott/tiger directory=dump dumpfiel=a.dmp

Tables=emp query=’WHERE deptno=20’

21) SCHEMAS

该方案用于指定执行方案模式导出,默认为当前用户方案.

22) STATUS

指定显示导出作用进程的详细状态,默认值为 0

23) TABLES

指定表模式导出

TABLES=[schema_name.]table_name[:partition_name][,…]

Schema_name 用于指定方案名,table_name 用于指定导出的表名,partition_name 用于指定要导出

的分区名.

24) TABLESPACES

指定要导出表空间列表

25) TRANSPORT_FULL_CHECK

该选项用于指定被搬移表空间和未搬移表空间关联关系的检查方式,默认为 N.

当 设置为 Y 时,导出作用会检查表空间直接的完整关联关系,如果表空间所在表空间或其索引所在的表空

间只有一个表空间被搬移,将显示错误信息.当设置为 N 时, 导出作用只检查单端依赖,如果搬移索引所在

表空间,但未搬移表所在表空间,将显示出错信息,如果搬移表所在表空间,未搬移索引所在表空间,则不会

显示错误 信息.

26) TRANSPORT_TABLESPACES

指定执行表空间模式导出

27) VERSION

指定被导出对象的数据库版本,默认值为 COMPATIBLE.

VERSION={COMPATIBLE | LATEST | version_string}

为 COMPATIBLE 时,会根据初始化参数 COMPATIBLE 生成对象元数据;为 LATEST 时,会根据数据库的实

际版本生成对象元数据.version_string 用于指定数据库版本字符串。

 

2.EXPDP应用

1）建立目录对象，并赋权给用户（如果用sys用户，则不需要赋权）

[oracle@drz ~]$ mkdir datadump

[oracle@drz ~]$ cd datadump/

[oracle@drz datadump]$ sqlplus / as sysdba

 

SQL*Plus: Release 11.2.0.4.0 Production on Wed Jan 18 11:37:55 2017

 

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

 

 

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

 

SYS@drz>create or replace directory dir_dp as '/home/oracle/datadump';

 

Directory created.

 

SYS@drz>grant read,write on directory dir_dp to scott;

 

Grant succeeded.

 

2）导出表中指定行

[oracle@drz datadump]$ expdp scott/tiger directory=dir_dp dumpfile=emp30.dmp tabl

es=emp query="'where deptno=30'";

Export: Release 11.2.0.4.0 - Production on Wed Jan 18 11:41:44 2017

 

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

 

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit P

roductionWith the Partitioning, OLAP, Data Mining and Real Application Testing options

Starting "SCOTT"."SYS_EXPORT_TABLE_01":  scott/******** directory=dir_dp dumpfile

=emp30.dmp tables=emp query='where deptno=30' Estimate in progress using BLOCKS method...

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 64 KB

Processing object type TABLE_EXPORT/TABLE/TABLE

Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX

Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT

. . exported "SCOTT"."EMP"                                8.25 KB       6 rows

Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded

******************************************************************************

Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:

  /home/oracle/datadump/emp30.dmp

Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at Wed Jan 18 11:41:56 2

017 elapsed 0 00:00:10

[oracle@drz datadump]$ ls

emp30.dmp  export.log    ##默认生成log文件，下次导出时覆盖此文件。

 

3）运用parfile导出表中指定行

[oracle@drz datadump]$ vi par.txt

userid=scott/tiger directory=dir_dp dumpfile=emp30.dmp logfile=emp30.log tables=emp query='where deptno=30'

 

[oracle@drz datadump]$ expdp parfile=par.txt

 

Export: Release 11.2.0.4.0 - Production on Wed Jan 18 11:48:06 2017

 

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

 

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit P

roductionWith the Partitioning, OLAP, Data Mining and Real Application Testing options

Starting "SCOTT"."SYS_EXPORT_TABLE_01":  scott/******** parfile=par.txt

Estimate in progress using BLOCKS method...

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 64 KB

Processing object type TABLE_EXPORT/TABLE/TABLE

Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX

Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT

. . exported "SCOTT"."EMP"                                8.25 KB       6 rows

Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded

******************************************************************************

Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:

  /home/oracle/datadump/emp30.dmp

Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at Wed Jan 18 11:48:17 2

017 elapsed 0 00:00:09

[oracle@drz datadump]$ ls

emp30.dmp  emp30.log  par.txt

 

4）导出表

[oracle@drz datadump]$ expdp scott/tiger directory=dir_dp dumpfile=emp.dmp tables

=emp

Export: Release 11.2.0.4.0 - Production on Wed Jan 18 11:52:11 2017

 

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

 

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit P

roductionWith the Partitioning, OLAP, Data Mining and Real Application Testing options

Starting "SCOTT"."SYS_EXPORT_TABLE_01":  scott/******** directory=dir_dp dumpfile

=emp.dmp tables=emp Estimate in progress using BLOCKS method...

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 64 KB

Processing object type TABLE_EXPORT/TABLE/TABLE

Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX

Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT

. . exported "SCOTT"."EMP"                               8.562 KB      14 rows

Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded

******************************************************************************

Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:

  /home/oracle/datadump/emp.dmp

Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at Wed Jan 18 11:52:21 2

017 elapsed 0 00:00:08

[oracle@drz datadump]$ ls

emp.dmp  export.log

 

5）导出schema

[oracle@drz datadump]$ expdp scott/tiger directory=dir_dp dumpfile=scott.dmp sche

mas=scott

Export: Release 11.2.0.4.0 - Production on Wed Jan 18 11:57:23 2017

 

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

 

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit P

roductionWith the Partitioning, OLAP, Data Mining and Real Application Testing options

Starting "SCOTT"."SYS_EXPORT_SCHEMA_01":  scott/******** directory=dir_dp dumpfil

e=scott.dmp schemas=scott Estimate in progress using BLOCKS method...

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 896 KB

Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

Processing object type SCHEMA_EXPORT/TABLE/TABLE

Processing object type SCHEMA_EXPORT/TABLE/COMMENT

Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT

Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

. . exported "SCOTT"."T1"                                  531 KB    5756 rows

. . exported "SCOTT"."DEPT"                              5.929 KB       4 rows

. . exported "SCOTT"."EMP"                               8.562 KB      14 rows

. . exported "SCOTT"."SALGRADE"                          5.859 KB       5 rows

. . exported "SCOTT"."T2"                                19.77 KB     100 rows

. . exported "SCOTT"."BONUS"                                 0 KB       0 rows

Master table "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded

******************************************************************************

Dump file set for SCOTT.SYS_EXPORT_SCHEMA_01 is:

  /home/oracle/datadump/scott.dmp

Job "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully completed at Wed Jan 18 11:57:45

2017 elapsed 0 00:00:21

[oracle@drz datadump]$ ls

export.log  scott.dmp

 

6）导出表空间

##要使用DBA角色的用户导出，否则只能导出本schema的表空间

[oracle@drz datadump]$ expdp system/oracle directory=dir_dp dumpfile=ts.dmp logfi

le=ts.log tablespaces=users

Export: Release 11.2.0.4.0 - Production on Wed Jan 18 12:05:59 2017

 

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

 

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit P

roductionWith the Partitioning, OLAP, Data Mining and Real Application Testing options

Starting "SYSTEM"."SYS_EXPORT_TABLESPACE_01":  system/******** directory=dir_dp d

umpfile=ts.dmp logfile=ts.log tablespaces=users Estimate in progress using BLOCKS method...

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 896 KB

Processing object type TABLE_EXPORT/TABLE/TABLE

Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX

Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT

Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

. . exported "SCOTT"."T1"                                  531 KB    5756 rows

. . exported "SCOTT"."DEPT"                              5.929 KB       4 rows

. . exported "SCOTT"."EMP"                               8.562 KB      14 rows

. . exported "SCOTT"."SALGRADE"                          5.859 KB       5 rows

. . exported "SCOTT"."T2"                                19.77 KB     100 rows

. . exported "SCOTT"."BONUS"                                 0 KB       0 rows

Master table "SYSTEM"."SYS_EXPORT_TABLESPACE_01" successfully loaded/unloaded

******************************************************************************

Dump file set for SYSTEM.SYS_EXPORT_TABLESPACE_01 is:

  /home/oracle/datadump/ts.dmp

Job "SYSTEM"."SYS_EXPORT_TABLESPACE_01" successfully completed at Wed Jan 18 12:0

6:09 2017 elapsed 0 00:00:08

[oracle@drz datadump]$ ll

total 1620

-rw-r--r-- 1 oracle oinstall   1891 Jan 18 11:57 export.log

-rw-r----- 1 oracle oinstall 856064 Jan 18 11:57 scott.dmp

-rw-r----- 1 oracle oinstall 786432 Jan 18 12:06 ts.dmp

-rw-r--r-- 1 oracle oinstall   1728 Jan 18 12:06 ts.log

 

7）导出整个数据库

[oracle@drz datadump]$ expdp system/oracle directory=dir_dp dumpfile=db.dmp logfi

le=db.log full=y

Export: Release 11.2.0.4.0 - Production on Wed Jan 18 12:07:43 2017

 

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

 

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit P

roductionWith the Partitioning, OLAP, Data Mining and Real Application Testing options

Starting "SYSTEM"."SYS_EXPORT_FULL_01":  system/******** directory=dir_dp dumpfil

e=db.dmp logfile=db.log full=y Estimate in progress using BLOCKS method...

...省略中间内容...

. . exported "SYSTEM"."REPCAT$_TEMPLATE_PARMS"               0 KB       0 rows

. . exported "SYSTEM"."REPCAT$_TEMPLATE_REFGROUPS"           0 KB       0 rows

. . exported "SYSTEM"."REPCAT$_TEMPLATE_SITES"               0 KB       0 rows

. . exported "SYSTEM"."REPCAT$_TEMPLATE_TARGETS"             0 KB       0 rows

. . exported "SYSTEM"."REPCAT$_USER_AUTHORIZATIONS"          0 KB       0 rows

. . exported "SYSTEM"."REPCAT$_USER_PARM_VALUES"             0 KB       0 rows

. . exported "SYSTEM"."SQLPLUS_PRODUCT_PROFILE"              0 KB       0 rows

Master table "SYSTEM"."SYS_EXPORT_FULL_01" successfully loaded/unloaded

******************************************************************************

Dump file set for SYSTEM.SYS_EXPORT_FULL_01 is:

  /home/oracle/datadump/db.dmp

Job "SYSTEM"."SYS_EXPORT_FULL_01" successfully completed at Wed Jan 18 12:08:25 2

017 elapsed 0 00:00:41

[oracle@drz datadump]$ ls

db.dmp  db.log  export.log  scott.dmp  ts.dmp  ts.log

 

8)EXCLUDE/INCLUDE  用法

  ☆语法

  ·剔除指定对象：

EXCLUDE=[object_type]:[name_clause],[object_type]:[name_clause]

  ·包含指定对象：

INCLUDE=[object_type]:[name_clause],[object_type]:[name_clause]

object_type: table,sequence,view,procedure,package 等所有对象类型

name_claus:由 SQL 操作符以及对象名(可使用通配符)来过滤指定对象类型中的特定对象

  ☆示例

  ·expdp ：

expdp <other_parameters>

SCHEMAS=scott EXCLUDE=SEQUENCE,TABLE:"IN ('EMP','DEPT')"

  ·impdp ：

impdp <other_parameters>

SCHEMAS=scott INCLUDE=PACKAGE,FUNCTION,PROCEDURE,TABLE:"='EMP'"

注 示例无法运行，需要转义，之所以要转义是因为，这些命令是在操作系统中执行的，操作系统中逗号、

括号等都有特定的含义，所以，如果要将其当做字符使用的话，那么需要进行转义

  常用的过滤表达式

  · 过滤所有的 SEQUENCE,VIEW

EXCLUDE=SEQUENCE,VIEW

  · 过滤表对象 EMP,DEPT

EXCLUDE=TABLE:"IN ('EMP','DEPT')"

  · 过滤所有的 SEQUENCE,VIEW  以及表对象 EMP,DEPT

EXCLUDE=SEQUENCE,VIEW,TABLE:"IN ('EMP','DEPT')"

  · 过滤指定的索引对象 IDX_NAME

EXCLUDE=INDEX:"= 'INDX_NAME'"

  · 包含以 SP  开头的所有存储过程

INCLUDE=PROCEDURE:"LIKE 'SP%'"

①EXCLUDE测试

导出scott用户下不包含emp、dept的表   ##注意标点符号需要转义

[oracle@drz datadump]$ expdp scott/tiger directory=dir_dp dumpfile=scott_all_tab.

dmp schemas=scott exclude=table:\"in \(\'emp\',\'dept\'\)\"

 

Export: Release 11.2.0.4.0 - Production on Wed Jan 18 12:34:29 2017

 

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

 

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit P

roductionWith the Partitioning, OLAP, Data Mining and Real Application Testing options

Starting "SCOTT"."SYS_EXPORT_SCHEMA_01":  scott/******** directory=dir_dp dumpfil

e=scott_all_tab.dmp schemas=scott exclude=table:"in ('emp','dept')" Estimate in progress using BLOCKS method...

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 896 KB

Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

Processing object type SCHEMA_EXPORT/TABLE/TABLE

Processing object type SCHEMA_EXPORT/TABLE/COMMENT

Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT

Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

. . exported "SCOTT"."T1"                                  531 KB    5756 rows

. . exported "SCOTT"."DEPT"                              5.929 KB       4 rows

. . exported "SCOTT"."EMP"                               8.562 KB      14 rows

. . exported "SCOTT"."SALGRADE"                          5.859 KB       5 rows

. . exported "SCOTT"."T2"                                19.77 KB     100 rows

. . exported "SCOTT"."BONUS"                                 0 KB       0 rows

Master table "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded

******************************************************************************

Dump file set for SCOTT.SYS_EXPORT_SCHEMA_01 is:

  /home/oracle/datadump/scott_all_tab.dmp

Job "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully completed at Wed Jan 18 12:34:49

2017 elapsed 0 00:00:19

[oracle@drz datadump]$ ls

db.dmp  db.log  export.log  scott_all_tab.dmp  scott.dmp  ts.dmp  ts.log

 

②INCLUDE用法

导出scott用户下的所有对象，表只包括emp和dept

[oracle@drz datadump]$ expdp scott/tiger directory=dir_dp dumpfile=scott_all_tab8

.dmp schemas=scott include=table:\" in \(\'EMP\',\'DEPT\'\)\"

Export: Release 11.2.0.4.0 - Production on Wed Jan 18 12:43:25 2017

 

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

 

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit P

roductionWith the Partitioning, OLAP, Data Mining and Real Application Testing options

Starting "SCOTT"."SYS_EXPORT_SCHEMA_01":  scott/******** directory=dir_dp dumpfil

e=scott_all_tab8.dmp schemas=scott include=table:" in ('EMP','DEPT')" Estimate in progress using BLOCKS method...

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 128 KB

Processing object type SCHEMA_EXPORT/TABLE/TABLE

Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT

. . exported "SCOTT"."DEPT"                              5.929 KB       4 rows

. . exported "SCOTT"."EMP"                               8.562 KB      14 rows

Master table "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded

******************************************************************************

Dump file set for SCOTT.SYS_EXPORT_SCHEMA_01 is:

  /home/oracle/datadump/scott_all_tab8.dmp

Job "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully completed at Wed Jan 18 12:43:36

2017 elapsed 0 00:00:09

[oracle@drz datadump]$ ls

db.dmp  db.log  export.log  scott_all_tab8.dmp  scott_all_tab.dmp  scott.dmp  ts.dmp  ts.log

 

9） Content

使用 CONTENT 参数在当前操作中可只请求元数据、只请求数据，或者请求这两者。语法：

CONTENT = ALL | METADATA_ONLY | DATA_ONLY

10）SAMPLE

取样率，导出表EMP百分之二十的数据

[oracle@drz datadump]$ expdp scott/tiger directory=dir_dp dumpfile=emp.dmp tables

=emp sample=20

Export: Release 11.2.0.4.0 - Production on Wed Jan 18 12:49:52 2017

 

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

 

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit P

roductionWith the Partitioning, OLAP, Data Mining and Real Application Testing options

Starting "SCOTT"."SYS_EXPORT_TABLE_01":  scott/******** directory=dir_dp dumpfile

=emp.dmp tables=emp sample=20 Estimate in progress using BLOCKS method...

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 12.79 KB

Processing object type TABLE_EXPORT/TABLE/TABLE

Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX

Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT

. . exported "SCOTT"."EMP"                               8.078 KB       2 rows

Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded

******************************************************************************

Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:

  /home/oracle/datadump/emp.dmp

Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at Wed Jan 18 12:50:01 2

017 elapsed 0 00:00:08

 

11）FILESIZE

##filesize与%U一起使用，%U自动生成递增的序号，另外filesize经常与parallel一起配合使用

[oracle@drz datadump]$ expdp system/oracle directory=dir_dp dumpfile=system_%U.dm

p schemas=system filesize=1M

Export: Release 11.2.0.4.0 - Production on Wed Jan 18 12:52:53 2017

 

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

 

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit P

roductionWith the Partitioning, OLAP, Data Mining and Real Application Testing options

Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01":  system/******** directory=dir_dp dumpf

ile=system_%U.dmp schemas=system filesize=1M Estimate in progress using BLOCKS method...

...省略中间部分...

Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded

******************************************************************************

Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:

  /home/oracle/datadump/system_01.dmp

  /home/oracle/datadump/system_02.dmp

  /home/oracle/datadump/system_03.dmp

Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Wed Jan 18 12:53:21

 2017 elapsed 0 00:00:26

[oracle@drz datadump]$ ls

db.dmp  emp.dmp     scott_all_tab8.dmp  scott.dmp      system_02.dmp  ts.dmp

db.log  export.log  scott_all_tab.dmp   system_01.dmp  system_03.dmp  ts.log

 

12) REMAP_DATAFILE

该选项用于将源数据文件名转变为目标数据文件名，在不同平台之间搬移表空间时可能需要该选项。

REMAP_DATAFIEL=source_datafie:target_datafile

 

13) REMAP_SCHEMA

该选项用于将源方案的所有对象装载到目标方案中。

REMAP_SCHEMA=source_schema:target_schema

 

14) REMAP_TABLESPACE

将源表空间的所有对象导入到目标表空间中。

REMAP_TABLESPACE=source_tablespace:target:tablespace

 

 

3.IMPDP命令行选项

IMPDP  命令行选项与 EXPDP  有很多相同的, 不同的 如下：

1)  REMAP_DATAFILE

该选项用于将源数据文件名转变为目标数据文件名,在不同平台之间搬移表空间时可能需要该选项.

REMAP_DATAFIEL=source_datafie:target_datafile

2)  REMAP_SCHEMA

该选项用于将源方案的所有对象装载到目标方案中.

REMAP_SCHEMA=source_schema:target_schema

3)  REMAP_TABLESPACE

将源表空间的所有对象导入到目标表空间中

REMAP_TABLESPACE=source_tablespace:target:tablespace

4)  REUSE_DATAFILES

该选项指定建立表空间时是否覆盖已存在的数据文件.默认为 N

REUSE_DATAFIELS={Y | N}

5)  SKIP_UNUSABLE_INDEXES

指定导入是是否跳过不可使用的索引,默认为 N

6)  SQLFILE

指定将导入要指定的索引 DDL 操作写入到 SQL 脚本中

SQLFILE=[directory_object:]file_name

Impdp scott/tiger DIRECTORY=dump DUMPFILE=tab.dmp SQLFILE=a.sql

7)  STREAMS_CONFIGURATION

指定是否导入流元数据(Stream Matadata),默认值为 Y.

8)  TABLE_EXISTS_ACTION

该选项用于指定当表已经存在时导入作业要执行的操作,默认为 SKIP

TABBLE_EXISTS_ACTION={SKIP | APPEND | TRUNCATE | FRPLACE }

当 设置该选项为 SKIP 时,导入作业会跳过已存在表处理下一个对象;当设置为 APPEND 时,会追加数据,

为 TRUNCATE 时,导入作业会截断表,然后为 其追加新数据;当设置为 REPLACE 时,导入作业会删除已存

在表,重建表病追加数据,注意,TRUNCATE 选项不适用与簇表和 NETWORK_LINK 选项

9)  TRANSFORM

该选项用于指定是否修改建立对象的 DDL 语句

TRANSFORM=transform_name:value[:object_type]

Transform_name 用于指定转换名,其中 SEGMENT_ATTRIBUTES 用于标识段属性(物理属性,存储属性,

表空间,日志等信息),STORAGE 用于标识段存储属 性,VALUE 用于指定是否包含段属性或段存储属

性,object_type 用于指定对象类型.

Impdp scott/tiger directory=dump dumpfile=tab.dmp

Transform=segment_attributes:n:table

10) TRANSPORT_DATAFILES

该选项用于指定搬移空间时要被导入到目标数据库的数据文件

TRANSPORT_DATAFILE=datafile_name

Datafile_name 用于指定被复制到目标数据库的数据文件

Impdp system/manager DIRECTORY=dump DUMPFILE=tts.dmp

TRANSPORT_DATAFILES='/user01/data/tbs1.f'

 

4.IMPDP

1）把表导入lgr用户

##创建测试用户

SYS@drz>grant connect,resource to lgr identified by lgr;

 

Grant succeeded.

 

SYS@drz>host

##导入表并将表映射到lgr用户下

[oracle@drz datadump]$ impdp system/oracle directory=dir_dp dumpfile=emp30.dmp r

map_schema=scott:lgr

Import: Release 11.2.0.4.0 - Production on Wed Jan 18 13:14:28 2017

 

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

 

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit

roductionWith the Partitioning, OLAP, Data Mining and Real Application Testing options

Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded

Starting "SYSTEM"."SYS_IMPORT_FULL_01":  system/******** directory=dir_dp dumpfi

e=emp30.dmp remap_schema=scott:lgr Processing object type TABLE_EXPORT/TABLE/TABLE

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

. . imported "LGR"."EMP"                                  8.25 KB       6 rows

Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX

Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT

ORA-39083: Object type REF_CONSTRAINT failed to create with error:

ORA-00942: table or view does not exist

Failing sql is:

ALTER TABLE "LGR"."EMP" ADD CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO") REFERE

CES "LGR"."DEPT" ("DEPTNO") ENABLEJob "SYSTEM"."SYS_IMPORT_FULL_01" completed with 1 error(s) at Wed Jan 18 13:14:

2 2017 elapsed 0 00:00:03

[oracle@drz datadump]$ exit

exit

 

SYS@drz>conn lgr/lgr     

Connected.

LGR@drz>select * from tab;

 

TNAME        TABTYPE         CLUSTERID

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

EMP            TABLE

## 在导入的时候，会提示个错误，原因是 p emp  表存在外键的缘故，如果是单纯到粗 p emp  表，那么此错误可以忽略。

 

2）导入schema

##创建drz用户

SYS@drz>grant connect,resource to drz identified by drz;

 

Grant succeeded.

 

SYS@drz>

SYS@drz>host

##将schema导入drz用户

[oracle@drz datadump]$ impdp system/oracle directory=dir_dp dumpfile=scott.dmp r

emap_schema=scott:drz

Import: Release 11.2.0.4.0 - Production on Wed Jan 18 13:24:15 2017

 

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

 

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit

ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing options

Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded

Starting "SYSTEM"."SYS_IMPORT_FULL_01":  system/******** directory=dir_dp dumpfi

le=scott.dmp remap_schema=scott:drz Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

Processing object type SCHEMA_EXPORT/TABLE/TABLE

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

. . imported "DRZ"."T1"                                    531 KB    5756 rows

. . imported "DRZ"."DEPT"                                5.929 KB       4 rows

. . imported "DRZ"."EMP"                                 8.562 KB      14 rows

. . imported "DRZ"."SALGRADE"                            5.859 KB       5 rows

. . imported "DRZ"."T2"                                  19.77 KB     100 rows

. . imported "DRZ"."BONUS"                                   0 KB       0 rows

Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT

Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at Wed Jan 18 13:24:20

2017 elapsed 0 00:00:03

[oracle@drz datadump]$ exit

exit

 

SYS@drz>conn drz/drz

Connected.

DRZ@drz>select tname from tab;

 

TNAME

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

BONUS

DEPT

EMP

SALGRADE

T1

T2

 

6 rows selected.

 

3）导入表空间

##删除USERS表空间下所有表

SYS@drz>select 'drop table '||OWNER||'.'||TABLE_NAME||' purge;'from dba_tables w

here tablespace_name='USERS';

'DROPTABLE'||OWNER||'.'||TABLE_NAME||'PURGE;'

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

drop table SCOTT.DEPT purge;

drop table SCOTT.EMP purge;

drop table SCOTT.BONUS purge;

drop table SCOTT.SALGRADE purge;

drop table SCOTT.T1 purge;

drop table SCOTT.T2 purge;

drop table LGR.EMP purge;

drop table DRZ.T1 purge;

drop table DRZ.SALGRADE purge;

drop table DRZ.T2 purge;

drop table DRZ.BONUS purge;

 

'DROPTABLE'||OWNER||'.'||TABLE_NAME||'PURGE;'

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

drop table DRZ.EMP purge;

drop table DRZ.DEPT purge;

 

13 rows selected.

 

##此时USERS表空间的所有表都已经被删除

SYS@drz>select table_name,tablespace_name from user_tables where tablespace_name

='USERS';

no rows selected

##向USERS表空间中导入数据

[oracle@drz datadump]$ impdp system/oracle directory=dir_dp dumpfile=ts.dmp tabl

espaces=users;

Import: Release 11.2.0.4.0 - Production on Wed Jan 18 13:34:12 2017

 

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

 

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit

ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing options

Master table "SYSTEM"."SYS_IMPORT_TABLESPACE_01" successfully loaded/unloaded

Starting "SYSTEM"."SYS_IMPORT_TABLESPACE_01":  system/******** directory=dir_dp

dumpfile=ts.dmp tablespaces=users Processing object type TABLE_EXPORT/TABLE/TABLE

ORA-39151: Table "SCOTT"."T2" exists. All dependent metadata and data will be sk

ipped due to table_exists_action of skipORA-39151: Table "SCOTT"."T1" exists. All dependent metadata and data will be sk

ipped due to table_exists_action of skipORA-39151: Table "SCOTT"."SALGRADE" exists. All dependent metadata and data will

 be skipped due to table_exists_action of skipORA-39151: Table "SCOTT"."BONUS" exists. All dependent metadata and data will be

 skipped due to table_exists_action of skipORA-39151: Table "SCOTT"."EMP" exists. All dependent metadata and data will be s

kipped due to table_exists_action of skipORA-39151: Table "SCOTT"."DEPT" exists. All dependent metadata and data will be

skipped due to table_exists_action of skipProcessing object type TABLE_EXPORT/TABLE/TABLE_DATA

Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX

Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT

Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Job "SYSTEM"."SYS_IMPORT_TABLESPACE_01" completed with 6 error(s) at Wed Jan 18

13:34:16 2017 elapsed 0 00:00:02

 

4）导入数据库

[oracle@drz datadump]$ impdp system/oracle directory=dir_dp dumpfile=db.dmp full=y