欧美bbbwbbbw肥妇,免费乱码人妻系列日韩,一级黄片

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

 更新時間:2018年11月21日 10:12:34   作者:瀟湘隱者  
在Oracle數(shù)據(jù)庫中,如何查找,定位一張表最后一次的DML操作的時間呢? 方式有三種,不過都有一些局限性,下面簡單的解析、總結(jié)一下。感興趣的朋友跟隨小編一起看看吧

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

1:使用ORA_ROWSCN偽列獲取表最后的DML時間

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

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

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

根據(jù)官方文檔來看: SMON進程每5分鐘采集一次插入到SMON_SCN_TIME表中,同時也刪除一些歷史數(shù)據(jù)(超過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操作時間可能不準確,當然對于獲取表最后的DML時間是準確的。

    默認情況下,每行記錄的ORA_ROWSCN是基于數(shù)據(jù)塊(block)的,這樣對于某一行最后的DML時間是不準確的,除非在建表的時候執(zhí)行開啟行級跟蹤(create table … rowdependencies),這樣才會是在行級記錄級別的SCN。而每個數(shù)據(jù)塊(block)在頭部是記錄了該數(shù)據(jù)塊(block)最近事務的SCN,所以默認情況下,只需要從塊的頭部直接獲取這個值就可以了,不需要其他任何的開銷。但是這明顯是不精確的,一個數(shù)據(jù)塊(block)中會有很多行記錄,每次事務不可能影響到整個數(shù)據(jù)塊(block)中所有的行,所以這是一個非常不精準的估算值,同一個數(shù)據(jù)塊(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:假如表的數(shù)據(jù)被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視圖是不會收集相關表的數(shù)據(jù)的呢。 假如某張表之前沒有設置MONITORING屬性,那么無法查找最后一次DML操作的時間,設置MONITORING屬性后,DBA_TAB_MODIFICATIONS視圖里面收集的是這個設置時間點后面的DML操作時間。

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

3:DML操作不提交或回滾,也會記錄到視圖中。這樣就會導致數(shù)據(jù)不準確。

未提交情況:

回滾情況:

3:收集完統(tǒng)計信息(ANALYZE或dbms_stats包收集統(tǒng)計信息)后,視圖中相關表記錄會置空

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收集數(shù)據(jù)會有幾秒的延時,這個時間只能接近最后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
------------ -------------------------------- --------------------------------
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操作時間

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

總結(jié)

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

相關文章

  • 探索ORACLE之ASM概念(完整版)

    探索ORACLE之ASM概念(完整版)

    ASM是Oracle 10g R2中為了簡化Oracle數(shù)據(jù)庫的管理而推出來的一項新功能,這是Oracle自己提供的卷管理器,主要用于替代操作系統(tǒng)所提供的LVM,它不僅支持單實例,同時對RAC的支持也是非常好
    2013-11-11
  • Oracle的約束介紹與約束維護

    Oracle的約束介紹與約束維護

    這篇文章介紹了Oracle的約束與約束維護,文中通過示例代碼介紹的非常詳細。對大家的學習或工作具有一定的參考借鑒價值,需要的朋友可以參考下
    2022-05-05
  • Oracle動態(tài)視圖v$active_session_history實戰(zhàn)示例

    Oracle動態(tài)視圖v$active_session_history實戰(zhàn)示例

    這篇文章主要為大家介紹了Oracle動態(tài)視圖v$active_session_history實戰(zhàn)示例詳解,有需要的朋友可以借鑒參考下,希望能夠有所幫助,祝大家多多進步,早日升職加薪
    2023-03-03
  • Oracle存儲過程的幾種調(diào)用方式圖文詳解

    Oracle存儲過程的幾種調(diào)用方式圖文詳解

    存儲過程是一個預編譯的SQL語句,優(yōu)點是允許模塊化的設計,就是說只需創(chuàng)建一次,以后在程序中就可以調(diào)用多次,下面這篇文章主要給大家介紹了關于Oracle存儲過程的幾種調(diào)用方式,需要的朋友可以參考下
    2023-04-04
  • Oracle SQLPlus導出數(shù)據(jù)到csv文件的方法

    Oracle SQLPlus導出數(shù)據(jù)到csv文件的方法

    這篇文章主要介紹了Oracle SQLPlus導出數(shù)據(jù)到csv文件,需要的朋友可以參考下
    2020-05-05
  • 關于Oracle多表連接,提高效率,性能優(yōu)化操作

    關于Oracle多表連接,提高效率,性能優(yōu)化操作

    這篇文章主要介紹了關于Oracle多表連接,提高效率,性能優(yōu)化操作,具有很好的參考價值,希望對大家有所幫助。一起跟隨小編過來看看吧
    2020-10-10
  • Oracle數(shù)據(jù)庫產(chǎn)重啟服務和監(jiān)聽程序命令介紹

    Oracle數(shù)據(jù)庫產(chǎn)重啟服務和監(jiān)聽程序命令介紹

    大家好,本篇文章主要講的是Oracle數(shù)據(jù)庫產(chǎn)重啟服務和監(jiān)聽程序命令介紹,感興趣的同學趕快來看一看吧,對你有幫助的話記得收藏一下,方便下次瀏覽
    2021-12-12
  • Oracle 查看表空間的大小及使用情況sql語句

    Oracle 查看表空間的大小及使用情況sql語句

    表空間使用情況包括:查看表空間的名稱及大小/查看表空間物理文件的名稱及大小/查看回滾段名稱及大小等等感興趣的你可以參考下本文
    2013-03-03
  • Oracle對字段的增刪改方法分享

    Oracle對字段的增刪改方法分享

    這篇文章給大家分享了Oracle對字段的增刪改語句,對大家日常操作Oracle非常實用,有需要的可以參考借鑒。
    2016-08-08
  • Oracle中行列轉(zhuǎn)換有哪些方法

    Oracle中行列轉(zhuǎn)換有哪些方法

    這篇文章主要給大家介紹了關于Oracle中行列轉(zhuǎn)換有哪些方法的相關資料,最近在工作中遇到了涉及到數(shù)據(jù)庫行列之間相互轉(zhuǎn)換的問題,所以這里給大家總結(jié)介紹下,需要的朋友可以參考下
    2023-08-08

最新評論