exp imp 详解 include 不通用 content DATA_ONLY

jnrjian

已于 2023-12-16 11:17:15 修改

阅读量208

点赞数

文章标签： sql 数据库

于 2023-12-16 10:35:36 首次发布

本文链接：https://blog.csdn.net/jnrjian/article/details/135029596

版权

directory=DUMP_mig dumpfile=cas_mig_core_jf.dmp

remap_schema=

include=DATA_ONLY

table_exists_action=truncate

cluster=n

parallel=8

INCLUDE=TABLE:"IN('','')"

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
ORA-39001: invalid argument value
ORA-39038: Object path "DATA_ONLY" is not supported for FULL jobs.

在使用数据泵导入时，为了加快导入速度先是将表上索引禁用：

alter index SYS_C0061367 unusable;
alter table my_test disable constraint SYS_C0061367;
alter index idx_mt_y unusable;

然后使用数据泵导入：

impdp \'/ as sysdba\' directory=foss_dump dumpfile=my_test01.dmp tables=iawr.my_test include=table_data table_exists_action=truncate
impdp \'/ as sysdba\' directory=foss_dump dumpfile=my_test01.dmp tables=iawr.my_test content=data_only table_exists_action=truncate

最后并行重建索引：

alter index idx_mt_y rebuild online compute statistics parallel 4;
alter index idx_mt_y noparallel;

alter table my_test enable constraint SYS_C0061367;
alter index SYS_C0061367 rebuild online compute statistics parallel 4;
alter index SYS_C0061367 noparallel;

今天在操作期间，发现使用content=data_only时，索引最后都自动被重建
而使用include=data_only时，索引一直处于unusable状态，这样看这两个
选项还是有些区别的

经常使用参数及示例

ATTACH

做用
    当咱们使用ctrl+C 退出交互式命令时，可心使用attach参数从新进入到交互模式
语法
    ATTACH=[schema_name.]job_name
    Schema_name用户名,job_name任务名
示例
    Expdp scott/tiger ATTACH=scott.export_job-----ctrl+C 不代表停止 要打 stop 才会停止

JOB_NAME

做用
    指定任务名，若是不指定的话，系统会默认自动命名：SYS_EXPORT_mode_nn
语法
    JOB_NAME=&JOB_NAME
其余
    查看有哪些expdp/impdp job，能够经过dba_datapump_jobs查看，其实你经过v$session.action也能够查看到
    大多与attach参数一块儿使用，从新进行expdp交互命令时使用。

CONTENT

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

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 所使用的用户有访问的权限。

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给对象表，这个参数咱们基本不用管。---type 类型
    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 交互式命令

链接到对应的job impdp \'\/ as sysdba\' attach=&job_name 不知道job_name 去哪儿找？看上面的参数:job_name
查看运行状态： status
中止导入导出： kill_job(直接kill 掉进程，不自动退出交互模式)
中止导入导出：stop_job（逐一中止job进程的运行，并退出交互模式）
修改并发值： parallel
退出交互模式： 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脚本中执行，须要转义的地方。session

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