Pre-Rolled Diagnostic Queries

An accompaniment to the Stuff I Don’t Use Often Enough To Remember. To anyone else using them, these are for my own use, so they just may not work for you. If you’re not using RAC, replace any gv$ prefixes to views with v$.

Owners of Locks and Latches

select ao.owner,
       ao.object_name,
       ao.object_type,
       lo.SESSION_ID,
       lo.ORACLE_USERNAME,
       lo.OS_USER_NAME,
       DECODE(lo.locked_mode,
              0, 'None',
              1, 'Null',
              2, 'Row-S (SS)',
              3, 'Row-X (SX)',
              4, 'Share',
              5, 'S/Row-X (SSX)',
              6, 'Exclusive',
              lo.locked_mode) MODE_HELD
from   gv$locked_object lo,
       all_objects ao
where  ao.object_id = lo.OBJECT_ID;

Lock mode translations from Patrick Boulay’s blog.

Data Blocks in SGA

select owner,
       object,
       object_blocks blocks,
       round(percent, 2) percent,
       round(sum(percent) over (order by percent desc, object),3) cumul_percent
from   (select owner, object, object_blocks, (100 * (object_blocks / total_blocks.c)) percent
        from   (select   owner, object_name || decode (subobject_name, NULL,'', ' : ' || subobject_name) object, count(*) object_blocks
                from     dba_objects o,
                         gv$bh       bh
                where    o.object_id = bh.objd
                and      owner not in ('SYS', 'SYSTEM', 'SYSMAN', 'DBSNMP')
                group by owner, object_name || decode (subobject_name, NULL,'', ' : ' || subobject_name)),
               (select count(*) c
                from   gv$bh) total_blocks)
order by blocks desc, object;

What’s Locking What
from: http://www.orafaq.com/node/854

select s1.username || '@' || s1.machine || ' ( SID=' || s1.sid || ' )  is blocking ' || s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status
from   gv$lock l1, gv$session s1, gv$lock l2, gv$session s2
where  s1.sid = l1.sid
and    s2.sid = l2.sid
and    l1.BLOCK = 1
and    l2.request > 0
and    l1.id1 = l2.id1
and    l2.id2 = l2.id2;