V$OBJECT_USAGE view empty.

This is a very old issue, but one that sometimes is hard to remember when needed. I tried to monitor index usage to get rid of some indexes which in my opinion are not in use. I implemented the traditional method of index monitoring:

Start monitoring the index.
alter index SchemaOwner.IndexName monitoring usage;

After a while, query the V$OBJECT_USAGE view.

select * from V$OBJECT_USAGE;

It came to my surprise that the view was empty. The reason ... I knew it some time back ... but I forgot. I googled and I found a very good reference from Alex Gorvachev, who went through some sort of similar experience ( v$object_usage empty ? ).

The reason is because internally the v$object_usage view displays filters the objects that belong to the user who performs the query. If you are the owner of the index, or if you have access to the schema owner password you just go ahead, logon to the database with the schema owner password and query the view, if you don't have access to the password or you are not the owner of the index, even if you have DBA privileges you won't be able to query the view.

So the trick in this case is to create a home made view that works around this issue. It is assumed you have enough privileges to access the underlying SYS objects queried by the view.

create or replace view 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.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#
/

enjoy!

The view source code was taken as is from the Alex Gorvachev's blog.