OracleTechnical: V$OBJECT_USAGE does not display rows


When you issue command ALTER INDEX <Schema.index> MONITORING USAGE

from SYS v$object_usage not giving any rows then the solution is mention below

OR


you run monitoring usage command from the same schema whose indexes you want

to monition.


Metalink DOC ID  160712.1

Viewing All Indexes Being Monitored Under Another User's Schema:

================================================================

V$OBJECT_USAGE does not display rows for all indexes in the database whose  usage is

being monitored.

'ALTER INDEX <index> MONITORING USAGE' places an entry in V$OBJECT_USAGE for  that

particular index to help determine if the index is being used or not.

The  V$OBJECT_USAGE view uses the username logged into database when the

'ALTER  INDEX <index> MONITORING USAGE' is issued.

This will not enable any user other than the user who issued the

'ALTER INDEX <index> MONITORING USAGE' to view if  index is being monitored or not.

The view structure may be changed slightly (see below) in order to expand its  scope

system-wide (see below) so that you may see all indexes being monitored.

For example:  Showing User Scott monitoring his Index on EMP table:

SQL>  connect scott/tiger

SQL> set LONG 30000

SQL> select text from dba_views where view_name ='V$OBJECT_USAGE';

TEXT

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#

SQL> select index_name, table_name, uniqueness, status from user_indexes

           where table_name = 'EMP';

INDEX_NAME   TABLE_NAME     UNIQUENES STATUS     PK_EMP   EMP     UNIQUE    VALID

SQL> alter index PK_EMP monitoring usage;

 Index altered.

SQL> select * from v$object_usage;

INDEX_NAME   TABLE_NAME    MONITORING USED START_MONITORING  END_MONITORING

PK_EMP              EMP    YES        NO   10/12/2001 06:42:35

Then connect as another user to view indexes being monitored:

SQL> connect / as sysdba;

Connected.

SQL> select * from v$object_usage;

no rows selected

To be able to view them do the following:

SQL> 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#;

View created.

SQL> select * from v$all_object_usage;

OWNER INDEX_NAME      TABLE_NAME     MON  USE   START_MONITORING   END_MONITORING

SCOTT    PK_EMP          EMP            YES  NO    10/12/2001 06:42:35

OracleTechnical