ORACLE中查定位表最后DML操作的时间⼩结
在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)) ;
如下所⽰，我们可以创建⼀个表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-clockoracle 时间转换
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
------------ -------------------------------- --------------------------------

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。