在Oracle資料庫中,如何查找,定位一張表最後一次的DML操作的時間呢? 方式有三種,不過都有一些局限性,下麵簡單的解析、總結一下。 1:使用ORA_ROWSCN偽列獲取表最後的DML時間 ORA_ROWSCN偽列是Oracle 10g開始引入的,可以查詢表中記錄最後變更的SCN。然後通過SCN_... ...
在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