락걸린 세션 Kill

>>락 확인
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',6, 'LOCK', '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'
AND A.USERNAME = 'USER_NAME';
 
* 사용자 아이디는 대문자
 
SELECT SUBSTR(C.OBJECT_NAME,1,20),A.SID,A.SERIAL#
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';
 
>>락 해제
ALTER SYSTEM KILL SESSION '119, 16917'; --SID, SERIAL#
 
### Lock 확인 쿼리
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 ;
 
####  어떤 object에 어떤 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;

#### session 확인
SELECT * FROM V$SESSION WHERE STATUD = 'ACTIVE'
 
#### cursor 확인
V$OPEN_CURSOR
 
#### 테이블의 lock 확인
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 ('<??????????>');
 

/*******************************************************************************
* LOCK 관련
*******************************************************************************/

--V$LOCK 을 사용한 잠금 경합 모니터링
SELECT S.USERNAME, S.SID, S.SERIAL#, S.LOGON_TIME,
    DECODE(L.TYPE, 'TM', 'TABLE LOCK',
          'TX', 'ROW LOCK',
       NULL) "LOCK LEVEL",
    O.OWNER, O.OBJECT_NAME, O.OBJECT_TYPE
FROM V$SESSION S, V$LOCK L, DBA_OBJECTS O
WHERE S.SID = L.SID
AND O.OBJECT_ID = L.ID1
AND S.USERNAME IS NOT NULL   

--락이 걸린 세션 자세히 알아보기

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'

--락이 걸린 세션 간단히 알아보기

SELECT A.SID, A.SERIAL#, B.TYPE, C.OBJECT_NAME, A.PROGRAM, A.LOCKWAIT,
       A.LOGON_TIME, A.PROCESS, A.OSUSER, A.TERMINAL
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';
SELECT A.SID, A.SERIAL#, A.USERNAME, A.PROCESS, B.OBJECT_NAME
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'

--락이 걸린 세션을 찾아 내어 세션을 죽이려고 해도 죽지 않는 경우
--아래 쿼리문으로 OS단의 PROCESS ID를 찾아내어 OS에서 죽인다
--kill -9 프로세스아이디

SELECT SUBSTR(S.USERNAME,1,11) "ORACLE USER", P.PID "PROCESS ID",
S.SID "SESSION ID", S.SERIAL#, OSUSER "OS USER",
P.SPID "PROC SPID",S.PROCESS "SESS SPID", S.LOCKWAIT "LOCK WAIT"
FROM V$PROCESS P, V$SESSION S, V$ACCESS A
WHERE A.SID=S.SID AND
P.ADDR=S.PADDR AND
S.USERNAME != 'SYS'

--위 쿼리문의 결과가 있다면 락이 걸린 세션이 있다는것이므로 아래의 쿼리문으로 세션을 죽인다

ALTER SYSTEM KILL SESSION '11,39061'