Oracle视图user_tab_modifications
user_tab_modifications收集自采集信息以来被改变表的dml操作量数据,一个表只有数据量被改变10%以上才会被定期采集信息,也可以执行过程
DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO进行实时采集。
由于user_tab_modifications收集的信息是自第一次收集以来的信息,所以要了解某一段时间对某表的dml操作情况,应该做个快照差。
下面给个例子
现在有表t1
SQL> select * from t2;
ID NAME
---------- --------------------------------
1 scott
2 fishcat
查询user_tab_modifications无记录说明没做任何信息收据
SQL> select * from user_tab_modifications where table_name='T2';
no rows selected
使用DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO更新下看看结果
SQL> conn / as sysdba
Connected.
SQL> grant ANALYZE any to scott;
Grant succeeded.
SQL> exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;
PL/SQL procedure successfully completed.
SQL> select * from user_tab_modifications where table_name='T2';
no rows selected
可以看出依然没有结果
对t2插入一条数据看看
SQL> insert into t2 values(3,'xyc');
1 row created.
SQL> exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;
PL/SQL procedure successfully completed.
SQL> select table_name,inserts,updates,deletes,timestamp,drop_segments from user_tab_modifications where table_name='T2';
TABLE_NAME INSERTS UPDATES DELETES TIMESTAMP DROP_SEGMENTS
------------------------------ ---------- ---------- ---------- ------------ -------------
T2 1 0 0 20-FEB-13 0
可以看到一条insert记录inserts为1,下面继续
SQL> update t2 set name='xycxyc' where id=3;
1 row updated.
SQL> exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;
PL/SQL procedure successfully completed.
SQL> select table_name,inserts,updates,deletes,timestamp,drop_segments from user_tab_modifications where table_name='T2';
TABLE_NAME INSERTS UPDATES DELETES TIMESTAMP DROP_SEGMENTS
------------------------------ ---------- ---------- ---------- ------------ -------------
T2 1 1 0 20-FEB-13 0
SQL> insert into t2 values(3,'xyc');
1 row created.
SQL> exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;
PL/SQL procedure successfully completed.
SQL> select table_name,inserts,updates,deletes,timestamp,drop_segments from user_tab_modifications where table_name='T2';
TABLE_NAME INSERTS UPDATES DELETES TIMESTAMP DROP_SEGMENTS
------------------------------ ---------- ---------- ---------- ------------ -------------
T2 2 1 0 20-FEB-13 0
可以看到inserts和updates的更改
USER_TAB_MODIFICATIONS还记录了表是否被truncate和分区,子分区是否有删除过的记录,参考:
Related Views
-
DBA_TAB_MODIFICATIONS describes such information for all tables in the database.
-
USER_TAB_MODIFICATIONS describes 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 DBMS_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 | |
TRUNCATED | VARCHAR2(3) | Indicates whether the table has been truncated since the last analyze (YES) or not (NO) | |
DROP_SEGMENTS | NUMBER | Number of partition and subpartition segments dropped since the last analyze |
查询数据更改小于10%的表信息
select owner,table_name,last_analyzed,num_rows,modify_rows,modify_rate from (select t.owner,
t.table_name,
t.last_analyzed,
t.num_rows,
m.modify_rows,
round(m.modify_rows / t.num_rows * 100, 2) as modify_rate
from dba_tables t
inner join (select table_owner,
table_name,
inserts + updates + deletes as modify_rows
from dba_tab_modifications
where inserts + updates + deletes > 0) M
on t.owner = m.table_owner
and t.table_name = m.table_name
and t.num_rows > 0)
where modify_rate<10
order by modify_rate
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29108856/viewspace-2143648/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/29108856/viewspace-2143648/