Wednesday, August 10, 2016

Index monitoring - v$object_usage returns no rows

Cause: v$object_usage view is specific to the currently connected user.

Solution:  Either connect as the object owner, or create the following view as sys

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#
/


No comments:

Post a Comment