Query(s) for lock investigation – Drilldown per schema/sid/object_name

In Case Toad or session browser is not accessible J

SELECT /*+ RULE */
       lk.SID, se.username, se.osuser, se.machine,
       DECODE (lk.TYPE,
               ‘TX’, ‘Transaction’,
               ‘TM’, ‘DML’,
               ‘UL’, ‘PL/SQL User Lock’,
               lk.TYPE
              ) lock_type,
       DECODE (lk.lmode,
               0, ‘None’,
               1, ‘Null’,
               2, ‘Row-S (SS)’,
               3, ‘Row-X (SX)’,
               4, ‘Share’,
               5, ‘S/Row-X (SSX)’,
               6, ‘Exclusive’,
               TO_CHAR (lk.lmode)
              ) mode_held,
       DECODE (lk.request,
               0, ‘None’,
               1, ‘Null’,
               2, ‘Row-S (SS)’,
               3, ‘Row-X (SX)’,
               4, ‘Share’,
               5, ‘S/Row-X (SSX)’,
               6, ‘Exclusive’,
               TO_CHAR (lk.request)
              ) mode_requested,
       TO_CHAR (lk.id1) lock_id1, TO_CHAR (lk.id2) lock_id2, ob.owner,
       ob.object_type, ob.object_name,
       DECODE (lk.BLOCK, 0, ‘No’, 1, ‘Yes’, 2, ‘Global’) BLOCK, se.lockwait
  FROM v$lock lk, dba_objects ob, v$session se
 WHERE lk.TYPE IN (‘TM’, ‘UL’)
   AND lk.SID = se.SID
   AND lk.id1 = ob.object_id(+)
   AND se.username = ‘ATD_NL_HORIZON’;
  –AND (lk.SID = ‘452’);
–and ob.object_name = ‘ACCESSORY’;

Advertisements

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

%d bloggers like this: