用user_tab_modifications查询表dml操作时间

最新推荐文章于 2017-09-18 20:59:47 发布

转载最新推荐文章于 2017-09-18 20:59:47 发布 · 197 阅读

本文介绍如何通过Oracle数据库的USER_TAB_MODIFICATIONS视图来查看表的最后DML操作时间，包括插入、更新和删除操作的数量，以及操作的时间戳。实验证明在对表执行ANALYZE命令后，USER_TAB_MODIFICATIONS视图会被清空，并在执行了DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO过程后再次填充最新信息。

如何查看表最后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:
%F_+e3Mf8yC;e24558279These 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
;l&X,OX)tX.H|24558279TABLE_OWNER VARCHAR2(30) Owner of the modified table.
k+Q{+\ ]24558279TABLE_NAME VARCHAR2(30) Name of the modified tableITPUB个人空间G[3S/TF9f4z,b
PARTITION_NAME VARCHAR2(30) Name of the modified partitionITPUB个人空间[^$hW"R|&s PD
SUBPARTITION_NAME VARCHAR2(30) Name of the modified subpartitionITPUB个人空间m6JcX1tI
INSERTS NUMBER Approximate number of inserts since the last time statistics were gathered
"?]`Q(N*dI24558279UPDATES NUMBER Approximate number of updates since the last time statistics were gathered
%d5BTM"KH24558279DELETES NUMBER Approximate number of deletes since the last time statistics were gatheredITPUB个人空间BfTQ4LA%_
TIMESTAMP DATE Indicates the last time the table was modifiedITPUB个人空间,gCy u0qnBY}/N
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

ITPUB个人空间;FsXE%MJ`
Connected to:ITPUB个人空间Ce w-~`/O
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
2?~i:Gl8r'gW%\24558279With 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;
HXcOr3\;`24558279Connected.ITPUB个人空间vG JVm wc
SQL> set line 200
0h@uDW[24558279SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';ITPUB个人空间ZBR9El)L3DK(X0X2KH
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';

MONITPUB个人空间4KEq*X#LtQA/R9s'u$i
---
ET(k2P6P9[*n24558279YES

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;ITPUB个人空间 ?;@UjS.m ]
BEGIN DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO; END;

*
'NQ$v7]+Z6L#x24558279ERROR at line 1:
qF["`KT24558279ORA-20000: Insufficient privileges
#j!Kd${wEJ m`_;a$Q24558279ORA-06512: at "SYS.DBMS_STATS", line 4535
1B1b,W3~YpPt24558279ORA-06512: at "SYS.DBMS_STATS", line 25376ITPUB个人空间1?H5E1i8g%G't
ORA-06512: at line 1

ITPUB个人空间Z&}D lhu,T
SQL> conn / as sysdba
)QJ!c:z"Z#V J24558279Connected.
u-r;_iT24558279SQL> 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_SEGMENTSITPUB个人空间UG-?O(J?
------------------------------ ------------------------------ ------------------------------ ---------- ---------- ---------- ------------------- --- -------------
{%|6T8\C(RK \24558279T 1 0 0 2012-05-04 12:46:01 NO 0
mYg*L~l@"Nm24558279SQL> analyze table t compute statistics;

Table analyzed.

SQL> select * from USER_TAB_MODIFICATIONS;

no rows selected

SQL>

实验后发现analyze后表USER_TAB_MODIFICATIONS会清空

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/24558279/viewspace-753848/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/24558279/viewspace-753848/