락 세션 조회 및 KILL 방법

★락 걸린 세션 및 오브젝트 확인
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;

★락 세션 죽이는 SQL문
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;

★위 문장으로 kill이 안될경우 OS에서 해당 PID로 kill
select nvl(s.osuser,s.type) os_user,s.username ora_user,s.sid SID,s.serial#,p.spid OS_pid,s.program
from v$session s, v$process p
where s.paddr = p.addr;

unix,linux
$ kill -9 <PID>
ex> kill -9 14221

window($ORACLE_HOME/bin 에 orakill 사용)
$ orakill <oraSID> <osPID>
ex> orakill oraking 14221