V$LOCKED_OBJECT
SELECT LO.session_id, LO.oracle_username, LO.os_user_name,
OBJ.object_name , OBJ.owner
FROM V$LOCKED_OBJECT LO,
DBA_OBJECTS OBJ
WHERE LO.object_id = OBJ.object_id;
FROM V$LOCKED_OBJECT LO,
DBA_OBJECTS OBJ
WHERE LO.object_id = OBJ.object_id;
V$LOCKED_OBJECT + V$SESSION
SELECT SESS.sid, SESS.serial#, SESS.schemaname, SESS.machine,
SESS.program, SESS.module, SESS.blocking_session,
LO.os_user_name, OBJ.object_name , OBJ.owner,
SESS.sql_id,
'ALTER SYSTEM KILL SESSION ''' || SESS.sid || ',' || SESS.serial# ||''';' kill_session
FROM V$LOCKED_OBJECT LO,
DBA_OBJECTS OBJ,
V$SESSION SESS
WHERE LO.object_id = OBJ.object_id
AND LO.session_id = SESS.sid;
V$LOCK
SELECT A.sid "blocking session",
B.sid "blocked session" ,
DECODE (A.block,1, A.sid ||' is Blocking '||B.sid,'Not Blocking'),
A.type "Lock Type"
FROM V$LOCK A,
V$LOCK B
WHERE A.sid != B.sid
AND A.id1 = B.id1
AND A.id2 = B.id2
AND A.block = 1
AND B.request > 0;
V$SESSION - lock time
SELECT
sid, serial#, username, machine, program,
blocking_session "blocking_session",
sid "blocked_session",
serial# "blocked_serial#",
seconds_in_wait/60 "wait_time(minutes)"
FROM V$SESSION
WHERE blocking_session IS NOT NULL
ORDER BY blocking_session;
V$SESSION - lock time + lock info
SELECT
ROUND(V_SESS_LOCKED.seconds_in_wait/60) "WAIT_TIME (MIN)",
V_SESS_LOCKED.sid locked_sid,
V_SESS_LOCKED.serial# locked_serial,
V_SESS_LOCKED.username locked_username,
V_SESS_LOCKED.machine locked_machine,
V_SESS_LOCKED.program locked_program,
V_SESS_LOCKED.blocking_session blocking_session,
V_SESS_LOCKED.sql_id locked_sql,
V_SESS_LOCKING.sid locking_session,
V_SESS_LOCKING.serial# locking_serial,
V_SESS_LOCKING.username locking_username,
V_SESS_LOCKING.machine locking_machine,
V_SESS_LOCKING.program locking_program,
V_SESS_LOCKING.sql_id locking_sql
FROM V$SESSION V_SESS_LOCKED,
V$SESSION V_SESS_LOCKING
WHERE V_SESS_LOCKED.blocking_session IS NOT NULL
AND V_SESS_LOCKED.blocking_session = V_SESS_LOCKING.sid
ORDER BY ROUND(V_SESS_LOCKED.seconds_in_wait/60) DESC;
