다른 명령
락 처리 절차
락 모니터링
락 모니터링 뷰 생성
-- DROP VIEW RTIS_DBA.VW_DBA_LOCKS; CREATE OR REPLACE FORCE VIEW VW_DBA_LOCKS ( LVL, LCK_HOLD, LCK_WAIT, USERNAME, OSUSER, STATUS, TYPE, LOCK_TM, HOLD, OBJECT, ID1, ID2, REQUEST, EVENT, MACHINE, MODULE, PROGRAM, SQL_ID, BLOCKING_SESSION, CTIME, OBJ#, TYPE_NM, ID1_TAG, ID2_TAG, SID, SERIAL#, CMD_KILL ) BEQUEATH DEFINER AS SELECT /*+ ordered */ LEVEL lvl, NVL (s.blocking_session, s.sid) lck_hold, CASE WHEN s.blocking_session IS NOT NULL THEN s.sid END lck_wait, s.username, s.osuser, s.status, l.TYPE TYPE, fn_sec_to_time (l.ctime) lock_tm, DECODE (lmode, 0, 'NONE', 1, 'NULL', 2, 'row-S(SS)', 3, 'row-X(SX)', 4, 'share(S)', 5, 'S/Row-X(SSX)', 6, 'exclusive(X)') HOLD, o.name object --, decode(l.id2, 0, null, trunc(l.id1 / power(2,16))) undo# --, decode(l.id2, 0, null, bitand(l.id1, to_number('ffff', 'xxxx')) + 0 ) slot# , l.id1, l.id2, DECODE (l.request, 0, 'NONE', 1, 'NULL', 2, 'row-S(SS)', 3, 'row-X(SX)', 4, 'share(S)', 5, 'S/Row-X(SSX)', 6, 'exclusive(X)') REQUEST, s.event, s.machine, s.module, s.program, s.sql_id, s.blocking_session, l.ctime, o.obj#, t.name TYPE_NM, t.id1_tag, t.id2_tag, s.sid, s.serial#, 'ALTER SYTEM KILL SESSION ''' || TO_CHAR (S.SID) || ',' || TO_CHAR (S.SERIAL#) || ''';' CMD_KILL FROM v$lock l, v$session s, sys.obj$ o, v$lock_type t WHERE 1 = 1 AND s.TYPE != 'BACKGROUND' -- and s.username = 'EP_APP' -- and s.sid = 4179 AND l.sid = s.sid AND l.TYPE IN ('TM', 'TX', 'UL') AND o.obj#(+) = DECODE (L.ID2, 0, L.ID1, -1) AND t.TYPE = l.TYPE CONNECT BY PRIOR s.sid = s.blocking_session AND PRIOR l.id1 = l.id1 AND PRIOR l.id2 = l.id2 START WITH s.blocking_session IS NULL ORDER SIBLINGS BY lock_tm DESC, lck_hold --order by lck_hold, l.ctime desc, l.sid;; ; COMMENT ON TABLE RTIS_DBA.VW_DBA_LOCKS IS 'DBA_락_확인'; COMMENT ON COLUMN RTIS_DBA.VW_DBA_LOCKS.LVL IS '레벨';
lock 확인
lock 걸린 SERIAL_NO,SESSION_ID, OBJECT
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;
락 걸린 세션 과 락을 잡은 세션
- RAC 환경
SELECT blocking_session, blocked_session, script FROM (SELECT DISTINCT s1.username || '@' || s1.machine || ' ( INST=' || s1.inst_id || ' SID=' || s1.sid || ' ET=' || s1.last_call_et || 'sn. STATUS=' || s1.status || ' EVENT=' || s1.event || ' ACTION= ' || s1.action || ' PROGRAM=' || s1.program || ' MODULE=' || s1.module || ')' blocking_session , s2.username || '@' || s2.machine || ' ( INST=' || s2.inst_id || ' SID=' || s2.sid || ' ET=' || s2.last_call_et || 'sn. STATUS=' || s2.status || ' EVENT=' || s2.event || ' ACTION= ' || s2.action || ' PROGRAM=' || s2.program || ' MODULE=' || s2.module || ')' blocked_session , DECODE (s1.TYPE , 'USER', 'alter system kill session ''' || s1.sid || ',' || s1.serial# || ',@' || s1.inst_id || ''' immediate;' , NULL) script , COUNT (*) OVER (PARTITION BY s1.inst_id, s1.sid) blocked_cnt FROM gv$lock l1 , gv$session s1 , gv$lock l2 , gv$session s2 WHERE s1.sid = l1.sid AND s2.sid = l2.sid AND s1.inst_id = l1.inst_id AND s2.inst_id = l2.inst_id AND l1.block > 0 AND l2.request > 0 AND l1.id1 = l2.id1 AND l1.id2 = l2.id2) ORDER BY blocked_cnt DESC;
- 락 owner(가해자) 0 , 락 waiter(피해자) 6
SELECT S.USERNAME, L.SID, L.ID1, TRUNC(L.ID1 / POWER(2, 16)) RBS, BITAND(L.ID1, TO_NUMBER('ffff', 'xxxx')) + 0 SLOT, L.ID2 SEQ, L.LMODE, L.REQUEST FROM V$LOCK L, V$SESSION S WHERE L.SID = S.SID AND L.TYPE = 'TX' AND S.USERNAME = USER; @ REQUEST 0 : 락을 보유 @ REQUEST 6 : 락으로 인해 대기중인 상태
락 경합 사항 보기
- V$LOCK 셀프조인 모니터링
SELECT (SELECT USERNAME FROM V$SESSION WHERE SID = A.SID) BLOCKER, A.SID, ' is blocking ', (SELECT USERNAME FROM V$SESSION WHERE SID = B.SID) BLOCKER, B.SID FROM V$LOCK A, V$LOCK B WHERE A.ID1 = B.ID1 AND A.ID2 = B.ID2 AND A.BLOCK = 1 -- Lock을 잡고 있는 세션 AND B.REQUEST > 0 ; -- Lock으로 인해 대기하는 세션
2. session_id를 *SESSION_ID*에 넣어 락 sql 찾기
-- lock 걸린 sql
SELECT b.username username ,c.SID AS session_id ,c.owner object_owner ,c.OBJECT OBJECT ,b.lockwait ,a.sql_text SQL ,piece FROM v$sqltext a , v$session b , v$access c WHERE a.address = b.sql_address AND a.hash_value = b.sql_hash_value AND b.SID = c.SID AND c.owner != 'SYS' and c.SID = '&SESSION_ID' ORDER BY b.username, c.SID, c.owner, c.OBJECT, piece;
세션 kill/discount/OS process kill
- SESSION_ID*,*SERIAL_NO* 부분에 위 1번 쿼리에서 확인한 session_id,serial_no을 사용하여 lock 걸린 session을 kill 한다.
- lock 걸린 SESSION kill
alter system kill session 'SESSION_ID ,SERIAL_NO[,@1]' ; --@1안 rac일경우 1번 인스턴스
- lock SESSION discount
alter system discount session 'SESSION_ID ,SERIAL_NO[,@1]' immediate; --@1안 rac일경우 1번 인스턴스
- OS Process Kill
3번의 alter system kill session을 사용하여도 죽지 않는 경우 아래 쿼리를 사용하여 PROCESS ID를 확인하여 unix에서 직접 PROCESS를 kill한다. * 주의!!! 실수하기 쉽고 위험한 방법으로 lock걸린 내용을 정확히 알지 못할 경우 kill하지 말것!!!
-- lock 걸린 PROCESS ID 찾기
SELECT DISTINCT s.username "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'
-- unix에 로그인 하여 process kill
kill -9 프로세스아이디
락 걸린 테이블 찾아서 kill
-- 1) 헤당 테이블 찾기 SELECT * FROM v$lock WHERE id1 = (SELECT object_id FROM all_objects WHERE owner ='오너명' AND object_name ='테이블명' ) -- 2) 죽이기 SELECT * FROM v$session WHERE sid = 찾은 sid값