lock 걸린 자료 찾기 및 삭제

1. lock 걸린 잡 찾기

select        nvl(S.USERNAME,'Internal') username,
                 nvl(S.TERMINAL,'None') terminal,
                 L.SID||','||S.SERIAL# Kill,
                U1.NAME||'.'||substr(T1.NAME,1,20) tab,
                decode(L.LMODE,1,'No lock',
                2,'Row Share',
                3,'Row Exclusive',
                4,'Share',
                5,'Share Row Exclusive',
                6,'Exclusive',null) lmode,
                decode(L.REQUEST,1,'No lock',
                2,'Row Share',
                3,'Row Exclusive',
                4,'Share',
                5,'Share Row Exclusive',
                6,'Exclusive',null) request
from        V$lock L, 
              V$SESSION S, 
              SYS.USER$ U1,
              SYS.OBJ$ T1
where     L.SID = S.SID 
   and     T1.OBJ# = decode(L.ID2,0,L.ID1,L.ID2) 
   and     U1.USER# = T1.OWNER#
   and     S.TYPE != 'BACKGROUND'
 order by 1,2,5

 

 

2. lock걸린 잡 삭제

ALTER SYSTEM  KILL SESSION '8, 8589';