江小南

V1

2022/10/21阅读:24主题:默认主题

【Oracle篇】Oracle数据泵-impdp和expdp

引言

最近做数据库迁移的需求,趁着机会将相关使用的方法和技巧做一下梳理。

数据库类型:Oracle

使用技术:数据泵

方法步骤

说明:以下使用到的目录,用户等按照自己的修改。

  1. linux 服务器中切换到 oracle 用户:
su -oracle
  1. 创建一个数据泵文件夹
mkdir -p /sams/backup/sams_db/
  1. 管理员登录 Oracle 数据库
sqlplus system/sys as sysdba
  1. 创建数据泵
# 语法: create directory 数据泵名称 as 数据泵文件路径
create directory samsdb_dir as /sams/backup/sams_db/;

后期使用到的dmp都会放在这个目录下。

  1. 将数据泵授权给其他用户
# 语法: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:排除不需要导出的内容。

分类:

后端

标签:

数据库

作者介绍

江小南
V1