Oracle expdp impdp 参数详解 示例

 Oracle expdp impdp 参数详解 示例

1 导数据注意事项

  • 检查数据库版本（用于决定导出时生成为哪个版本的dmp头文件） select version from v$instance; 也可以用sqlplus -v 查看。
  • 检查字符集是否一致（字符集不一致，不能导入） select userenv('language') from dual;
  • 检查数据量及磁盘空间（决定采取什么样的方式导出及导入）

2 前期准备

  • 创建目录

create directory DUMP_DIR as '&PATH';

  • 查看目录 

select * from dba_directories;

  • 授权其他用户使用该目录 

grant read,write on directory dpdata1 to scott;

3 常用参数及示例

  • 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

4 常用语句示例

  • expdp导出
1)导出表
expdp  tables=dbmon.lihaibo_exp dumpfile=sms.dmp DIRECTORY=dump_dir;
2)并发导出parallel，指定job名
我们需要特别注意一点，parallel 一定要与 dumpfile=...%U.dmp结合 使用，或者有多个表需要同时导出。单表，或者其他诸如network_link方式，指定parallel,也无法开启并发进程
expdp scott/tiger@orcl directory=dpdata1 dumpfile=scott3%U.dmp parallel=4 job_name=scott3
3)全表
expdp scott/tiger@orcl TABLES=emp,dept dumpfile=expdp.dmp DIRECTORY=dpdata1;
4)导出表，并指定表中的内容
expdp scott/tiger@orcl directory=dpdata1 dumpfile=expdp.dmp Tables=emp query="WHERE deptno=20";
5)导出表空间
expdp system/manager DIRECTORY=dpdata1 DUMPFILE=tablespace.dmp TABLESPACES=temp,example;
6)导出全库
expdp system/manager DIRECTORY=dpdata1 DUMPFILE=full.dmp FULL=y;
  • impdp导入
1) 全用户导入
impdp scott/tiger DIRECTORY=dpdata1 DUMPFILE=expdp.dmp SCHEMAS=scott;
2) 用户对象迁移
impdp system/manager DIRECTORY=dump_dir DUMPFILE=expdp.dmp TABLES=scott.dept REMAP_SCHEMA=scott:system; （SCOTT为原用户，system为目标用户）
3) 导入指定表空间
impdp system/manager DIRECTORY=dump_dir DUMPFILE=tablespace.dmp TABLESPACES=example;
4) 全库导入
impdb system/manager DIRECTORY=dump_dir DUMPFILE=full.dmp FULL=y;
5) 表已存在的处理
impdp system/manager DIRECTORY=dump_dir DUMPFILE=expdp.dmp SCHEMAS=system TABLE_EXISTS_ACTION=append;
6) 表空间迁移
impdp system/manager directory=dump_dir dumpfile=remap_tablespace.dmp logfile=remap_tablespace.log remap_tablespace=A:B (A为原表空间名，B为指定的目标表空间名)

5 交互式命令

  1. 连接到对应的job impdp \'\/ as sysdba\' attach=&job_name 不知道job_name 去哪儿找？看上面的参数:job_name
  2. 查看运行状态： status
  3. 停止导入导出： kill_job(直接kill 掉进程，不自动退出交互模式)
  4. 停止导入导出：stop_job（逐一停止job进程的运行，并退出交互模式）
  5. 修改并发值： parallel
  6. 退出交互模式： exit / exit_client(退出到日志模式，对job无影响)

6 技巧

 

6.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

6.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

6.3 如何导出数百张表

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

Author: Halberd E-mail: halberd.lee@gmail.com Tel:18258160531

Created: 2017-11-09 Thu 00:18

Emacs 26.0.50.1 (Org mode 8.2.10)

Validate

posted @ 2017-11-09 00:20  halberd.lee  阅读(15843)  评论(1)  编辑  收藏  举报