完美世界国际版下载,完美世界有声小说,欢乐颂小说结局

主頁(yè) > 知識(shí)庫(kù) > ORACLE中查找定位表最后DML操作的時(shí)間小結(jié)

ORACLE中查找定位表最后DML操作的時(shí)間小結(jié)

在Oracle數(shù)據(jù)庫(kù)中，如何查找，定位一張表最后一次的DML操作的時(shí)間呢？方式有三種，不過(guò)都有一些局限性，下面簡(jiǎn)單的解析、總結(jié)一下。

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

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

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

如下所示，我們可以創(chuàng)建一個(gè)表TEST，然后查一查T(mén)EST表最后的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)換要依賴(lài)于數(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開(kāi)始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í)行開(kāi)啟行級(jí)跟蹤(create table … rowdependencies)，這樣才會(huì)是在行級(jí)記錄級(jí)別的SCN。而每個(gè)數(shù)據(jù)塊（block)在頭部是記錄了該數(shù)據(jù)塊（block）最近事務(wù)的SCN，所以默認(rèn)情況下，只需要從塊的頭部直接獲取這個(gè)值就可以了，不需要其他任何的開(kāi)銷(xiāo)。但是這明顯是不精確的，一個(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要求精確的話(huà)，就必須開(kāi)啟行級(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)確的，但是也是性能開(kāi)銷(xiāo)最大的，不推薦使用。

總結(jié)

以上所述是小編給大家介紹的ORACLE中查找定位表最后DML操作的時(shí)間小結(jié)，希望對(duì)大家有所幫助，如果大家有任何疑問(wèn)請(qǐng)給我留言，小編會(huì)及時(shí)回復(fù)大家的。在此也非常感謝大家對(duì)腳本之家網(wǎng)站的支持！

您可能感興趣的文章:

JDBC Oracle執(zhí)行executeUpdate卡死問(wèn)題的解決方案
ORACLE檢查找出損壞索引(Corrupt Indexes)的方法詳解
Oracle call 和 exec的詳解及區(qū)別
Oracle數(shù)據(jù)庫(kù)中 call 和 exec的區(qū)別
Oracle基礎(chǔ):通過(guò)sqlplus執(zhí)行sql語(yǔ)句后的結(jié)果進(jìn)行判斷
Oracle統(tǒng)計(jì)信息的導(dǎo)出導(dǎo)入測(cè)試示例詳解
Oracle數(shù)據(jù)庫(kù)自動(dòng)備份腳本分享（超實(shí)用）
VMware下CentOS靜默安裝oracle12.2詳細(xì)圖文教程
ORACLE中關(guān)于表的一些特殊查詢(xún)語(yǔ)句
運(yùn)行在容器中的Oracle XE-11g

標(biāo)簽：南充涼山合肥滁州許昌遼源郴州焦作

巨人網(wǎng)絡(luò)通訊聲明：本文標(biāo)題《ORACLE中查找定位表最后DML操作的時(shí)間小結(jié)》，本文關(guān)鍵詞 ORACLE,中,查找,定位,表,最后,；如發(fā)現(xiàn)本文內(nèi)容存在版權(quán)問(wèn)題，煩請(qǐng)?zhí)峁┫嚓P(guān)信息告之我們，我們將及時(shí)溝通與處理。本站內(nèi)容系統(tǒng)采集于網(wǎng)絡(luò)，涉及言論、版權(quán)與本站無(wú)關(guān)。