Oracle数据库expdp用法以及注意事项

一、导出注意事项

检查数据库版本（用于决定导出时生成为哪个版本的dmp头文件） select version from v$instance; 也可以用sqlplus -v 查看。 

检查字符集是否一致（字符集不一致，不能导入） select userenv('language') from dual; 

检查数据量及磁盘空间（决定采取什么样的方式导出及导入）

二、expdp和exp的区别

在10g之前,传统的导出和导入分别使用EXP工具和IMP工具,从10g开始,不仅保留了原有的EXP和IMP工具,还提供了数据泵导出导入工具EXPDP和IMPDP：

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

2、EXPDP和IMPDP是服务端的工具程序,他们只能在ORACLE服务端使用,不能在客户端使用，客户端只能用来连接服务器启动导入导出操作，导出文件只存储在服务端。

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

4、EXPDP/IMPDP在备份和恢复时间上要比EXP/IMP有着优势，而且文件要小的多。

5、使用EXPDP/IMPDP管理更加灵活。支持并行；可中断；可以修改元数据文件名字、表空间名；支持元数据导入导出；估算导出数据所需空间；指定导入数据库版本等。

三、expdp使用方法

1、创建expdp导出目录，默认导出目录为D:\app\Administrator/admin/orcl/dpdump/（其中前面为Oracle安装目录）。

create or replace directory 目录名 as '目录路径'
--例：create or replace directory dmp as 'D:/app/dmp'

注意：目录路径必须是已经存在的目录，如果目录不存在，也会显示目录常见成功（语句不会自动去常见目录），在执行expdp时候会报以下错误：

ORA-39002: 操作无效

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

2、查看当前创建的所有dmp导出目录  (验证看是否创建成功)

select * from dba_directories

3、为创建的目录赋权限，需要以其他用户运行，建议在sys用户下执行。

grant read,write on directory 目录名 to 需要赋值的用户名
--例：grant read,write on directory dmp to GISDATA;

4、导出（这里使用最简单的导出方式）

expdp 用户名/密码@数据库实例名 directory=导出目录 dumpfile=导出的文件名.dmp logfile=导出的日志名.log
--例：expdp GISDATA/GISDATA@LOCALHOST/orcl directory=dmp dumpfile=GISDATA20200921.dmp logfile=GISDATA20200921.log 

四、常用导出命令

1、按用户导
expdp GISDATA/GISDATA@localhost/orcl schemas=GISDATA dumpfile=expdp.dmp directory=dmp  logfile=expdlog.log;
2、并行进程parallel
expdp GISDATA/GISDATA@localhost/orcl directory=dmp dumpfile=expdp.dmp parallel=40 job_name=expdpjob logfile=expdlog.log
3、按表名导
expdp GISDATA/GISDATA@localhost/orcl TABLES=test1,test2 dumpfile=expdp.dmp directory=dmp  logfile=expdlog.log;
4、按查询条件导
expdp GISDATA/GISDATA@localhost/orcl directory=dmp  dumpfile=expdp.dmp Tables=test query='WHERE id<20' logfile=expdlog.log;
5、按表空间导
expdp GISDATA/GISDATA directory=dmp  dumpfile=expdp.dmp TABLESPACES=GIDDATA,YWDATA logfile=expdlog.log;
6、导整个数据库
expdp GISDATA/GISDATA directory=dmp  dumpfile=expdp.dmp FULL=y logfile=expdlog.log;

五、expdp参数说明

可以输入expdp help=y命令查看其参数说明。

• ATTACH

作用
    当我们使用ctrl+C 退出交互式命令时，可心使用attach参数重新进入到交互模式
语法
    ATTACH=[schema_name.]job_name
    Schema_name用户名,job_name任务名
示例
    Expdp scott/tiger ATTACH=scott.export_job

• CONTENT

作用
    限制了导出的内容，包括三个级别：全部／数据／元数据（结构）
语法
   CONTENT={ALL | DATA_ONLY | METADATA_ONLY}
   ALL           -- 导出所有数据，包括元数据及数据
   DATA_ONLY     -- 只导出数据
   METADATA_ONLY -- 只包含元数据，就是创建语句
示例
   Expdp scott/tiger DIRECTORY=dump DUMPFILE=a.dump CONTENT=METADATA_ONLY

• DIRECTORY

作用

此路径可以理解为实际绝对路径在oracle数据库里的别名，是导出文件的存储位置
    路径的创建： create directory &DIRECTORY_NAME AS '&PATH';
    查看已存在路径： select  * from dba_directories;
语法
    directory=[directory_name]
示例
    Expdp scott/tiger DIRECTORY=dump_dir DUMPFILE=lhb.dump

• DUMPFILE

作用
    此参数用户命名导出文件，默认是 expdat.dmp. 文件的存储位置如果在文件名前没有指定directory,则会默认存储到directory参数指定的路径下。
语法
    DUMPFILE=[dump_dir:]file_name
示例
    Expdp scott/tiger DIRECTORY=dump_dir DUMPFILE=dump_dir1:a.dmp

• ESTIMATE

在使用Expdp进行导出时，Expdp需要计算导出数据大小容量，Oracle可以通过两种方式进行容量估算，一种是通过数据块(blocks)数量、一种是通过统计信息中记录的内容(statistics)估算.

语法结构：
    EXTIMATE={BLOCKS | STATISTICS}
示例：
    Expdp scott/tiger TABLES=emp ESTIMATE=STATISTICS DIRECTORY=dump_dir DUMPFILE=halberd.dump
    Expdp scott/tiger TABLES=emp ESTIMATE=BLOCKS DIRECTORY=dump_dir DUMPFILE=halberd.dump

• EXTIMATE_ONLY

作用
    此参数用于统计导出的数据量大小及统计过程耗时长短。
语法
    EXTIMATE_ONLY={Y | N}
示例
    Expdp scott/tiger ESTIMATE_ONLY=y NOLOGFILE=y directory=dump_dir schemas=halberd

• EXCLUDE

作用
    此参数用于排除不需要导出的内容，如我们进行全库导出，但是不需要导出用户scott，此时需要在exlude后先指定排除类型为schema,再指定具体的schema。具体使用方法见include参数. EXCLUDE与include的使用方法是一样的
语法
    EXCLUDE=object_type[:name_clause] [,object_type[:name_clause] ]
    name_clause
        "='object_name'"
        "in ('object_name'[,'object_name',....])"
        "in (select_clause) "
    Object_type对象类型，如：table,view,procedure,schema等
    name_clause指定名称的语句,如果不具体指定是哪个对象，则此类所有对象都不导出, select 语句中表名不要加用户名。用户名，通过schemas 指定。

示例
    expdp scott/tiger DIRECTORY=dump_dir DUMPFILE=halberd.dup EXCLUDE=VIEW
    expdp scott/tiger DIRECTORY=dump_dir DUMPFILE=halberd.dup EXCLUDE=TABLE:\" IN\(\'TEMP\',\'GRADE\'\)\"
    EXCLUDE=TABLE:"='APPLICATION_AUDIT'"

• FILESIZE

作用
    用于指定单个导出的数据文件的最大值，与％U一起使用。比如，我们需要导出100G的数据，文件全部存储到一个文件内，在文件传输时，会耗费大量的时间，此时我们就可以使用这个参数，限制每个文件的大小，在传输导出文件时，就可以多个文件同时传送，大大的节省了文件传输时间。提高了工作的效率。
语法
  FILESIZE=integer[B | K | M | G]
示例
   Expdp scott/tiger DIRECTORY=dump_dir DUMPFILE=halberd%U.dup FILESIZE=20g

• FLASHBACK_SCN／FLASHBACK_TIME

作用
    基于undo 及scn号(时间点)进行的数据导出。使用此参数设置会进行flashback query的功能，查询到对应指定的SCN时的数据，然后进行导出。只要UNDO不被覆盖，无论数据库是否重启，都可以进行导出. flashback_time参数与flashback_scn的原理是一样的。在导出的数据里保持数据的一致性是很有必要的。这个。。我想，没谁傻忽忽的把这两个参数一起使用吧？所以我就不提醒你两个参数不可以同时使用了。
语法
   FLASHBACK_SCN=scn_value
   FLASHBACK_TIME 有多种设定值的格式：
   flashback_time=to_timestamp (localtimestamp)
   flashback_time=to_timestamp_tz (systimestamp)
   flashback_time="TO_TIMESTAMP (""25-08-2003 14:35:00""， ""DD-MM-YYYY HH24:MI:SS"")"  使用此格式可能会遇到ORA-39150错误。
示例
   Expdp scott/tiger DIRECTORY=dump_dir DUMPFILE=halberd.dmp FLASHBACK_SCN= 12345567789
   Expdp scott/tiger DIRECTORY=dump_dir DUMPFILE=halberd.dmp FLASHBACK_TIME= to_timestamp (localtimestamp)

• FULL

作用
   指定导出内容为全库导出。这里需要特别注意的是,expdp 不能导出sys用户对象。即使是全库导出也不包含sys用户。
语法
   FULL={Y | N}
示例
   expdp \'\/ as sysdba\' directory=dump_dir full=y

• HELP

作用
    当我们对参数的意义不了解时，或者忘记参数怎么写时，就可以用这个参数，来寻求帮助，实际上和操作系统里的man命令是一样的。
示例
    impdp -help
    expdp help=y

• INCLUDE

作用
    限制范围，指定自己想要的内容，比如要导出某个用户的某张表。
语法
    INCLUDE = object_type[:name_clause],object_type[:name_clause]
示例
    impdp dbmon/dbmon_123 directory=dump_dir network_link=zjzwb2 SCHEMAS=AICBS remap_schema=aicbs:aicbsb include=table:\"IN\(SELECT TABLE_NAME FROM dbmon.TABLES_TOBE_MASKED\)\"  LOGFILE=zjzwb.log transform=segment_attributes:n
    PARFILE中设置:
        INCLUDE=table:"in(select table_name from dba_tables where owner='AA')"
        INCLUDE=TABLE:"IN('TEST1','TEST2')"
    SHELL环境设置:
        INCLUDE=TABLE:\"IN\(SELECT TABLE_NAME FROM DBA_TABLES WHERE OWNER=\'AA\'\)\"
        INCLUDE=TABLE:\"IN\(\'TEST1\',\'TEST2\'\)\"
说明
    当导入命令在目标端发起时，select 子句所涉及的表要在源端，并且dblink 所使用的用户有访问的权限。

• JOB_NAME

作用
    指定任务名，如果不指定的话，系统会默认自动命名：SYS_EXPORT_mode_nn
语法
    JOB_NAME=&JOB_NAME
其他
    查看有哪些expdp/impdp job，可以通过dba_datapump_jobs查看，其实你通过v$session.action也可以查看到
    大多与attach参数一起使用，重新进行expdp交互命令时使用。

• LOGFILE

作用： 指定导出日志名称。默认是：expdp.log
语法
    LOGFILE=[DIRECTORY:]file_name   , 如果参数值里没有指定路径，会默认使用directory参数值所指向的路径。
    directory ： 存储路径,
    file_name :日志文件名
示例
    expdp scott/tiger DIRECTORY=dump_dir DUMPFILE=halberd.dmp logfile=halberd.log
    impdp scott/tiger DIRECTORY=dump_dir DUMPFILE=halberd.dmp logfile=halberd.log

• NETWORK_LINK

作用
    此参数只有在导入（impdp）时使用，可通过本地数据库里的db_link连接到其他数据库A，将数据库A的数据直接导入到本地数据库。中间可节省导出数据文件，传送数据文件的过程。很方便。但是要特别注意，不同版本之间可能会存在问题，比如源库为10g,目标库为11g。使用network_link参数会报错。至于 12C 与低版本之间是否有问题尚未尝试。
语法
    network_link=[db_link]
示例
    impdp scott/tiger DIRECTORY=dump_dir DUMPFILE=halberd.dmp NETWORK_LINK=to_tjj SCHEMAS=halberd logfile=halberd.log

• NOLOGFILE

作用
    不写导入导出日志，这个笔者是灰常灰常滴不建议设置为“Y”滴。
语法
    nologfile=[y|n]

• PARALLEL

作用
    指定导出／导入时使用多少个并发，默认是1.
语法
    parallel=[digit]
示例
    expdp \'\/ as sysdba\' directory=dump_dir schemas=halberd dumpfile=halberd%U.dmp parallel=8 logfile=halberd.log

• PARFILE

作用
    参数文件，这个参数文件里，存储着一些参数的设置。比如上面说过的，parallel,network_link,等。导出时，可以使用此参数，expdp/impdp会自动读取文件中的参数设置，进行操作。
语法
    PARFILE=[directory_path] file_name
示例
   expdp \'\/ as sysdba\' parfile=halberd.par
   cat halberd.par
   directory=dump_dir                          
   logfile=test.log                            
   schemas=test                                
   query="where create_date > last_day(add_months(sysdate,-1)) and create_date <= last_day(sysdate)" 

   transform=segment_attributes:n                 
   network_link=to_aibcrm

   table_exists_action=append                    
   impdp \'\/ as sysdba\' parfile=test.par

• QUERY

作用
    此参数指定在导入导出时的限制条件，和SQL语句中的 "where" 语句是一样儿一样儿滴
语法
    QUERY=([schema.] [table_name:] query_clause, [schema.] [table_name:] query_clause,……)
    CONTENT=METADATA_ONLY, EXTIMATE_ONLY＝Y,TRANSPORT_TABLESPACES.
示例
   Expdp scott/tiger directory=dump dumpfiel=a.dmp Tables=emp query="WHERE deptno<>20"

• SCHEMAS

作用
    指定导出／导入哪个用户
语法
    schemas=schema_name[,schemaname,....]
示例
    expdp \'\/ as sysdba\' directory=dump_dir schemas=halberd

• REMAP_SCHEMA

 只在导入时使用
作用
    当把用户A的对象导入到用户（其实应该叫schema，将就看吧）B时，使用此参数，可实现要求
格式
    remap_schema=schema1: schema2
示例
    impdp \'\/ as sysdba\' directory=dump_dir dumpfile=halberd.dmp logfile=halberd.log remap_schema=scott:halberd

• TABLES

作用
    指定导出哪些表。
格式
    TABLES=[schema.]table_name[:partition_name][,[schema.]table_name[:partition_name]]
说明
    Schema 表的所有者；table_name表名；partition_name分区名.可以同时导出不同用户的不同的表
示例
    expdp \'\/ as sysdba\' directory=dump_dir tables=emp.emp_no,emp.dept

• TABLESPACES

作用
    指定导出／导入哪个表空间。
语法
    tablespaces=tablespace_name[,tablespace_name,....]
示例
    expdp \'\/ as sysdba\' directory=dump_dir tablespace=user

• REMAP_TABLESPACE

作用
    只有在导入时使用，用于进行数据的表空间迁移。 把前一个表空间中的对象导入到冒号后面的表空间
用法
    remap_tablespace=a:b
说明
   a: 数据所在的原表空间; b： 目标表空间
示例
   impdp \'\/ as sysdba\' directory=dump_dir tables=emp.dept remap_tablespace=user:user1

• TRANSPORT_FULL_CHECK

检查需要进行传输的表空间与其他不需要传输的表空间之间的信赖关系，默认为N。当设置为“Y”时，会对表空间之间的信赖关系进行检查，如A（索引表空间）信赖于B（表数据表空间），那么传输A而不传输B，则会出错，相反则不会报错。

• TRANSPORT_TABLESPACES

作用
    列出需要进行数据传输的表空间
格式
     TRANSPORT_TABLESPACES＝tablespace1[,tablespace2,.............]

• TRANSFORM

作用
  此参数只在导入时使用，是一个用于设定存储相关的参数,有时候也是相当方便的。假如数据对应的表空间都存在的话，就根本用不到这个参数，但是，假如数据存储的表空间不存在，使用此参数导入到用户默认表空间就可以了。更灵活的，可以使用remap_tablespace参数来指定。
格式
    transform=transform_name:value[bject_type]
    transform_name = [OID | PCTSPACE | SEGMENT_ATTRIBUTES | STORAGE]:[Y|N]
    segment attributes:段属性包括物理属性、存储属性、表空间和日志,Y 值按照导出时的存储属性导入，N时按照用户、表的默认属性导入
    storage:默认为Y，只取对象的存储属性作为导入作业的一部分
    oid:  owner_id,如果指定oid=Y(默认)，则在导入过程中将分配一个新的oid给对象表，这个参数我们基本不用管。
    pctspace:通过提供一个正数作为该转换的值，可以增加对象的分配尺寸，并且数据文件尺寸等于pctspace的值(按百分比)
示例
    transform=segment_attributes:n --表示将用户所有对象创建到用户默认表空间，而不再考虑原来的存储属性。

• VERSION

此参数主要在跨版本之间进行导数据时使用，更具体一点，是在从高版本数据库导入到低版本数据库时使用,从低版本导入到高版本，这个参数是不可用的。默认值是:compatible。此参数基本在导出时使用，导入时基本不可用。
VERSION={COMPATIBLE | LATEST | version_string}
COMPATIBLE       ： 以参数compatible的值为准，可以通过show parameter 查看compatible参数的值
LATEST           ： 以数据库版本为准
version_string   ： 指定版本。如： version=10.2.0.1

• SAMPLE

 SAMPLE 给出导出表数据的百分比，参数值可以取.000001~100（不包括100）。不过导出过程不会和这里给出的百分比一样精确，是一个近似值。 
 格式： SAMPLE=[[schema_name.]table_name:]sample_percent 
 示例： SAMPLE="HR"."EMPLOYEES":50

• table_exists_action

此参数只在导入时使用。
作用：导入时，假如目标库中已存在对应的表，对于这种情况，提供三种不同的处理方式：append,truncate,skip,replace
格式： table_exists_action=［append | replace| skip |truncate］
说明： append :   追加数据到表中
       truncate:  将目标库中的同名表的数据truncate掉。
       skip ：      遇到同名表，则跳过，不进行处理，注意：使用此参数值时，与该表相关的所有操作都会skip掉。
       replace:    导入过程中，遇到同名表，则替换到目标库的那张表（先drop,再创建）。
示例：  table_exists_action=replace

• SQLFILE

只在导入时使用！
作用： 使用此参数时，主要是将DMP文件中的metadata语句取出到一个单独的SQLfile中，而数据并不导入到数据库中
格式： sqlfile=&file_name.sql
示例： impdp \'\/ as sysdba\' directory=dump_dir dumpfile=halberd.dmp logfile=halberd.log sqlfile=halberd.sql

legacy mode 

在11g中，才有这种模式。这种模式里兼容了以前版本中的部分参数，如：consistent,reuse_dumpfiles等（其实我现在也就知道这两个参数，哈哈，以后再遇到再补充）

• consistent

这个是保持数据一致性的一个参数。在11g中使用时，如果设置 consistent=true,则会默认转换成 flashback_time参数，时间设置为命令开始执行的那个时间点。
格式： consistent=[true|false]

• reuse_dumpfiles

作用：重用导出的dmp文件 。假如第一次我们导失败了，虽然导出失败，但是dmp文件 还 是会生成的。在修改导出命令，第二次执行时，就可以 加上这个参数。
格式： reuse_dumpfile=[true|false]

• partition_options

1 NONE 不对分区做特殊处理。在系统上的分区表一样创建。
2 DEPARTITION 每个分区表和子分区表作为一个独立的表创建，名字使用表和分区（子分区）名字的组合。
3 MERGE 将所有分区合并到一个表 
注意：如果导出时使用了TRANSPORTABLE参数，这里就不能使用NONE和MERGE

六、使用技巧

1、不生成文件直接导入目标数据库

在一些情况下，我们并没有足够的存储空间允许我们存储导出的dmp文件。这个时候，我们就无计可施了么？ 不是的。我们可以不生成dmp文件，直接将数据抽取到目标数据。在迁移大量数据而没有充足存储空间时，这是一个救命稻草。 最关键的点就是在目标端执行impdp的时候，使用network_link，直接从源库抽取数据。 示例如下：

cat test.par
directory=dump_dir
logfile=test.log
schemas=test
query="where create_date > last_day(add_months(sysdate,-1)) and create_date <= last_day(sysdate)"
transform=segment_attributes:n
network_link=to_aibcrm
table_exists_action=append
impdp \'\/ as sysdba\' parfile=test.par/

2、通过shell脚本自动导入

此处只关注，impdp 命令在shell脚本中执行，需要转义的地方。

cat import_sr.sh
#!/bin/sh
cd /u01/app
for da in 2012-10 2013-09 2013-08 2013-07 2013-06 2013-05 2013-04 2013-03 2013-02 2013-01 2012-12 2012-11 2014-08 2014-07 2014-06 2014-05 2014-04 2014-03 2014-02 2014-01 2013-12 2013-11 2013-10 2015-07 2015-06 2015-05 2015-04 2015-03 2015-02 2015-01 2014-12 2014-11 2014-10 2014-09 2016-06 2016-05 2016-04 2016-03 2016-02 2016-01 2015-12 2015-11 2015-10 2015-09 2015-08 2017-05 2017-04 2017-03 2017-02 2017-01 2016-12 2016-11 2016-10 2016-09 2016-08 2016-07;
do
impdp \'\/ as sysdba\' parfile=import_sr.par logfile=sr${da}.log query=\" where create_date\> last_day\(add_months\(to_date\(\'$da\',\'yyyy-mm\'\),-1\)\) and create_date \<\=last_day\(to_date\(\'$da\',\'yyyy-mm\'\)\)\"
done

-- 参数文件内容
directory=dump_dir
tables=SR.SR_VOUCHER_FILE_tomig
remap_table=sr.SR_VOUCHER_FILE_tomig:sr_his.sr_voucher_file
transform=segment_attributes:n
network_link=to_aibcrm
table_exists_action=append

3、如何导出数百张表

include=table:"in (select * from &table_name where_clause)" &table_name :+: 在表里存储需要导出的表明细

七、参考地址

文中部分内容出自以下地址，特此注明出处。

http://blog.itpub.net/29785807/viewspace-1593229/

https://www.cnblogs.com/halberd-lee/p/7807032.html

https://www.cnblogs.com/linbo3168/p/6052054.html

https://blog.csdn.net/zhongguomao/article/details/78933333