Wednesday, November 13, 2013

scrip to list top 10 hottest object in oracle database

col owner  format a20 trunc
col object_name format a30
col touches  format 9,999,999
select *
from (
 select count(*)
 , sum(tch) TOUCHES
 , u.name OWNER
 , o.name OBJECT_NAME
 from  x$bh x
 , obj$ o
 , user$ u
 where x.obj = o.obj#
 and o.owner# = u.user#
 group  by u.name, o.name
   order by 2 desc
 )
where rownum < 11;

No comments:

Post a Comment