Oracle数据泵实现数据导入导出

一、导出/导入指定用户下的表带条件的数据

在实际数据库运维中，经常需要针对特定用户下、特定日期范围的数据做迁移或备份。Oracle数据泵（expdp/impdp）提供了一套足够灵活的工具，今天就来拆解一个完整的操作流程——从生成SQL到执行导入，每一步都附带说明和注意事项。

1. 先通过Oracle视图生成可执行的SQL

这一步的核心是利用ALL_TABLES视图拼出动态SQL，方便后续直接复制。以下语句会列出ISS_A用户下所有以'ISS_A'开头的表，并拼出带条件过滤的导出子句：

SELECT 'ISS_A.' || table_name || ':"WHERE DATA_DATE IN (''20250630'',''20250731'')",'FROM ALL_TABLES WHERE OWNER='ISS_A' AND TABLE_NAME LIKE 'ISS_A%';

2. 新建一个par文件

参数文件（par）可以把所有选项集中管理，比直接在命令行敲一长串参数更加清爽。这里有一个关键点：如果脚本里写的是SCHEMAS=ISS_A而不是TABLES=...，那么会导出ISS_A用户下所有表过滤后的数据——注意筛选范围的区别。

vim exp-20250630.par

USERID=ISS_A/123456@127.0.0.1:1521/testdb
DUMPFILE=ISS_A_data_20250630.dmp
LOGFILE=ISS_A_data_20250630.log
TABLES=(ISS_A.ISS_A_ACCT_INFO,ISS_A.ISS_A_ACCT_INFO_BAK,ISS_A.ISS_A_BD_BS_SM_DUTY_INFO,ISS_A.ISS_A_BD_BS_SM_DUTY_INFO_BAK,ISS_A.ISS_A_BD_BS_SM_PUNISH_INFO,ISS_A.ISS_A_BD_BS_SM_PUNISH_INFO_BAK)
CONTENT=DATA_ONLY
QUERY=(ISS_A_ACCT_INFO:"WHERE DATA_DATE IN ('20250630','20250731')",ISS_A_ACCT_INFO_BAK:"WHERE DATA_DATE IN ('20250630','20250731')",ISS_A_BD_BS_SM_DUTY_INFO:"WHERE DATA_DATE IN ('20250630','20250731')",ISS_A_BD_BS_SM_DUTY_INFO_BAK:"WHERE DATA_DATE IN ('20250630','20250731')",ISS_A_BD_BS_SM_PUNISH_INFO:"WHERE DATA_DATE IN ('20250630','20250731')",ISS_A_BD_BS_SM_PUNISH_INFO_BAK:"WHERE DATA_DATE IN ('20250630','20250731')")

3. 使用数据泵执行该par文件

参数文件准备好后，直接交给expdp执行：

[oracle@t-zt-db ~]$ expdp PARFILE=exp-20250630.par

4. 查询导出的行数与数据库中的行数是否一致

导出完成后，先做一次快速校验。以ISS_A_ACCT_INFO表为例，原始数据如下：

SELECT * FROM ISS_A.ISS_A_ACCT_INFO WHERE DATA_DATE IN ('20250630','20250731');

得到33行，与导出文件中的记录数一致，说明导出无误。

5. 使用数据泵导入dmp文件

导入前需要先清空目标表中对应日期的旧数据，避免冲突：

DELETE FROM ISS_A.ISS_A_ACCT_INFO WHERE DATA_DATE IN ('20250630','20250731');
DELETE FROM ISS_A.ISS_A_ACCT_INFO_BAK WHERE DATA_DATE IN ('20250630','20250731');
DELETE FROM ISS_A.ISS_A_BD_BS_SM_DUTY_INFO WHERE DATA_DATE IN ('20250630','20250731');
DELETE FROM ISS_A.ISS_A_BD_BS_SM_DUTY_INFO_BAK WHERE DATA_DATE IN ('20250630','20250731');
DELETE FROM ISS_A.ISS_A_BD_BS_SM_PUNISH_INFO WHERE DATA_DATE IN ('20250630','20250731');
DELETE FROM ISS_A.ISS_A_BD_BS_SM_PUNISH_INFO_BAK WHERE DATA_DATE IN ('20250630','20250731');

然后执行impdp，注意必须加上CONTENT=DATA_ONLY，否则会把表结构也重新导入，造成问题：

impdp ISS_A/123456@127.0.0.1:1521/testdb DUMPFILE=ISS_A_data_20250630.dmp LOGFILE=imp_ISS_A_data_20250630.log CONTENT=DATA_ONLY

6. 导入后查看数据量是否一致

最后再做一次交叉验证，通过生成查询语句对比导入后的行数：

SELECT 'SELECT * FROM ISS_A.' || table_name || ' WHERE DATA_DATE IN (''20250630'',''20250731'');,'FROM ALL_TABLES WHERE OWNER='ISS_A' AND TABLE_NAME LIKE 'ISS_A%';

SELECT * FROM ISS_A.ISS_A_ACCT_INFO WHERE DATA_DATE IN ('20250630','20250731');
SELECT * FROM ISS_A.ISS_A_ACCT_INFO_BAK WHERE DATA_DATE IN ('20250630','20250731');
SELECT * FROM ISS_A.ISS_A_BD_BS_SM_DUTY_INFO WHERE DATA_DATE IN ('20250630','20250731');
SELECT * FROM ISS_A.ISS_A_BD_BS_SM_DUTY_INFO_BAK WHERE DATA_DATE IN ('20250630','20250731');
SELECT * FROM ISS_A.ISS_A_BD_BS_SM_PUNISH_INFO WHERE DATA_DATE IN ('20250630','20250731');
SELECT * FROM ISS_A.ISS_A_BD_BS_SM_PUNISH_INFO_BAK WHERE DATA_DATE IN ('20250630','20250731');

对比两边的结果集，数字对上了，整个流程就完成了。