Information Technology/Oracle Database
[Oracle] How to kill a locked user's session
Patrick Moon
2019. 8. 24. 16:11
-- 01. Check the locked table
SELECT do.object_name, do.owner, do.object_type, do.owner,
vo.xidusn, vo.session_id, vo.locked_mode
FROM
v$locked_object vo , dba_objects do
WHERE vo.object_id = do.object_id ;
-- 02. Find out which table is locked
SELECT A.SID, A.SERIAL#, B.TYPE, C.OBJECT_NAME
FROM V$SESSION A, V$LOCK B, DBA_OBJECTS C
WHERE A.SID=B.SID AND B.ID1=C.OBJECT_ID
AND B.TYPE='TM' AND C.OBJECT_NAME IN ('TableName');
-- 03. Inquire lock user, sql, object
SELECT distinct x.session_id, a.serial#,
d.object_name, a.machine, a.terminal,
a.program, b.address, b.piece, b.sql_text
FROM v$locked_object x, v$session a, v$sqltext b, dba_objects d
WHERE x.session_id = a.sid and
x.object_id = d.object_id and
a.sql_address = b.address
order by b.address,b.piece;
-- 04. Identify the user who is locking
SELECT distinct x.session_id, a.serial#,
d.object_name, a.machine, a.terminal, a.program,
a.logon_time , 'alter system kill session ''' || a.sid || ', ' || a.serial# || ''';'
FROM gv$locked_object x, gv$session a, dba_objects d
WHERE x.session_id = a.sid and x.object_id = d.object_id
order by logon_time;
-- 05. Remove access user
alter system kill session 'session_id,serial#';
-- 06. Analysis of current user
SELECT distinct a.sid, a.serial#,
a.machine, a.terminal, a.program,
b.address, b.piece, b.sql_text
FROM v$session a, v$sqltext b
WHERE a.sql_address = b.address
order by a.sid, a.serial#,b.address,b.piece;
