Thursday, April 4, 2013

Get the DB Lock Information


SELECT s.audsid AUDSID,
       l.os_user_name "OS Username",
       l.oracle_username "Username",
       l.process "Process",
       p.spid "SPID",
       o.owner "Owner",
       o.object_name "Object",
       DECODE (l.locked_mode,
               1, 'No Lock',
               2, 'Row Share',
               3, 'Row Exclusive',
               4, 'Share',
               5, 'Share Row Excl',
               6, 'Exclusive',
               NULL)
          "Lock Mode",
       SUBSTR (s.status, 1, 8) "Status"
  FROM v$locked_object l,
       all_objects o,
       v$session s,
       v$process p
 WHERE     l.object_id = o.object_id
       AND l.session_id = s.sid
       AND s.paddr = p.addr
       AND s.status <> 'KILLED';