v$object_usage catches me out every time :-)

 

Every …. single …. time….

Why does index monitoring make me scratch my head and charge off to google so many times…Well, I’m over it, so time to put it on my blog (even though its already on many other places) so I do not get caught out anymore 🙂

It always starts like this:

  • Turn on index monitoring on some indexes
  • Wait…
  • Come back later, and get ready to check on my results
  • Then this happens…
SQL> select * from v$object_usage;

no rows selected

And I panic…Did I run the commands on the wrong database ?  Did they not run properly ? Do I need to flush a pool ? etc etc etc

And after plenty of wasted minutes…I end up digging up the source for the v$object_usage view

create or replace force view sys.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#;

And there it is…you can only see rows for the schema you are currently in….Grrrr.

So from now on, I’m gonna to always create a new view

create or replace view sys.an_object_usage_definition_that_works as
  select io.name index_name
        ,t.name table_name
        ,decode(bitand(i.flags
                      ,65536)
               ,0, 'NO'
               ,'YES') monitoring
        ,decode(bitand(ou.flags
                      ,1)
               ,0, 'NO'
               ,'YES') used
        ,ou.start_monitoring
        ,ou.end_monitoring
  from   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#;

but I have to wait until we can have 37 character long object names 🙂

Advertisements

5 thoughts on “v$object_usage catches me out every time :-)

  1. Just for the record – in 12c the view v$object_usage is deprecated. 😦 There is already a task on my ToDo list, to adapt some scripts, to use USER_OBJECT_USAGE and DBA_OBJECT_USAGE instead of v$object_usage …

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s