【Oracle篇】Oracle数据泵-impdp和expdp
引言
最近做数据库迁移的需求，趁着机会将相关使用的方法和技巧做一下梳理。
数据库类型：Oracle
使用技术：数据泵
方法步骤
说明：以下使用到的目录，用户等按照自己的修改。
- linux 服务器中切换到 oracle 用户:
su -oracle
- 创建一个数据泵文件夹
mkdir -p /sams/backup/sams_db/
- 管理员登录 Oracle 数据库
sqlplus system/sys as sysdba
- 创建数据泵
# 语法： create directory 数据泵名称 as 数据泵文件路径
create directory samsdb_dir as /sams/backup/sams_db/;
后期使用到的dmp都会放在这个目录下。
- 将数据泵授权给其他用户
# 语法：grant read,write on directory 数据泵名称 to 数据库用户名
grant read,write on directory samsdb_dir to sams;
通过以上的步骤，就可以使用授权的用户进行导入和导出操作。
导出: expdp
定义变量：
# 数据库用户名/密码
SAMS_DBURL=SAMS/Sump_2020
- 示例一: 导出单表
expdp ${SAMS_DBURL) directory=samsdb_dir dumpfile=standing_book_test01.dmp TABLES=sams.standing_book
解析: 这条语句将导出表sams.standing_book。在directory目录下生成名为standing_book_test01.dmp的文件。
问题分析: 使用这种方法生成的文件会很大，可以加上compression=all进行压缩。语句不能重复执行，如果可重复执行需要加上statistics=none，重复执行时原文件会被覆盖。在rac集群模式下需要配置cluster=N，指定只在当前机器导出。优化如下:
expdp $(SAMS_DBURL} directory=samsdb_dir compression=all statistics=none cluster=N dumpfile=standing_book_test01.dmp TABLES=sams.standing_book
- 示例二: 并发导出
expdp ${SAMS_DBURL} directory=samsdb_dir compression=all parallel=4 dumpfile=expdp_all_%U.dmp
解析: parallel=4表示开启了4个进程，将提高导出效率。parallel一定要与dumpfile=…%U.dmp结合使用，或者有多个表需要同时导出，单表，或者诸如network_1ink方式，即使指定parallel也无法开启并发进程。
另外，这里没有指定TABLES，会将全表导出，需要指定导出多张表时加上TABLES=table1,table2…
- 示例三: 导出特定对象
expdp ${SAMS_DBURL} directory=samsdb_dir compression=all cluster=N dumpfile=standing_book_test02.dmp statistics=none INCLUDE=SEQUENCE,FUNCTION,PROCEDURE,VIEW,TABLE:\"IN\(\'STANDING_BOOK\','FPRI_BUTNM\'\)\"
解析: 通过INCLUDE指定导出了SEQUENCE,FUNCTION,PROCEDURE,VIEW,TABLE等对象，其中TABLE只有两张表。也可以使用查询的形式，将需要导出的表名写到单独一张表中，比如DBBACKUP_TABLES表：
expdp ${SAMS_DBURL} directory=samsdb_dir compression=all cluster=N dumpfile=standing_book_test03.dmp statistics=none INCLUDE=SEQUENCE,FUNCTION,PROCEDURE,VIEW,TABLE:\"IN\(SELECT TABLES_ENAME FROM DBBACKUP_TABLES\)\"
易错点: 注意转义符的书写。
- 示例四: 导出表，并指定表中的内容
expdp ${SAMS_DBURL} directory=samsdb_dir compression=all statistics=none cluster=N dumpfile=fpri_butnm.dmp TABLES=sams.fpri_butnm query=\"where resource_id\>10000\"
解析: query参数指定在导入导出时的限制条件，和SQL语句中的"where"是一样的。这里只导出resource_id大于10000的数据。
易错点: 注意转义符的书写。
- 示例五: 导出表空间
expdp ${SAMS_DBURL} directory=samsdb_dir compression=all statistics=none dumpfile=tablespace.dmp tablespaces=system
解析: 导出了特定表空间system。
- 示例六: 导出全库
expdp ${SAMS_DBURL} directory=samsdb_dir compression=all statistics=none dumpfile=full.dmp full=y
解析：full=y 将整个数据库导出。
- 示例七: par文件使用
上面的几个示例都是将参数写到了命令行中，其实还可以将他们写入一个xx.par的文件中，更加方便，也不会有转义符的问题。
# expdp.par
directory=samsdb_dir
compression=all
statistics=none
cluster=N
dumpfile=fpri_butnm01.dmp
TABLES=(sams.fpri_butnm)
query=(sams.fpri_butnm:"where resource_id>100000")
# 导出命令
expdp ${SAMS_DBURL} parfile=expdp.par
说明: parfile指定par文件。如果有张表或者多个条件，使用","隔开。其他参数都可以往里面加。
导入: impdp
说明：expdp.dmp原本属于sams。
- 示例一: 全用户导入
impdp ${SAMS_DBURL} directory=samsdb_dir dumpfile=expdp.dmp schemas=sams
解析: schemas指定用户。将dmp文件中的sams用户下的对象进行导入。
- 示例二: 用户对象迁移
impdp ${SAMS_DBURL} directory=samsdb_dir dumpfile=expdp.dmp tables=fpri_butnm remap_schema=sams:samswf table_exists_action=replace
解析: 将dmp文件中的fpri_butnm表进行迁移，remap_schema指定用户，sams为原用户，samswf为目标用户，如果表存在，会替换。
- 示例三: 导入指定表空间
impdp ${SAMS_DBURL} directory=samsdb_dir dumpfile=expdp.dmp tablespaces=system
解析: 将dmp文件中的数据导入到表空间system中。
- 示例四: 全库导入
impdp ${SAMS_DBURL} directory=samsdb_dir dumpfile=expdp.dmp full=y
- 示例五: 表空间迁移
impdp ${SAMS DBURL} directory=samsdb_dir dumpfile=expdp.dmp remap tablespace=sams:system
解析: 将dmp文件中的sams表空间迁移到system表空间。
说明：对于导入，同样可以使用xx.par文件的形式，同时支持query直接从dmp文件中查询数据进行导入。
其他常用参数说明
logfile: 指定日志文件。
filesize: 指定单个文件的最大值，与dumpfile=…%U.dmp结合使用。
content: 限制导出/导入的内容，包括三个级别:全部(ALL)、数据(DATA_ONLY)、元数据(METADATA_ONLE)，默认是ALL。元数据就是表结构，创建语句。
version: 此参数主要在跨版本之间进行导数据时使用，一般从高版本向低版本导出数据时使用。
sqlfile: 此参数将dmp文件中的metadata语句取出到一个单独的sq1文件中，而不是导入到数据库中。
schemas: 指定导出/导入哪个用户。其实是用户的对象。
table_exists_action: 在导入时，如果表存在的处理方法:追加(append)、删除数据(truncate)、跳过(skip)、替换(replace，也就是先删除表，在创建表)追加的时候需要考虑外键问题。
exclude:排除不需要导出的内容。