Gesperrte Objekte und Transaktionen

1 Gesperrte Tabelllen anzeigen

select

   c.owner,

   c.object_name,

   c.object_type,

   b.sid,

   b.serial#,

   b.status,

   b.osuser,

   b.machine

from

   v$locked_object a ,

   v$session b,

   dba_objects c

where

   b.sid = a.session_id

and

   a.object_id = c.object_id;


Die Session 'killen' die den LOCK verursacht
ALTER SYSTEM KILL SESSION 'SID,Serial'

2 Gesperrte Objekte anzeigen

SELECT
  l.inst_id,
  substr(l.oracle_username, 1, 8) ora_user,
  substr(l.session_id, 1, 3) sid,
  s.serial#,
  substr(o.owner
         || '.'
         || o.object_name, 1, 40) object,
  p.spid os_pid,
  decode(l.locked_mode, 0, 'NONE', 1, 'NULL',
         2, 'ROW SHARE', 3, 'ROW EXCLUSIVE', 4,
         'SHARE', 5, 'SHARE ROW EXCLUSIVE', 6, 'EXCLUSIVE',
         NULL) lock_mode
FROM
  sys.gv_$locked_object   l,
  dba_objects             o,
  sys.gv_$session         s,
  sys.gv_$process         p
WHERE
  l.object_id = o.object_id
  AND l.inst_id = s.inst_id
  AND l.session_id = s.sid
  AND s.inst_id = p.inst_id
  AND s.paddr = p.addr (+)
ORDER BY
  l.inst_id;

oder

SELECT SID,SERIAL#,STATUS,SERVER
   FROM V$SESSION
   ORDER BY sid;

Session abbrechen

3 Transaktionen anzeigen

SELECT * FROM v$transaction;