[Oracle] 오라클 Lock 찾아서 Kill 하기

Search Lock

SELECT  T1.object_name, DECODE(locked_mode, 2, 'ROW SHARE', 3, 'ROW EXCLUSIVE',  4, 'SHARE', 5, 'SHARE ROW EXCLUSIVE', 6, 'EXCLUSIVE', 'UNKNOWN') lock_mode
FROM  dba_objects T1, v$locked_object T2
WHERE T1.object_id = T2.object_id;

select *
from 
(select * from v$locked_object where oracle_username = 'ESMCONSOLE') aa,
(select object_id,object_name from dba_objects) bb
where aa.object_id = bb.object_id(+)
 
 

Kill Lock

- search serial number 

select 
a.session_id as SESSION_ID,
b.serial# as SERIAL_NO,
a.os_user_name as OS_USER_NAME,
a.oracle_username as ORACLE_USERNAME,
b.status as STATUS
from v$locked_object a, v$session b
where a.session_id = b.sid;

- kill

alter system kill session 'SESSION_ID, SERIAL_