ORACLE中如何查找定位表最后DML操作的時(shí)間

140次閱讀

共計(jì) 7244 個(gè)字符，預(yù)計(jì)需要花費(fèi) 19 分鐘才能閱讀完成。

這篇文章主要介紹 ORACLE 中如何查找定位表最后 DML 操作的時(shí)間，文中介紹的非常詳細(xì)，具有一定的參考價(jià)值，感興趣的小伙伴們一定要看完！

1：使用 ORA_ROWSCN 偽列獲取表最后的 DML 時(shí)間

ORA_ROWSCN 偽列是 Oracle 10g 開始引入的，可以查詢表中記錄最后變更的 SCN。然后通過(guò) SCN_TO_TIMESTAMP 函數(shù)可以將 SCN 轉(zhuǎn)換為時(shí)間戳，從而找到最后 DML 操作時(shí) SCN 的對(duì)應(yīng)時(shí)間。但是，默認(rèn)情況下，每行記錄的 ORA_ROWSCN 是基于 Block 的，除非在建表的時(shí)候開啟行級(jí)跟蹤。

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

如下所示，我們可以創(chuàng)建一個(gè)表 TEST，然后查一查 TEST 表最后的 DML 的操作時(shí)間。如下所示：

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 時(shí)間，也有一些不足和缺陷，具體如下所示：

1：使用 SCN_TO_TIMESTAMP(MAX(ORA_ROWSCN))獲取表最后的 DML 操作時(shí)，有可能會(huì)遇到 ORA-08181 錯(cuò)誤。

$ 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 和時(shí)間戳的這種轉(zhuǎn)換要依賴于數(shù)據(jù)庫(kù)內(nèi)部的數(shù)據(jù)記錄, 而這些數(shù)據(jù)記錄就來(lái)自 SMON_SCN_TIME 基表，具體來(lái)說(shuō)，SMON_SCN_TIME 基表用于記錄過(guò)去時(shí)間段中 SCN(system change number)與具體的時(shí)間戳 (timestamp) 之間的映射關(guān)系，因?yàn)槭遣蓸佑涗涍@種映射關(guān)系，所以 SMON_SCN_TIME 可以較為粗糙地 (不精確地) 定位某個(gè) SCN 的時(shí)間信息。實(shí)際的 SMON_SCN_TIME 是一張簇表。而且從 10g 開始 SMON 也會(huì)定期清理 SMON_SCN_TIME 中的記錄，所以對(duì)于比較久遠(yuǎn)的 SCN 則不能轉(zhuǎn)換。也就出現(xiàn)了數(shù)據(jù)庫(kù)某些表使用 SCN_TO_TIMESTAMP 函數(shù)時(shí)，會(huì)遇到 ORA-08181 錯(cuò)誤，如下所示，我們用比基表 SMON_SCN_TIME 中 MIN（SCN）的還小 1 的 SCN 做轉(zhuǎn)換時(shí)，就會(huì)遇到 ORA-08181 這個(gè)錯(cuò)誤。

根據(jù)官方文檔來(lái)看: SMON 進(jìn)程每 5 分鐘采集一次插入到 SMON_SCN_TIME 表中，同時(shí)也刪除一些歷史數(shù)據(jù)(超過(guò) 5 天前數(shù)據(jù))

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 操作時(shí)間可能不準(zhǔn)確，當(dāng)然對(duì)于獲取表最后的 DML 時(shí)間是準(zhǔn)確的。

默認(rèn)情況下，每行記錄的 ORA_ROWSCN 是基于數(shù)據(jù)塊（block）的，這樣對(duì)于某一行最后的 DML 時(shí)間是不準(zhǔn)確的，除非在建表的時(shí)候執(zhí)行開啟行級(jí)跟蹤 (create table … rowdependencies)，這樣才會(huì)是在行級(jí)記錄級(jí)別的 SCN。而每個(gè)數(shù)據(jù)塊（block) 在頭部是記錄了該數(shù)據(jù)塊（block）最近事務(wù)的 SCN，所以默認(rèn)情況下，只需要從塊的頭部直接獲取這個(gè)值就可以了，不需要其他任何的開銷。但是這明顯是不精確的，一個(gè)數(shù)據(jù)塊（block)中會(huì)有很多行記錄，每次事務(wù)不可能影響到整個(gè)數(shù)據(jù)塊（block）中所有的行，所以這是一個(gè)非常不精準(zhǔn)的估算值，同一個(gè)數(shù)據(jù)塊（block）的所有記錄的 ORA_ROWSCN 都會(huì)是相同的. 如下實(shí)驗(yàn)所示，當(dāng)然對(duì)于獲取表最后的 DML 時(shí)間是準(zhǔn)確的。所以對(duì)于每一行的 ORA_ROWSCN 要求精確的話，就必須開啟行級(jí)跟蹤。

 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：假如表的數(shù)據(jù)被 TRUNCATE 掉或全部 DELETE 后，也會(huì)導(dǎo)致無(wú)法定位最后一次 DML 操作的時(shí)間。如下所示：

2：使用 DBA_TAB_MODIFICATIONS 來(lái)查找、定為最后的 DML 操作時(shí)間

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 來(lái)查看表最后 DML 的操作時(shí)間，如下測(cè)試所示

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 來(lái)定位表最后的 DML 操作時(shí)間也有一定的局限性。如下所示，有些局限性會(huì)影響定位最后 DML 操作的時(shí)間的準(zhǔn)確性。

1：如果表沒(méi)有設(shè)置 MONITORING 屬性，那么 DBA_TAB_MODIFICATIONS 視圖是不會(huì)收集相關(guān)表的數(shù)據(jù)的呢。假如某張表之前沒(méi)有設(shè)置 MONITORING 屬性，那么無(wú)法查找最后一次 DML 操作的時(shí)間，設(shè)置 MONITORING 屬性后，DBA_TAB_MODIFICATIONS 視圖里面收集的是這個(gè)設(shè)置時(shí)間點(diǎn)后面的 DML 操作時(shí)間。

2：需要執(zhí)行 EXEC DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO 后，視圖才會(huì)有數(shù)據(jù)。

3：DML 操作不提交或回滾，也會(huì)記錄到視圖中。這樣就會(huì)導(dǎo)致數(shù)據(jù)不準(zhǔn)確。

未提交情況：

回滾情況：

3：收集完統(tǒng)計(jì)信息 (ANALYZE 或 dbms_stats 包收集統(tǒng)計(jì)信息) 后，視圖中相關(guān)表記錄會(huì)置空

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 建立的插入信息不會(huì)記錄。如下測(cè)試所示：

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 收集數(shù)據(jù)會(huì)有幾秒的延時(shí)，這個(gè)時(shí)間只能接近最后 DML 時(shí)間，而不是精準(zhǔn)的。

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：觸發(fā)器捕獲最后 DML 操作時(shí)間

使用觸發(fā)器捕獲 DML 操作的最后時(shí)間是最準(zhǔn)確的，但是也是性能開銷最大的，不推薦使用。

以上是“ORACLE 中如何查找定位表最后 DML 操作的時(shí)間”這篇文章的所有內(nèi)容，感謝各位的閱讀！希望分享的內(nèi)容對(duì)大家有幫助，更多相關(guān)知識(shí)，歡迎關(guān)注丸趣 TV 行業(yè)資訊頻道！

正文完