LOCKED DATABASE OBJECTS
SELECT l.session_id || ',' || v.serial# sid_serial,
l.ORACLE_USERNAME ora_user,
o.object_name,
o.object_type,
DECODE(l.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(l.locked_mode)
) lock_mode,
o.status,
to_char(o.last_ddl_time, 'dd.mm.yy') last_ddl
FROM dba_objects o, gv$locked_object l, v$session v
WHERE o.object_id = l.object_id
and l.SESSION_ID = v.sid;
select a.sid, a.serial#, OBJECT_NAME , a.* from v$session a, v$locked_object b, dba_objects c where b.object_id = c.object_id and a.sid = b.session_id; ALTER SYSTEM KILL SESSION 'sid,serial#';
SELECT T.*, sa.SQL_TEXT, sa.SQL_FULLTEXT, sa.BIND_DATA
FROM (select c.owner,
c.object_name,
c.object_type,
b.sid,
b.serial#,
b.status,
a.ORACLE_USERNAME,
b.osuser,
b.machine,
DECODE(a.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(a.locked_mode)) alock_mode,
b.STATUS SESSION_STATUS,
b.LOGON_TIME,
b.SQL_HASH_VALUE
from v$locked_object a, v$session b, dba_objects c
where b.sid = a.session_id
and a.object_id = c.object_id) T
left join v$sqlarea sa
ON sa.HASH_VALUE = t.SQL_HASH_VALUE;
select a.sid, a.serial#, OBJECT_NAME, a.LOGON_TIME
from v$session a, v$locked_object b, dba_objects c
where b.object_id = c.object_id
and a.sid = b.session_id;
SELECT SID, SQL_ID, USERNAME, BLOCKING_SESSION
FROM v$session
WHERE BLOCKING_SESSION IS NOT NULL;
SELECT l1.sid || ' is blocking ' || l2.sid blocking_sessions
FROM v$lock l1, v$lock l2
WHERE l1.block = 1
AND l2.request > 0
AND l1.id1 = l2.id1
AND l1.id2 = l2.id2;
SELECT s1.username || '@' || s1.machine || ' ( SID=' || s1.sid ||
' ) is blocking ' || s2.username || '@' || s2.machine || ' ( SID=' ||
s2.sid || ' ) ' AS blocking_status
FROM v$lock l1, v$session s1, v$lock l2, v$session s2
WHERE s1.sid = l1.sid
AND s2.sid = l2.sid
AND l1.BLOCK = 1
AND l2.request > 0
AND l1.id1 = l2.id1
AND l1.id2 = l2.id2;
select * from v$lock where type='TX' and request>0;
select * from v$lock where type='TX' and lmode>0;
Reference:
