概述

在Oracle数据库中，如何查找，定位一张表最后一次的DML操作的时间呢？下面介绍怎么通过ORA_ROWSCN伪列来获取表最后的DML时间。

使用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的操作时间。

1、创建测试环境

drop table TEST;
CREATE TABLE TEST (ID NUMBER);
set linesize 1000;
COL OWNER FOR A12;
COL TABLE_NAME FOR A32;
COL MONITORING FOR A32;
SELECT OWNER, TABLE_NAME, MONITORING FROM DBA_TABLES WHERE OWNER='SYS' AND TABLE_NAME='TEST';
INSERT INTO TEST VALUES(1);
INSERT INTO TEST VALUES(2);
COMMIT;

2、查看最后的DML的操作时间

SELECT sysdate FROM DUAL;
SELECT MAX(ORA_ROWSCN), SCN_TO_TIMESTAMP(MAX(ORA_ROWSCN)) FROM TEST;

使用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要求精确的话，就必须开启行级跟踪。

SELECT * FROM TEST;
SELECT ID, SCN_TO_TIMESTAMP(ORA_ROWSCN) FROM SYS.TEST;
INSERT INTO TEST VALUES(3);
INSERT INTO TEST VALUES(4);
COMMIT;
SELECT ID, SCN_TO_TIMESTAMP(ORA_ROWSCN) FROM SYS.TEST;

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

默认情况下，每行记录的ORA_ROWSCN是基于Block的，这样是不准确的，除非在建表的时候执行开启行级跟踪(create table … rowdependencies)，这样就会是在行级记录scn（加上这个功能，会影响存储，每条记录会增加6字节的物理存储空间）

所以如果大家想查看准确的记录创建时间在建表时要加rowdependencies选项。

后面会分享更多关于DBA方面内容，感兴趣的朋友可以关注下！