今天在查看索引监控情况的时候发现v$object_usage视图其实是个比较特殊的v$视图，因为它只能查看本用户下索引的情况，即使是SYS用户也查不到其他用户的情况，该视图的创建语句如下：

create or replace view v$object_usage

(index_name, table_name, monitoring, used, start_monitoring, end_monitoring)

as

select io.name, t.name,

decode(bitand(i.flags, 65536), 0, 'NO', 'YES'),

decode(bitand(ou.flags, 1), 0, 'NO', 'YES'),

ou.start_monitoring,

ou.end_monitoring

from sys.obj$ io, sys.obj$ t, sys.ind$ i, sys.object_usage ou

where io.owner# = userenv('SCHEMAID')

and i.obj# = ou.obj#

and io.obj# = ou.obj#

and t.obj# = i.bo#;

可以看到确实只能查到自己拥有的索引情况。

如果要查看所有的索引监控情况，可以创建如下视图：

create or replace view sys.v$all_object_usage

(owner,index_name,table_name,monitoring,used,start_monitoring,end_monitoring)

as

select u.name, io.name, t.name,

decode(bitand(i.flags, 65536), 0, 'NO', 'YES'),

decode(bitand(ou.flags, 1), 0, 'NO', 'YES'),

ou.start_monitoring,

ou.end_monitoring

from sys.obj$ io, sys.obj$ t, sys.ind$ i, sys.object_usage ou, sys.user$ u

where i.obj# = ou.obj#

and io.obj# = ou.obj#

and t.obj# = i.bo#

and io.owner# = u.user#;

另外一个注意的地方是，被开启监控的索引如果rebuild，监控会停止，并且是否使用会变成NO，以下为实验过程：

[@more@]

SCOTT@ora10g>create table idx_mon as select * from emp;

表已创建。

SCOTT@ora10g>create index empno_idx on idx_mon(empno);

索引已创建。

SCOTT@ora10g>alter index empno_idx monitoring usage;

索引已更改。

SCOTT@ora10g>select * from v$object_usage;

INDEX_NAME TABLE_NAME MON USE START_MONITORING END_MONITORING

------------------------------ ------------------------------ --- --- ------------------- -------------------

EMPNO_IDX IDX_MON YES NO 12/17/2009 15:59:33

SCOTT@ora10g>conn / as sysdba

已连接。

SYS@ora10g>select * from v$object_usage; sys无法使用v$object_usage查看scott的索引监控

未选定行

SYS@ora10g>conn scott/tiger

已连接。

SCOTT@ora10g>alter index empno_idx rebuild;

索引已更改。

SCOTT@ora10g>select * from v$object_usage;

INDEX_NAME TABLE_NAME MON USE START_MONITORING END_MONITORING

------------------------------ ------------------------------ --- --- ------------------- -------------------

EMPNO_IDX IDX_MON NO YES 12/17/2009 15:59:33

SCOTT@ora10g>drop index empno_idx;

索引已删除。

SCOTT@ora10g>select * from v$object_usage; 索引drop后信息删除

未选定行

oracle9i的BUG：

在9205之前，如果你不慎监控了SYS.I_OBJAUTH1索引，并且不幸在重起数据库之前没有停止它，那么你的数据库将会无法启动，并且不会给出任何错误信息。

alter index sys.i_objauth1 monitoring usage;

索引监控开启命令：

alter index name_idx monitoring usage;

索引监控关闭命令：

alter index name_idx nomonitoring usage;

清理v$object_usage视图：

如果想清理v$object_usage只需清理object_usage即可，但是生产库请慎用！

最后附上开启某schema下所有索引监控的脚本：

set pages 999

set heading off

spool run_idx_mon.sql

select 'alter index '||index_name||' monitoring usage;'

from dba_indexes

where owner = 'SCOTT';

spool off

@run_idx_mon