Index monitoring: v$object_usage shows no rows selected

And the Note says that you can only see anything in V$OBJECT_USAGE if you’re connected as the owner of the indexes.

Instead of using the view, this query (which is a hacked version of the view) seems to work:

col username format a15
col ind format a24
col tab format a18

select io.name ind,
username,
t.name tab,
decode(bitand(i.flags, 65536), 0, ‘NO’, ‘YES’),
decode(bitand(ou.flags, 1), 0, ‘NO’, ‘YES’),
substr(ou.start_monitoring , 1, 10) strt_mon
— ou.end_monitoring end_mon
from sys.obj$ io, sys.obj$ t, sys.ind$ i, sys.object_usage ou, dba_users u
where i.obj# = ou.obj#
and io.obj# = ou.obj#
and t.obj# = i.bo#
and user_id=t.owner#

ShowDoc: “To view the information in the ‘V$OBJECT_USAGE’ view you have to be connected as the user owner of the objects you want to monitor, otherwise you might not find the monitoring information you are looking for.”

Advertisements