오라클에서 락(lock)이 걸려있는 것을 조회하고 해당 Session을 kill하는 방법을 알아본다.
-- 락(lock)이 걸려있는 Object에 대해 조회
SELECT
LOCK_OBJ.OBJECT_ID, DBA_OBJ.OBJECT_NAME, DBA_OBJ.OBJECT_TYPE,
LOCK_OBJ.SESSION_ID, DBA_OBJ.OWNER, LOCK_OBJ.XIDUSN, LOCK_OBJ.SESSION_ID,
DECODE(LOCKED_MODE, 2, 'ROW SHARE', 3, 'ROW EXCLUSIVE', 4, 'SHARE', 5,
'SHARE ROW EXCLUSIVE', 6, 'EXCLUSIVE', 'UNKNOWN') LOCKED_MODE
FROM
V$LOCKED_OBJECT LOCK_OBJ, DBA_OBJECTS DBA_OBJ
WHERE
LOCK_OBJ.OBJECT_ID = DBA_OBJ.OBJECT_ID
-- 특정 User 내에서 조회를 원할 때 추가
AND ORACLE_USERNAME = [user];
OBJECT_ID OBJECT_NAME OBJECT_TYPE SESSION_ID OWNER XIDUSN SESSION_ID LOCKED_MODE
--------------------------------------------------------------------------------------------------------------------------------------------------------
55502 TB_CASH_INFO TABLE 141 BACKEND 1 141 ROW EXCLUSIVE
-- kill 하고자 하는 Session ID, Serial NO 조회하기
SELECT
LOCK_OBJ.SESSION_ID, SESN.SERIAL# AS SERIAL_NO, LOCK_OBJ.OS_USER_NAME,
LOCK_OBJ.ORACLE_USERNAME, SESN.STATUS
FROM
V$LOCKED_OBJECT LOCK_OBJ, V$SESSION SESN
WHERE
LOCK_OBJ.SESSION_ID = SESN.SID
-- 특정 Session ID 내에서 조회를 원할 때 추가
AND LOCK_OBJ.SESSION_ID = [session_id]
SESSION_ID SERIAL_NO OS_USER_NAME ORACLE_USERNAME STATUS
---------------------------------------------------------------------------------------------------------------
141 10372 손님138 BACKEND ACTIVE
-- 특정 Session ID, Serial NO를 kill 하기
ALTER SYSTEM KILL SESSION ['session_id, serial_no' ]
ALTER SYSTEM KILL SESSION '141, 10372'
락이 걸려있는 session을 kill 한다.
-- 락(lock)이 걸려있는 Object에 대해 조회
SELECT
LOCK_OBJ.OBJECT_ID, DBA_OBJ.OBJECT_NAME, DBA_OBJ.OBJECT_TYPE,
LOCK_OBJ.SESSION_ID, DBA_OBJ.OWNER, LOCK_OBJ.XIDUSN, LOCK_OBJ.SESSION_ID,
DECODE(LOCKED_MODE, 2, 'ROW SHARE', 3, 'ROW EXCLUSIVE', 4, 'SHARE', 5,
'SHARE ROW EXCLUSIVE', 6, 'EXCLUSIVE', 'UNKNOWN') LOCKED_MODE
FROM
V$LOCKED_OBJECT LOCK_OBJ, DBA_OBJECTS DBA_OBJ
WHERE
LOCK_OBJ.OBJECT_ID = DBA_OBJ.OBJECT_ID
-- 특정 User 내에서 조회를 원할 때 추가
AND ORACLE_USERNAME = [user];
OBJECT_ID OBJECT_NAME OBJECT_TYPE SESSION_ID OWNER XIDUSN SESSION_ID LOCKED_MODE
--------------------------------------------------------------------------------------------------------------------------------------------------------
55502 TB_CASH_INFO TABLE 141 BACKEND 1 141 ROW EXCLUSIVE
-- kill 하고자 하는 Session ID, Serial NO 조회하기
SELECT
LOCK_OBJ.SESSION_ID, SESN.SERIAL# AS SERIAL_NO, LOCK_OBJ.OS_USER_NAME,
LOCK_OBJ.ORACLE_USERNAME, SESN.STATUS
FROM
V$LOCKED_OBJECT LOCK_OBJ, V$SESSION SESN
WHERE
LOCK_OBJ.SESSION_ID = SESN.SID
-- 특정 Session ID 내에서 조회를 원할 때 추가
AND LOCK_OBJ.SESSION_ID = [session_id]
SESSION_ID SERIAL_NO OS_USER_NAME ORACLE_USERNAME STATUS
---------------------------------------------------------------------------------------------------------------
141 10372 손님138 BACKEND ACTIVE
-- 특정 Session ID, Serial NO를 kill 하기
ALTER SYSTEM KILL SESSION ['session_id, serial_no' ]
ALTER SYSTEM KILL SESSION '141, 10372'
락이 걸려있는 session을 kill 한다.
