ORACLE中如何查找定位表最后DML操作的时间

60次阅读

共计 7244 个字符，预计需要花费 19 分钟才能阅读完成。

这篇文章主要介绍 ORACLE 中如何查找定位表最后 DML 操作的时间，文中介绍的非常详细，具有一定的参考价值，感兴趣的小伙伴们一定要看完！

1：使用 ORA_ROWSCN 伪列获取表最后的 DML 时间

ORA_ROWSCN 伪列是 Oracle 10g 开始引入的，可以查询表中记录最后变更的 SCN。然后通过 SCN_TO_TIMESTAMP 函数可以将 SCN 转换为时间戳，从而找到最后 DML 操作时 SCN 的对应时间。但是，默认情况下，每行记录的 ORA_ROWSCN 是基于 Block 的，除非在建表的时候开启行级跟踪。

SELECT MAX(ORA_ROWSCN), SCN_TO_TIMESTAMP(MAX(ORA_ROWSCN)) FROM xxx.xxx;

如下所示，我们可以创建一个表 TEST，然后查一查 TEST 表最后的 DML 的操作时间。如下所示：

SQL  CREATE TABLE TEST.TEST ( ID NUMBER);
 
Table created.
 SQL  COL OWNER FOR A12;
SQL  COL TABLE_NAME FOR A32;
SQL  COL MONITORING FOR A32;
SQL  SELECT OWNER, TABLE_NAME, MONITORING 
 2 FROM DBA_TABLES 
 3 WHERE OWNER= TEST  
 4 AND TABLE_NAME= TEST 
OWNER TABLE_NAME MONITORING
------------ -------------------------------- --------------------------------
TEST TEST YES
SQL  INSERT INTO TEST.TEST VALUES(1);
1 row created.
SQL  COMMIT;
Commit complete.
SQL  SELECT sysdate FROM DUAL;
SYSDATE
-------------------
2018-11-19 14:34:12
SQL  SELECT MAX(ORA_ROWSCN), SCN_TO_TIMESTAMP(MAX(ORA_ROWSCN)) FROM TEST.TEST;
MAX(ORA_ROWSCN) SCN_TO_TIMESTAMP(MAX(ORA_ROWSCN))
--------------- --------------------------------------------------------------
 52782810 19-NOV-18 02.34.03.000000000 PM
SQL

使用 ORA_ROWSCN 伪列获取表最新的 DML 时间，也有一些不足和缺陷，具体如下所示：

1：使用 SCN_TO_TIMESTAMP(MAX(ORA_ROWSCN))获取表最后的 DML 操作时，有可能会遇到 ORA-08181 错误。

$ oerr ora 8181
08181, 00000, specified number is not a valid system change number
// *Cause: supplied scn was beyond the bounds of a valid scn.
// *Action: use a valid scn.

SCN 和时间戳的这种转换要依赖于数据库内部的数据记录, 而这些数据记录就来自 SMON_SCN_TIME 基表，具体来说，SMON_SCN_TIME 基表用于记录过去时间段中 SCN(system change number)与具体的时间戳 (timestamp) 之间的映射关系，因为是采样记录这种映射关系，所以 SMON_SCN_TIME 可以较为粗糙地 (不精确地) 定位某个 SCN 的时间信息。实际的 SMON_SCN_TIME 是一张簇表。而且从 10g 开始 SMON 也会定期清理 SMON_SCN_TIME 中的记录，所以对于比较久远的 SCN 则不能转换。也就出现了数据库某些表使用 SCN_TO_TIMESTAMP 函数时，会遇到 ORA-08181 错误，如下所示，我们用比基表 SMON_SCN_TIME 中 MIN（SCN）的还小 1 的 SCN 做转换时，就会遇到 ORA-08181 这个错误。

根据官方文档来看: SMON 进程每 5 分钟采集一次插入到 SMON_SCN_TIME 表中，同时也删除一些历史数据(超过 5 天前数据)

This is expected behavior as the SCN must be no older than 5 days as part of the current flashback database
features.

Currently, the flashback query feature keeps track of times up to a
maximum of 5 days. This period reflects server uptime, not wall-clock
time. You must record the SCN yourself at the time of interest, such as
before doing a DELETE.

2：使用 ORA_ROWSCN 伪列获取表中某一行的 DML 操作时间可能不准确，当然对于获取表最后的 DML 时间是准确的。

默认情况下，每行记录的 ORA_ROWSCN 是基于数据块（block）的，这样对于某一行最后的 DML 时间是不准确的，除非在建表的时候执行开启行级跟踪 (create table … rowdependencies)，这样才会是在行级记录级别的 SCN。而每个数据块（block) 在头部是记录了该数据块（block）最近事务的 SCN，所以默认情况下，只需要从块的头部直接获取这个值就可以了，不需要其他任何的开销。但是这明显是不精确的，一个数据块（block)中会有很多行记录，每次事务不可能影响到整个数据块（block）中所有的行，所以这是一个非常不精准的估算值，同一个数据块（block）的所有记录的 ORA_ROWSCN 都会是相同的. 如下实验所示，当然对于获取表最后的 DML 时间是准确的。所以对于每一行的 ORA_ROWSCN 要求精确的话，就必须开启行级跟踪。

 SQL  SELECT * FROM TEST.TEST;
 ID
----------
 1
SQL  SELECT ID, SCN_TO_TIMESTAMP(ORA_ROWSCN) FROM TEST.TEST;
 ID SCN_TO_TIMESTAMP(ORA_ROWSCN)
---------- -------------------------------------------------------------------
 1 19-NOV-18 02.34.03.000000000 PM
SQL  INSERT INTO TEST.TEST VALUES(2);
1 row created.
SQL  COMMIT;
Commit complete.
SQL  INSERT INTO TEST.TEST VALUES(3);
1 row created.
SQL  COMMIT;
Commit complete.
SQL  SELECT ID, SCN_TO_TIMESTAMP(ORA_ROWSCN) FROM TEST.TEST;
 ID SCN_TO_TIMESTAMP(ORA_ROWSCN)
---------- ---------------------------------------------------------------
 1 19-NOV-18 03.41.01.000000000 PM
 2 19-NOV-18 03.41.01.000000000 PM
 3 19-NOV-18 03.41.01.000000000 PM

3：假如表的数据被 TRUNCATE 掉或全部 DELETE 后，也会导致无法定位最后一次 DML 操作的时间。如下所示：

2：使用 DBA_TAB_MODIFICATIONS 来查找、定为最后的 DML 操作时间

DBA_TAB_MODIFICATIONS describes modifications to all tables in the database that have been modified since the last time statistics were gathered on the tables

This view is populated only for tables with the MONITORING attribute. It is intended for statistics collection over a long period of time. For performance reasons, the Oracle Database does not populate this view immediately when the actual modifications occur. Run the FLUSH_DATABASE_MONITORING_INFO procedure in the DIMS_STATS PL/SQL package to populate this view with the latest information. The ANALYZE_ANY system privilege is required to run this procedure.

使用 DBA_TAB_MODIFICATIONS 来查看表最后 DML 的操作时间，如下测试所示

SQL  CREATE TABLE TEST.TEST (ID NUMBER);
Table created.
SQL  COL OWNER FOR A12;
SQL  COL TABLE_NAME FOR A32;
SQL  COL MONITORING FOR A32;
SQL  SELECT OWNER, TABLE_NAME, MONITORING 
 2 FROM DBA_TABLES 
 3 WHERE OWNER= TEST  
 4 AND TABLE_NAME= TEST 
OWNER TABLE_NAME MONITORING
------------ -------------------------------- --------------------------------
TEST TEST YES
SQL  INSERT INTO TEST.TEST VALUES(1);
1 row created.
SQL  COMMIT;
Commit complete.
SQL  ALTER SESSION SET NLS_DATE_FORMAT= YYYY-MM-DD HH24:MI:SS 
Session altered.
SQL  SELECT INSERTS,UPDATES,DELETES,TRUNCATED,TIMESTAMP 
 2 FROM DBA_TAB_MODIFICATIONS 
 3 WHERE TABLE_NAME= TEST  AND TABLE_OWNER= TEST 
no rows selected
SQL  EXEC DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;
PL/SQL procedure successfully completed.
SQL  SELECT INSERTS,UPDATES,DELETES,TRUNCATED,TIMESTAMP 
 2 FROM DBA_TAB_MODIFICATIONS 
 3 WHERE TABLE_NAME= TEST  AND TABLE_OWNER= TEST 
 INSERTS UPDATES DELETES TRU TIMESTAMP
---------- ---------- ---------- --- -------------------
 1 0 0 NO 2018-11-20 10:34:24

但是用 DBA_TAB_MODIFICATIONS 来定位表最后的 DML 操作时间也有一定的局限性。如下所示，有些局限性会影响定位最后 DML 操作的时间的准确性。

1：如果表没有设置 MONITORING 属性，那么 DBA_TAB_MODIFICATIONS 视图是不会收集相关表的数据的呢。假如某张表之前没有设置 MONITORING 属性，那么无法查找最后一次 DML 操作的时间，设置 MONITORING 属性后，DBA_TAB_MODIFICATIONS 视图里面收集的是这个设置时间点后面的 DML 操作时间。

2：需要执行 EXEC DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO 后，视图才会有数据。

3：DML 操作不提交或回滚，也会记录到视图中。这样就会导致数据不准确。

未提交情况：

回滚情况：

3：收集完统计信息 (ANALYZE 或 dbms_stats 包收集统计信息) 后，视图中相关表记录会置空

SQL  SELECT INSERTS,UPDATES,DELETES,TRUNCATED,TIMESTAMP 
 2 FROM DBA_TAB_MODIFICATIONS 
 3 WHERE TABLE_NAME= TEST  AND TABLE_OWNER= TEST 
 INSERTS UPDATES DELETES TRU TIMESTAMP
---------- ---------- ---------- --- -------------------
 6 0 4 YES 2018-11-20 13:14:08
SQL  exec dbms_stats.gather_table_stats( TEST , TEST 
PL/SQL procedure successfully completed.
SQL  SELECT INSERTS,UPDATES,DELETES,TRUNCATED,TIMESTAMP 
 2 FROM DBA_TAB_MODIFICATIONS 
 3 WHERE TABLE_NAME= TEST  AND TABLE_OWNER= TEST 
no rows selected
SQL

4：CTAS 建立的插入信息不会记录。如下测试所示：

SQL  CREATE TABLE TEST.TEST1
 2 AS
 3 SELECT * FROM TEST.TEST;
Table created.
SQL  exec dbms_stats.flush_database_monitoring_info;
PL/SQL procedure successfully completed.
SQL  SELECT INSERTS,UPDATES,DELETES,TRUNCATED,TIMESTAMP 
 2 FROM DBA_TAB_MODIFICATIONS 
 3 WHERE TABLE_NAME= TEST1  AND TABLE_OWNER= TEST 
no rows selected

5:DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO 收集数据会有几秒的延时，这个时间只能接近最后 DML 时间，而不是精准的。

SQL  COL OWNER FOR A12;
SQL  COL TABLE_NAME FOR A32;
SQL  COL MONITORING FOR A32;
SQL  SELECT OWNER, TABLE_NAME, MONITORING 
 2 FROM DBA_TABLES 
 3 WHERE OWNER= TEST  
 4 AND TABLE_NAME= TEST1 
OWNER TABLE_NAME MONITORING
------------ -------------------------------- --------------------------------
TEST TEST1 YES
SQL  
SQL  SELECT SYSDATE FROM DUAL;
SYSDATE
-------------------
2018-11-20 10:46:39
SQL  INSERT INTO TEST.TEST VALUES(10);
1 row created.
SQL  SELECT SYSDATE FROM DUAL;
SYSDATE
-------------------
2018-11-20 10:46:57
SQL  COMMIT;
Commit complete.
SQL  SELECT SYSDATE FROM DUAL;
SYSDATE
-------------------
2018-11-20 10:47:07
SQL  exec dbms_stats.flush_database_monitoring_info;
PL/SQL procedure successfully completed.
SQL  SELECT INSERTS,UPDATES,DELETES,TRUNCATED,TIMESTAMP 
 2 FROM DBA_TAB_MODIFICATIONS 
 3 WHERE TABLE_NAME= TEST  AND TABLE_OWNER= TEST 
 INSERTS UPDATES DELETES TRU TIMESTAMP
---------- ---------- ---------- --- -------------------
 3 0 0 NO 2018-11-20 10:47:13

3：触发器捕获最后 DML 操作时间

使用触发器捕获 DML 操作的最后时间是最准确的，但是也是性能开销最大的，不推荐使用。

以上是“ORACLE 中如何查找定位表最后 DML 操作的时间”这篇文章的所有内容，感谢各位的阅读！希望分享的内容对大家有帮助，更多相关知识，欢迎关注丸趣 TV 行业资讯频道！

正文完