关于视图：V$OBJECT_USAGE

地址：http://docs.oracle.com/cd/B19306_01/server.102/b14237/dynviews_2004.htm#REFRN30162

 

V$OBJECT_USAGE

You can use this view to monitor index usage. The view displaysstatistics about index usage gathered from the database. Allindexes that have been used at least once can be monitored anddisplayed in this view.

ColumnDatatypeDescriptionINDEX_NAMEVARCHAR2(30)Index name insys.obj$.nameTABLE_NAMEVARCHAR2(30)Table name insys.obj$.nameMONITORINGVARCHAR2(3)YES|NOUSEDVARCHAR2(3)YES|NOSTART_MONITORINGVARCHAR2(19)Start monitoring time insys.object_stats.start_monitoringEND_MONITORINGVARCHAR2(19)End monitoring time insys.object_stats.end_monitoring

注意：

 

1、$object_usage只包括当前用户所拥有索引的使用记录，即索引的创建者或者是索引的拥有者(owner),已这个用户登录后，看到的是此用户下拥有的索引在整个数据库的使用情况。如果使用了该索引，视图V$OBJECT_USAGE的字段USED会标成YES。否则是NO。当然首先是这些索引被启用使用监视。SQL>alter index index_test_pk monitoring usage;

 

2、如果需要查出所有用户所拥有索引的使用记录，使用下面的sql，这个sql来自DBA日记：
SQL> select u.name owner, io.name index_name, t.nametable_name,
decode(bitand(i.flags, 65536), 0, 'NO', 'YES') monitoring,
decode(bitand(ou.flags, 1), 0, 'NO', 'YES')used,ou.start_monitoring
start_monitoring,ou.end_monitoring end_monitoring
from sys.user$ u, sys.obj$ io, sys.obj$ t, sys.ind$ i,sys.object_usage
ou where i.obj# = ou.obj# and io.obj# = ou.obj# and t.obj# =i.bo#
and u.user# = io.owner#;