Monday, January 17, 2011

how to find what is locking my table

1-

SELECT LPAD(' ',DECODE(l.xidusn,0,3,0)) || l.oracle_username "User Name",o.owner, o.object_name, o.object_type
FROM v$locked_object l, dba_objects o
WHERE l.object_id = o.object_id
ORDER BY o.object_id, 1 desc


2-

SELECT a.sid,a.serial#, a.username,c.os_user_name,a.terminal,
b.object_id,substr(b.object_name,1,40) object_name
from v$session a, dba_objects b, v$locked_object c
where a.sid = c.session_id
and b.object_id = c.object_id

3-

select  oracle_username || ' (' || s.osuser || ')' username
,  s.sid || ',' || s.serial# sess_id,  owner || '.' || object_name object,object_type
,  decode( l.block,0, 'Not Blocking',1, 'Blocking',2, 'Global') status
,  decode(v.locked_mode,0, 'None',1, 'Null',2, 'Row-S (SS)',3, 'Row-X (SX)',4, 'Share',5, 'S/Row-X (SSX)',6, 'Exclusive'
, TO_CHAR(lmode)) mode_held
from       v$locked_object v,  dba_objects d,  v$lock l,  v$session s
where      v.object_id = d.object_id
and        v.object_id = l.id1
and        v.session_id = s.sid
order by oracle_username,  session_id


to kill the session
ALTER SYSTEM KILL SESSION 'sid,serial#';

No comments: