实战篇：LogMiner 分析数据泵导入参数 TABLE_EXISTS_ACTION 的秘密

目录

• 前言
• 环境准备

• 1、安装 LogMiner
• 2、创建数据字典文件
• 3、添加在线重做日志
• 4、启动 LogMiner
• 5、准备数据泵导入数据
• 6、查询 LogMiner 记录

• 开始实验

• 1、SKIP
• 2、APPEND
• 3、TRUNCATE
• 4、REPLACE

• 总结

前言

前几天，技术交流群里看到大家讨论 Oracle 数据泵导入时使用 table_exists_action 参数，存在一些疑惑。于是，我打算通过 LogMiner 来分析一下在线重做日志，看看到底是怎么玩的。

• 关于 LogMiner 的官方文档：Using LogMiner to Analyze Redo Log Files
• 关于 TABLE_EXISTS_ACTION 的官方文档：TABLE_EXISTS_ACTION
  

建议先阅读简单了解一下，下面👇🏻就开始~ ヾ(◍°∇°◍)ﾉﾞ

环境准备

已有 Oracle 11GR2 数据库环境，已开启归档模式。

1、安装 LogMiner

Oracle 自带的 sql 脚本与 LogMiner 相关的有以下三个：

在默认情况下，Oracle已经安装了LogMiner工具，如果没有安装，可以依次执行以下 sql 脚本，创建 LogMiner 相关的对象：

sqlplus/assysdba@?/rdbms/admin/dbmslm.sqlsqlplus/assysdba@?/rdbms/admin/dbmslmd.sql

脚本需要用 SYS 用户执行，可重复执行。

2、创建数据字典文件

DBMS_LOGMNR_D.BUILD 过程需要访问可以放置字典文件的目录。 因为 PL/SQL 过程通常不访问用户目录，所以必须指定一个目录供 DBMS_LOGMNR_D.BUILD 过程使用，否则该过程将失败。

mkdir/oradata/orcl/logmnrsqlplus/assysdbaCREATEDIRECTORYutlfileAS'/oradata/orcl/logmnr';
altersystemsetutl_file_dir='/oradata/orcl/logmnr'scope=spfile;
shutdownimmediate;
startup;

要指定目录，需要在初始化参数文件中设置初始化参数 UTL_FILE_DIR，需要重启数据库生效参数。

执行 PL/SQL 过程 DBMS_LOGMNR_D.BUILD。 指定字典的文件名和文件的目录路径名。 此过程创建字典文件。 例如，输入以下内容在 /oradata/orcl/logmnr 中创建文件 dictionary.ora：

EXECUTEDBMS_LOGMNR_D.BUILD('dictionary.ora','/oradata/orcl/logmnr',DBMS_LOGMNR_D.STORE_IN_FLAT_FILE);
•1

由于本次仅作实验所用，不单独创建用户和表空间。

3、添加在线重做日志

通过 LogMiner.ADD_LOGFILE 添加所有 REDO LOG ：

--查询所有在线重做日志selectmemberfromv$logfile;
--添加所有在线重做日志BEGINDBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME=>'/oradata/orcl/redo03.log',OPTIONS=>DBMS_LOGMNR.NEW);
DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME=>'/oradata/orcl/redo01.log',OPTIONS=>DBMS_LOGMNR.ADDFILE);
DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME=>'/oradata/orcl/redo02.log',OPTIONS=>DBMS_LOGMNR.ADDFILE);
end;

添加第一个文件时，OPTIONS 需要指定 DBMS_LOGMNR.NEW，后面添加的文件指定 DBMS_LOGMNR.ADDFILE。

4、启动 LogMiner

beginDBMS_LOGMNR.START_LOGMNR(DictFileName=>'/oradata/orcl/logmnr/dictionary.ora');
end;

这里需要注意的是，执行启动 LogMiner 的 SESSION 才可以进行查询，否则不能查询。

5、准备数据泵导入数据

创建用户和测试表：

createuserteaidentifiedbytea;
grantdbatotea;
conntea/teacreatetabletea (idnumber,textvarchar2(20));
insertintoteavalues (1,'test1');
insertintoteavalues (2,'test2');
commit;

数据泵导出表：

expdpsystem/oracledirectory=DATA_PUMP_DIRdumpfile=tea.dmplogfile=tea.logtables=tea.tea

6、查询 LogMiner 记录

altersessionsetNLS_DATE_FORMAT='YYYY-MM-DD HH24:mi:ss';
SELECTtimestamp, sql_redo, sql_undo, seg_ownerFROMv$logmnr_contentsWHEREseg_name='TEA'ANDseg_owner='TEA';

通过查询可以看到上面建表的 DDL 语句已经被查询到。

开始实验

数据泵导入参数 TABLE_EXISTS_ACTION，通常用于数据库中表已存在的情况下，导入数据时处理的参数。

TABLE_EXISTS_ACTION=[SKIP | APPEND | TRUNCATE | REPLACE]

可用选项有 4 种，接下来我们依次使用参数来进行测试。

• SKIP：跳过当前表进行下一个。 如果 CONTENT 参数设置为 DATA_ONLY，这不是有效选项。
• APPEND：从源加载数据并保持现有数据不变。
• TRUNCATE：删除现有表数据，然后从源加载数据。
• REPLACE：删除现有表，然后从源创建并加载数据。 如果 CONTENT 参数设置为 DATA_ONLY，这不是有效选项。

默认值：SKIP（注意，如果指定了 CONTENT=DATA_ONLY，则默认值是 APPEND，而不是 SKIP）

1、SKIP

SKIP 参数是指导入时跳过已存在的表，添加参数 TABLE_EXISTS_ACTION=SKIP 测试。

执行导入：

impdpsystem/oracledirectory=DATA_PUMP_DIRdumpfile=tea.dmplogfile=tea.logtables=tea.teatable_exists_action=skip

这个参数比较简单明了，就是直接跳过了存在的表，不进行导入，表数据不变。

2、APPEND

APPEND 参数是指导入时对已存在表进行增量导入，添加参数TABLE_EXISTS_ACTION=APPEND 测试。

由于目前表数据一样，无法看出效果，先修改表中数据：

deletefromtea.teawhereid=2;
insertintotea.teavalues (3,'test3');
commit;

执行导入：

impdpsystem/oracledirectory=DATA_PUMP_DIRdumpfile=tea.dmplogfile=tea.logtables=tea.teatable_exists_action=append

由于建表时没有主键唯一限制，因此允许存在重复数据，导入后数据如下：

select*fromtea.tea;

当使用 APPEND 参数，如果发现存在表，将导入数据进行增量导入，如果有唯一限制时，有重复数据，将会导入失败。

也可以通过在导入命令行上指定 DATA_OPTIONS=SKIP_CONSTRAINT_ERRORS 来覆盖此行为。如果有必须加载的数据，但可能会导致违反约束，可以考虑禁用约束，加载数据，然后在重新启用约束之前删除有问题的行。

3、TRUNCATE

TRUNCATE 参数会删除原表中所有的数据，并且导入新数据，添加参数TABLE_EXISTS_ACTION=TRUNCATE 测试。

执行导入：

select*fromtea.tea;

查询导入后数据：

select*fromtea.tea;

可以看到之前的数据已经不存在，数据重新导入。

SELECTtimestamp, sql_redo, sql_undo, seg_ownerFROMv$logmnr_contentsWHEREseg_name='TEA'ANDseg_owner='TEA';

通过比对导入时间和 LogMiner 表中记录时间，可以看到 TEA 表执行了 TRUNCATE 操作。

4、REPLACE

REPLACE 参数会删除已存在的表然后重新创建，并且导入新数据，添加参数TABLE_EXISTS_ACTION=REPLACE 测试。

导入前插入几条数据：

insertintoteavalues(3,'test3');
insertintoteavalues(4,'test4');
commit;

执行导入：

impdpsystem/oracledirectory=DATA_PUMP_DIRdumpfile=tea.dmplogfile=tea.logtables=tea.teatable_exists_action=replace

通过导入过程没有看到任何关于表已存在的提示，导入正常，查询数据：

数据只存在导入的数据，导入前新增的数据已经消失。

SELECTtimestamp, sql_redo, sql_undo, seg_ownerFROMv$logmnr_contentsWHEREseg_name='TEA'ANDseg_owner='TEA';

通过比对导入时间和 LogMiner 表中记录时间，可以看到 TEA 表先执行 DROP PURGE 操作，然后执行 CREATE TABLE 重新创建表。

总结

使用 SKIP、APPEND 或 TRUNCATE 时，不会修改源中现有的表相关对象，例如索引、授权、触发器和约束。

对于 REPLACE，如果依赖对象未被显式或隐式排除（使用 EXCLUDE）并且它们存在于源转储文件或系统中，则会从源中删除并重新创建它们。