数据泵 expdp/impdp常用命令详解（超详细）

一.简介

数据泵属于逻辑备份 逻辑备份仅关注数据部分，一般作为物理备份的辅助工具；

逻辑备份，可以有很宽松的备份级别：
表级别
模式级别(用户级别的)
表空间级别(数据泵可以)
数据库级别(将整个数据库迁移)

逻辑备份的两种工具：

导入/导出： imp/exp --原始导入导出工具

数据泵： impdp/expdp – oracle 10g之后的工具 --是一种高效的数据和元数据的迁移工具

二.准备工作

使用数据泵需要创建directory目录

-- 查看所有directory目录
col directory_name for a25
col DIRECTORY_PATH for a80
select directory_name,directory_path from dba_directories;

-- 创建directory目录
mkdir /oradata/all_backup
sqlplus中创建并授权给sys用户
create directory expdp_dir as '/oradata/all_backup';
grant read,write on directory expdp_dir to sys;

三.常用参数

compression= 压缩

ALL ：			对导出的元数据和表数据都进行压缩，得到的导出文件是最小的，耗时也是最长的。
DATA_ONLY：		仅对表数据进行压缩，对于大数据量的导出效果明显，会比METADATA_ONLY方式得到更小的压缩文件。
METADATA_ONLY：	仅对元数据进行压缩，而不会对表数据进行压缩，这种压缩执行后效果一般不是很明显，不过速度比较快。
NONE：			不进行任何的压缩，导出后的文件也是最大的。
DEFAULT：		默认方式，即不指定COMPRESSION参数，会采用默认的压缩方式METADATA_ONLY。

content= 用来指定导入导出的数据范围 content=

all：			默认数据和元数据全部导出或者导入
data_only：		只导数据部分
metadata_only：	只导元数据

table_exists_action= 导入数据时使用
– 导入时如果表结构存在的情况下：
– 此参数包含四个选项：skip ,append, truncate, replace

skip：			默认值，忽略所有的导入，既不导入元数据，也不导入数据
append：		追加到表中需要导入的数据，但不能违反表的约束限制
truncate：		将原来的数据truncate，然后导入外部dumpfile的数据
replace		先删除原有表，用dumpfile的数据，替换原表数据

数据库的性能参数

parallel=		并发  具体并发数根据cpu来决定,使用并发时dumpfile导出文件要加后缀%u   

其他常用参数

exclude=		用于排除导入/导出某些对象
include=		用于指定导入/导出某些对象
query=			带条件导出，用于导出指定条件数据

四.具体操作

在我们日常工作中，使用数据泵的场景一般都是迁移部分表到测试环境，迁移某个用户下的全部数据到测试环境，迁移元数据到其他环境，或者通过数据泵将数据库迁移到高版本等等。下面讲解实际工作中常用的多个场景的具体操作

1.生产环境test用户下的tmp1,tmp2两张表同步到测试环境

源端操作：
先查看转储目录，如果没有合适的转储目录需要手动创建并授权，具体步骤上面有写过，此处省略. 
本次测试源段目录名统一为 expdp_dir，目标端统一为impdp_dir

expdp导出对应表
expdp \"/as sysdba\" directory=expdp_dir dumpfile=tables_20240301.dmp tables=test.tmp1,test.tmp2 logfile=tables_20230628.log
也可以通过test用户导出
expdp test/password directory=expdp_dir dumpfile=tables_20240301.dmp tables=tmp1,tmp2 logfile=tables_20230628.log

目标端操作：
执行导入，如果有同名的表那么直接replace（删除原有表，用dumpfile的数据，替换原表数据）。如果没有同名的表可以不用加这个参数
impdp \"/as sysdba\" directory=impdp_dir dumpfile=tables_20240301.dmp logfile=tables_20240301.log table_exists_action=replace

2.生产环境test用户下的tmp1,tmp2两张表的create_time大于2024-01-01的数据同步到测试环境

源端操作：
单表的话可以直接执行  使用query参数对表的数据进行过滤
expdp \"/as sysdba\" directory=expdp_dir dumpfile=tables_20240301.dmp tables=test.tmp1 query=test.tmp1:"WHERE create_time>=to_date('2024-01-01','yyyy-mm-dd')" logfile=tables_20240301.log

如果要导出多张表的时候需要用到参数文件
vim tables.par

dumpfile=tables_20240301.dmp
directory=expdp_dir
logfile=tables_20230628.log
tables=(test.tmp1,
		test.tmp2)
query=(test.tmp1:"WHERE create_time>=to_date('2024-01-01','yyyy-mm-dd')",
	   test.tmp2:"WHERE create_time>=to_date('2024-01-01','yyyy-mm-dd')")

expdp \"/as sysdba\" parfile=tables.par 

目标端操作：
impdp \"/as sysdba\" directory=impdp_dir dumpfile=tables_20240301.dmp logfile=tables_20240301.log table_exists_action=replace

3.生产环境test用户所有数据迁移到测试环境

源端操作：
通过schemas参数导出test用户数据  parallel加并发
expdp \"/as sysdba\" directory=expdp_dir dumpfile=test_20240301_%u.dmp schemas=test parallel=6 logfile=test_20240301.log

目标端操作：
impdp \"/as sysdba\" directory=impdp_dir dumpfile=test_20240301_%u.dmp parallel=6 logfile=test_20240301.log

4.生产环境test用户所有元数据迁移到测试环境

源端操作：
通过schemas参数导出test用户数据  content=metadata_only导出元数据（表结构，存储过程等等）
expdp \"/as sysdba\" directory=expdp_dir dumpfile=test_20240301.dmp schemas=test content=metadata_only logfile=test_20240301.log

目标端操作：
impdp \"/as sysdba\" directory=impdp_dir dumpfile=test_20240301.dmp logfile=test_20240301.log

5.通过数据泵进行数据库迁移

生产环境通过expdp迁移要经过多次测试，记录好迁移测试用时。测试确认没有问题才可以进行正式迁移，特别是复杂的业务系统
迁移前首先要统计好源端的所有表空间情况，提前在目标端创建好所有表空间，添加好足够的数据文件
要统计源端所有业务用户，与客户沟通具体哪些用户需要进行迁移 然后统计源端所有对象信息，有多少张表、多少个存储过程、dblink等等，做好记录
最后统计源端所有失效对象，要确保迁移后的失效对象少于源端，没有新多出的失效对象。

迁移前关闭监听，关闭job  防止有数据写入
lsnrctl stop
alter system set job_queue_processes=0;

导出所有业务用户数据，排除统计信息（统计信息可以在目标端导入完成后手动收集）
expdp \"/as sysdba\" directory=expdp_dir dumpfile=allbak_%u.dmp schemas=test,test2,test3 parallel=8 exclude=statistics logfile=allbak.log 

导入数据
impdp \"/as sysdba\" directory=impdp_dir dumpfile=allbak_%u.dmp parallel=8 logfile=allbak.log

五.停止正在运行的expdp/impdp任务

如果任务在前台运行时，ctrl+c会中断。如果在后台运行时，通过 ps -ef | grep expdp找出进程号进行kill -9后任务会中断。中断后其实程序并未完全停止，在数据库中还可以查到任务的信息 下面是两个彻底终止任务的方法

查看正在运行的导入导出任务
SELECT owner_name, job_name, operation, job_mode, state, attached_sessions
  FROM dba_datapump_jobs
 WHERE job_name NOT LIKE 'BIN$%'
 ORDER BY 1, 2;
 
SELECT o.status,
       o.object_id,
       o.object_type,
       o.owner || '.' || object_name "OWNER.OBJECT"
  FROM dba_objects o, dba_datapump_jobs j
 WHERE o.owner = j.owner_name
   AND o.object_name = j.job_name
   AND j.job_name NOT LIKE 'BIN$%' 
 ORDER BY 4, 2;


impdp \"/as sysdba\"  attach=SYS_IMPORT_FULL_01
关掉job
stop_job=immediate
删除job
kill_job

或者直接删掉源表
drop table sys.sys_import_schema_01;