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#';
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:
Post a Comment