用user_tab_modifications查詢表dml操作時間

今天有朋友問，如何檢視錶最後dml操作時間，查詢官方文件發現表ALL_TAB_MODIFICATIONS可以滿足需求。

我們先來看看錶ALL_TAB_MODIFICATIONS的簡介

ALL_TAB_MODIFICATIONS describes tables accessible to the current user that have been modified since the last time statistics were gathered on the tables.

 

Related Views

 

DBA_TAB_MODIFICATIONS provides such information for all tables in the database.

 

USER_TAB_MODIFICATIONS provides such information for tables owned by the current user. This view does not display the TABLE_OWNER column.

 

Note:
These views are populated only for tables with the MONITORING attribute. They are intended for statistics collection over a long period of time. For performance reasons, the Oracle Database does not populate these views immediately when the actual modifications occur. Run the FLUSH_DATABASE_MONITORING_INFO procedure in the DIMS_STATS PL/SQL package to populate these views with the latest information. The ANALYZE_ANY system privilege is required to run this procedure.

 

Column Datatype NULL Description
TABLE_OWNER VARCHAR2(30)   Owner of the modified table.
TABLE_NAME VARCHAR2(30)   Name of the modified table
PARTITION_NAME VARCHAR2(30)   Name of the modified partition
SUBPARTITION_NAME VARCHAR2(30)   Name of the modified subpartition
INSERTS NUMBER   Approximate number of inserts since the last time statistics were gathered
UPDATES NUMBER   Approximate number of updates since the last time statistics were gathered
DELETES NUMBER   Approximate number of deletes since the last time statistics were gathered
TIMESTAMP DATE   Indicates the last time the table was modified
DROP_SEGMENTS NUMBER   Number of partition and subpartition segments dropped since the last analyze

 

看到官方叫views，我們還是稱呼為表吧，下面做個實驗，環境是11g

 

[oracle@cent6224 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Fri May 4 12:33:49 2012

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning and Data Mining options

SQL> create user modifytest identified by modifytest;

User created.

SQL> grant connect,resource to modifytest;

Grant succeeded.

SQL> conn modifytest/modifytest;
Connected.
SQL> set line 200
SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
SQL> select * from USER_TAB_MODIFICATIONS;

no rows selected

SQL> select table_name,inserts,updates,deletes from user_tab_modifications;

no rows selected

SQL> create table t(a number);

Table created.

SQL> select monitoring from user_tables where table_name='T';

MON
---
YES

SQL> select * from USER_TAB_MODIFICATIONS;

no rows selected

SQL> insert into t values(1);

1 row created.

SQL> select * from USER_TAB_MODIFICATIONS;

no rows selected

SQL> commit;

Commit complete.

SQL> select * from USER_TAB_MODIFICATIONS;

no rows selected

SQL> exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;
BEGIN DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO; END;

*
ERROR at line 1:
ORA-20000: Insufficient privileges
ORA-06512: at "SYS.DBMS_STATS", line 4535
ORA-06512: at "SYS.DBMS_STATS", line 25376
ORA-06512: at line 1

SQL> conn / as sysdba
Connected.
SQL> grant analyze any to modifytest;

Grant succeeded.

SQL> exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;

PL/SQL procedure successfully completed.

SQL> select * from USER_TAB_MODIFICATIONS;

TABLE_NAME                     PARTITION_NAME                 SUBPARTITION_NAME                 INSERTS    UPDATES    DELETES TIMESTAMP           TRU DROP_SEGMENTS
------------------------------ ------------------------------ ------------------------------ ---------- ---------- ---------- ------------------- --- -------------
T                                                                                                     1          0          0 2012-05-04 12:46:01 NO              0
SQL> analyze table t compute statistics;

Table analyzed.

SQL> select * from USER_TAB_MODIFICATIONS;

no rows selected

SQL>

 

實驗後發現analyze後表USER_TAB_MODIFICATIONS會清空