Lock 확인 및 해제

1. 확인 방법
SELECT DISTINCT t1.session_id AS session_id
   ,t2.serial# AS serial_no
   ,t1.os_user_name AS os_user_name
   ,t1.oracle_username AS oracle_username
   ,t2.status AS status
   ,t3.object_name
   ,DECODE( locked_mode
           ,2, 'ROW SHARE'
           ,3, 'ROW EXCLUSIVE'
           ,4, 'SHARE'
           ,5, 'SHARE ROW EXCLUSIVE'
           ,6, 'EXCLUSIVE'
           ,'UNKNOWN'
          ) lock_mode
FROM v$locked_object t1, v$session t2, dba_objects t3
WHERE t1.session_id = t2.SID
AND t1.object_id = t3.object_id;

2. 확인 방법

select a.sid, a.serial#,a.username,a.process,b.object_name,
decode(c.lmode,2,'RS',3,'RX',4,'S',5,'SRX',8,'X','NO') "TABLE LOCK",
decode (a.command,2,'INSERT',3,'SELECT',6,'UPDATE',7,'DELETE',12,'DROP TABLE',26,'LOCK TABLE','UNknown') "SQL",
decode(a.lockwait, NULL,'NO wait','Wait') "STATUS"
from v$session a,dba_objects b, v$lock c
where a.sid=c.sid and b.object_id=c.id1
and c.type='TM'
;

3. kill 하기

ALTER SYSTEM KILL SESSION s.sid,s.serial#;
ALTER SYSTEM KILL SESSION '121,21';