KILL SESSION

SELECT SW.SID SID
    , S.SERIAL# SERIAL
    , SUBSTR(RAWTOHEX(SW.P1),1,30) HANDLE
    , SUBSTR(RAWTOHEX(SW.P2),1,30) PIN_ADDR 
FROM V$SESSION_WAIT SW
INNER JOIN V$SESSION S
ON SW.SID = S.SID
WHERE SW.WAIT_TIME=0
AND SW.EVENT LIKE 'library cache pin%'
;
SELECT VSS.STATUS
    , VSS.SID
    , VSS.SERIAL#
    , VSS.USERNAME
    , VSS.OSUSER
    , VSA.SQL_TEXT
    , 'ALTER SYSTEM KILL SESSION ''' || VSS.SID || ',' || VSS.SERIAL# || ''';' KILL_SQL
FROM V$SESSION VSS
INNER JOIN V$SQLAREA VSA
ON VSS.SQL_HASH_VALUE = VSA.HASH_VALUE 
AND VSS.SQL_ADDRESS = VSA.ADDRESS 
WHERE VSS.STATUS = 'ACTIVE'
;
ALTER SYSTEM KILL SESSION 'SID,SERIAL';

ACTIVE 상태의 쿼리중 종료하고 싶은 쿼리를 확인하여

KILL_SQL를 복사해서 사용하면 된다.

library cache pin이벤트가 발생했을때 Lock걸린 것 같을때 확인해보면 된다.