【Oracle SQL】expdp和impdp导出导入表/表空间

目录

一、 Directory对象

--除了使用network_link参数外，expdp生成的文件都在服务器本地（Directory指定的位置）--如果参数值里没有指定路径，会默认使用directory参数值所指向的路径：DATA_PUMP_DIR--1.1 查看路径select*from dba_directories;--1.2 创建路径指向create directory dump_dir as'/home/oracle';--1.3 文件夹授权给用户，每个Directory都包含read, write两个权限grantread,writeon directory dump_dir to system;

二、 导出/导入数据

2.1 全量导出/导入全库

--导出全库
expdp system/123456@LAURA1 directory=dump_dir dumpfile=expdp.dmp logfile=expdp.log full=y;--导入全库
impdp system/123456@LAURA1 directory=dump_dir dumpfile=expdp.dmp logfile=impdp.log full=y;

2.2 按用户导出/导入

--导出用户
expdp system/123456@LAURA01 directory=dump_dir dumpfile=expdp.dmp logfile=expdp.log schemas=user;--同名导入，A -> A
impdp system/123456@LAURA1 directory=dump_dir dumpfile=expdp.dmp logfile=impdp.log schemas=user;--异名导入，A -> B
impdp system/123456@LAURA1 directory=dump_dir dumpfile=expdp.dmp logfile=impdp.log remap_schema=A:B;

2.3 按表空间导出/导入

--导出表空间
expdp system/123456@LAURA01 directory=dump_dir dumpfile=expdp.dmp logfile=expdp.log tablespace=tbs1;--同名导入，A -> A
impdp system/123456@LAURA1 directory=dump_dir dumpfile=expdp.dmp logfile=impdp.log tablespace=tbs1;--表空间A导入表空间B，用户A导入用户B，生成新的oid防止冲突
impdp system/123456@LAURA1 directory=dump_dir dumpfile=expdp.dmp logfile=impdp.log remap_schema=A:B 
remap_tablespace=TBSA:TBSB transform=oid:n;--impdp 多个表空间到1个表空间通配符的使用，remap_tablespace=%:tbs
impdp system/123456@LAURA1 directory=dump_dir dumpfile=expdp.dmp logfile=impdp.log remap_schema=A:B 
remap_tablespace=%:TBSB transform=oid:n;

2.4 按表导出/导入

--导出表
expdp system/123456@LAURA01 directory=dump_dir dumpfile=expdp.dmp logfile=expdp.log tables=table1;--导入表，用户A导入用户B
impdp system/123456@LAURA1 directory=dump_dir dumpfile=expdp.dmp logfile=impdp.log tables=table1 remap_schema=A:B;

2.5 按查询条件导出

expdp system/123456@LAURA01 directory=dump_dir dumpfile=expdp.dmp logfile=expdp.log tables=table1='where number=1234';

三、 如何调用expdp和impdp

3.1 命令行方式（最简单）

expdp user/pwd

3.2 参数文件方式（推荐）

expdp user/pwd parfile=xxx.par

3.3 交互方式（不推荐）

• 用户执行导入或导出任务，执行到一半，使用Ctrl+C中断了任务（或其他原因导致的中断），此时任务并不是被取消，而是被转移到了后台
• 再次使用expdp/impdp命令，附加attach参数方式重新连接到中断的任务中，并选择后续操作 expdp user/pwd attach=[schema_name].job_name

四、 操作模式

命令expdp help=y/expdp -help查看其参数说明

--4.1 全库模式--导入或者导出整个数据库，只有拥有dba或者exp_full_database和imp_full_database权限的用户才能执行--注意：expdp不能导出sys用户对象。即使是全库导出也不包含sys用户FULL={Y | N}

--4.2 Schema模式--导出或导入Schema下的自有对象，默认的操作模式--如果拥有dba或者 exp_full_database和imp_full_database权限的用户执行的话，就可以导出或导入多个Schema中的对象
SCHEMAS=xxx

--4.3 表模式--导出指定的表或者表分区（如果有分区的话）以及依赖该表的对象（如该表的索引，约束等，不过前提是这些对象在同一个 Schema中，或者执行的用户有相应的权限）TABLES=xxx

--4.4 表空间模式--导出指定的表空间中的内容。这种模式类似于表模式和 Schema模式的补充
TABLESPACES=xxx

--4.5 传输表空间模式--这种模式与前面几种模式最显著的区别是生成的Dump文件中并不包含具体的逻辑数据，而只导出相关对象的元数据(即对象的定义，可以理解成表的创建语句)，逻辑数据仍然在表空间的数据文件中，导出时需要将元数据和数据文件同时复制到目标端服务器。--这种导出方式效率很高，时间开销主要是花在复制数据文件产生的I/O上--expdp执行传输表空间模式的导出，用户必须拥有exp_full_database角色或者DBA角色--而通过传输表空间模式导入时，用户必须拥有imp_full_database角色或者DBA角色
TRANSPORT_TABLESPACES=xxx

--TRANSPORT_FULL_CHECK：检查需要进行传输的表空间与其他不需要传输的表空间之间的信赖关系，默认N
TRANSPORT_FULL_CHECK={Y | N}

--TRANSFORM：设定存储相关的参数，只在导入时使用--假如数据对应的表空间都存在的话，就根本用不到这个参数，但是，假如数据存储的表空间不存在，使用此参数导入到用户默认表空间就可以了。
TRANSFORM=transform_name:value[object_type]
transform_name=[OID | PCTSPACE | SEGMENT_ATTRIBUTES | STORAGE]:[Y | N]

OID：owner_id,如果指定oid=Y(默认)，则在导入过程中将分配一个新的oid给对象表，这个参数我们基本不用管。
PCTSPACE：通过提供一个正数作为该转换的值，可以增加对象的分配尺寸，并且数据文件尺寸等于pctspace的值(按百分比)
SEGMENT_ATTRIBUTES：段属性包括物理属性、存储属性、表空间和日志，Y值按照导出时的存储属性导入，N时按照用户、表的默认属性导入
STORAGE：默认为Y，只取对象的存储属性作为导入作业的一部分

TRANSFORM=SEGMENT_ATTRIBUTES:N --表示将用户所有对象创建到用户默认表空间，而不再考虑原来的存储属性--4.6 过滤数据--过滤数据主要依赖于Query和Sample两个参数。其中Sample参数主要针对expdp导出功能--4.6.1 Query：类似where语句来限定记录（建议把Query参数放入到参数文件中使用，以避免转义符带来的麻烦）--默认情况如果不指定Schema.table_name,则Query_clause针对所有导出的表有效
QUERY=[Schema].[Table_name:] Query_clause

QUERY=A:"Where id < 5",B:"Where name='a'"--4.6.2 Sample：用来指定导出数据的百分比，可指定的值的范围从0.000001到99.999999--Sample_percent指定的值只是一个参考值，EXPDP会根据数据量算出一个近似值
SAMPLE=[Schema].[Table_name:] Sample_percent

SAMPLE=A:50--4.7 计算导出数据大小容量--Oracle可以通过两种方式进行容量估算，一种是通过数据块(blocks)数量、一种是通过统计信息中记录的内容(statistics)估算
ESTIMATE={BLOCKS |STATISTICS}

expdp user/pwd TABLES=emp ESTIMATE=STATISTICS DIRECTORY=dump_dir DUMPFILE=halberd.dmp

--4.8 统计导出的数据量大小及统计过程耗时长短
ESTIMATE_ONLY={Y | N}

expdp user/pwd ESTIMATE_ONLY=Y NOLOGFILE=Y DIRECTORY=dump_dir SCHEMAS=scott

五、 过滤对象

--过滤对象主要依赖于Include和Exclude两个参数。在这两个参数中，可以指定你知道的任何对象类型（如：Package、Procedure、Table等等）或者对象名称（支持通配符）--5.1 Exclude反规则（建议把Exclude参数放入到参数文件中使用，以避免转义符带来的麻烦）--指定不被包含的对象类型或者对象名称。指定了该参数以后，指定的对象类型对应的所有对象都不会被导入或导出。如果被排除的对象有依赖的对象，那么其依赖的对象也不会被导入或导出。--如：通过Exclude参数指定不导出表对象的话，不仅指定的表不会被导出，连这些表关联的Index、Check等都不会被导出。
EXCLUDE=[object_type]:[name_clause],[object_type]:[name_clause]--排出特定对象

expdp <other_parameter> SCHEMAS=scott EXCLUDE=SEQUENCE,TABLE:"IN('EMP','DEPT')"--5.2 Include正规则--与Exclude正好相反。指定包含的对象类型或者对象名称。--由于两个参数功能正好相反，因此在执行导入或导出命令时，两个参数不能同时使用，否则Oracle也不知道你想要干什么
INCLUDE=[object_type]:[name_clause],[object_type]:[name_clause]--包含特定对象

impdp <other_parameter> SCHEMAS=scott INCLUDE=PACKAGE,FUNCTION,PROCEDURE,TABLE:"='EMP'"

INCLUDE=PROCEDURE:"LIKE 'PROC_U%'"--包含以PROC_U开头的所有存储过程（_符号代表任意单个字符）
INCLUDE=TABLE:"> 'E'"--包含大于字符E的所有表对象--Windows平台：需要对象双引号进行转义，使用转义符\
D:\>expdp system/manager DIRECTORY=my_dir DUMPFILE=exp_tab.dmp LOGFILE=exp_tab.log SCHEMAS=scott INCLUDE=TABLE:\"IN('EMP','DEPT')\"

--Unix平台：在未使用parfile文件的情形下，所有的符号都需要进行转义，包括括号，双引号，单引号等
expdp system/manger DIRECTORY=my_dir DUMPFILE=exp_tab.dmp LOGFILE=exp_tab.log SCHEMAS=scott INCLUDE=TABLE:\"IN \(\'EMP\',\'DEPT\'\)\"

--exclude/include常见的错误
任意需要转义的字符如果未转义或转义错误，都会产生ORA错误。下面给出几种常见的ORA错误
ORA-39001: invalid argument value
ORA-39071: Valuefor INCLUDE is badly formed.
ORA-00936: missing expression
ORA-39071: Valuefor EXCLUDE is badly formed.
ORA-00904: "DEPT": invalid identifier
ORA-39041: Filter "INCLUDE" either identifies all object typesorno object types.
ORA-39041: Filter "EXCLUDE" either identifies all object typesorno object types
ORA-39038: Object path “USER” isnot supported forTABLE jobs.

六、 高级过滤

--只想导出/导入表结构，或者只想导出/导入数据
CONTENT={ALL| DATA_ONLY | METADATA_ONLY}

ALL : 导出/导入对象定义和数据，该参数的默认值就是ALL
DATA_ONLY : 只导出/导入数据
METADATA_ONLY : 只导出/导入对象定义

--在执行导出的时候，如果使用了高级过滤，如只导出了数据，那么导入时，需要确保数据定义已经存在。--如果数据定义已经存在，导入时最好指定data_only，否则会触发ORA-39151错误，因为对象已经存在了。--6.1 过滤已经存在的数据--导入的表对象在目标库中已经存在，并且目标端没有创建数据完整性约束条件(RI)来检验数据的话，就有可能造成数据被重复导入
TABLE_EXISTS_ACTION={SKIP | APPEND |TRUNCATE|REPLACE}

SKIP : 跳过该表，继续处理下一个对象。该参数默认就是SKIP。值得注意的是，如果你同时指定了CONTENT参数为Data_only的话，SKIP参数无效，默认为APPEND。
APPEND : 向现有的表中添加数据。
TRUNCATE : TRUNCATE当前表，然后再添加记录。使用这个参数需要谨慎，除非确认当前表中的数据确实无用。否则可能造成数据丢失。
REPLACE : 删除（drop）并重建表对象，然后再向其中添加数据。值得注意的是，如果同时指定了CONTENT参数为Data_only的话，REPLACE参数无效。

--6.2 重定义表的Schema或表空间--把A用户的对象转移到B用户，或者更换数据的表空间--6.2.1 REMAP_SCHEMA : 重定义对象所属Schema，只在导入时使用--不能在同一个IMPDP命令中指定remap_schema=a:b,a:c
REMAP_SCHEMA=Source_schema:Target_schema[,Source_schema:Target_schema]--6.2.2 REMAP_TABLESPACE：重定义对象所在的表空间，只在导入时使用--如果使用Remap_tablespace参数，则要保证导入的用户对目标表空间有读写权限
REMAP_TABLESPACE=Source_tablespace:Target_tablespace[,Source_tablespace:Target_tablespace]--6.3 优化导入/导出效率--6.3.1 对于导出的parallel--对于导出来说，由于dump文件只能由一个线程进行操作（包括I/O处理），因此如果输出的DUMP文件只有一个，即使指定再多的并行，实际工作仍然是一个，而且还会触发ORA-39095错误。--因此，建议设置该参数小于或等于生成的DUMP文件数量。--如何控制生成的DUMP文件数量呢--EXPDP命令提供了一个FILESIZE参数，用来指定单个DUMP文件的最大容量，要有效的利用parallel参数，filesize参数必不可少--filesize：用于指定单个导出的数据文件的最大值，与％U一起使用
FILESIZE=interger[B | K | M | G]

expdp user/pwd directory=dump_file dumpfile=expdp_20100820_%U.dmp logfile=expdp_20100820.log filesize=500M parallel=4--6.3.2 对于导入的parallel--对于导入来说，使用parallel参数则要简单的多，导入更能体现parallel参数的优势。 参数设置为几，则认为同时将几张表的内容导入到库中。
impdp user/pwd directory=dump_file dumpfile=expdp_20100820_%U.dmp logfile=impdp_20100820.log parallel=10--6.4 基于undo及scn号(时间点)进行的数据导出
FLASHBACK_SCN=scn_value
FLASHBACK_TIME={to_timestamp(localtimestamp)| to_timestamp_tz(systimestamp)|"TO_TIMESTAMP(""2022-10-11 13:55:00"", ""YYYY-MM-DD HH24:MI:SS"")"} --有多种设定值的格式

expdp user/pwd DIRECTORY=dump_dir DUMPFILE=halberd.dmp FLASHBACK_SCN=123456789
expdp user/pwd DIRECTORY=dump_dir DUMPFILE=halberd.dmp FLASHBACK_TIME=to_timestamp(localtimestamp)--6.5 NETWORK_LINK--可通过本地数据库里的db_link连接到其他数据库A，将数据库A的数据直接导入到本地数据库。中间可节省导出数据文件，传送数据文件的过程。--注意：不同版本之间可能会存在问题，比如源库为10g,目标库为11g。使用network_link参数会报错
NETWORK_LINK=[db_link]

impdp user/pwd DIRECTORY=dump_dir DUMPFILE=halberd.dmp NETWORK_LINK=to_tjj SCHEMAS=halberd LOGFILE=halberd.log

--6.6 NOLOGFILE：不写入导入导出日志（不推荐），默认写入
NOLOGFILE={Y | N}

--6.7 VERSION：主要在跨版本之间进行导数据时使用--在从高版本数据库导入到低版本数据库时使用，从低版本导入到高版本，这个参数是不可用的。默认值是:compatible。此参数基本在导出时使用，导入时基本不可用。
VERSION={COMPATIBLE | LATEST | version_string}

COMPATIBLE：以参数COMPATIBLE的值为准，可以通过show parameter查看
LATEST：以数据库版本为准
version_string：指定版本。如：version=10.2.0.1--6.8 SQLFILE：主要是将DMP文件中的metadata语句取出到一个单独的SQLfile中，而数据并不导入到数据库中，只在导入时使用
SQLFILE=&file_name.sql

impdp user/pwd DIRECTORY=dump_dir DUMPFILE=halberd.dmp LOGFILE=halberd.log SQLFILE=halberd.sql--6.9 CONSISTENT：保持数据一致性--在11g中使用时，如果设置 consistent=true,则会默认转换成 flashback_time参数，时间设置为命令开始执行的那个时间点CONSISTENT={TRUE|FALSE}

--6.10 REUSE_DUMPFILES：重用导出的dmp文件--假如第一次我们导失败了，虽然导出失败，但是dmp文件 还 是会生成的。在修改导出命令，第二次执行时，就可以加上这个参数
REUSE_DUMPFILE={TRUE|FALSE}

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

七、 使用技巧

7.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 user/pwd parfile=test.par

7.2 通过shell脚本自动导入

cat import_sr.sh
#!/bin/shcd /u01/app
fordain2012-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 user/pwd 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

7.3 如何导出数百张表

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