[oracle] lock , alter session, 현재 접속자의 SQL 분석

-- LOCK 걸린 테이블 확인

SELECT DO.OBJECT_NAME,
       DO.OWNER,
       DO.OBJECT_TYPE,
       DO.OWNER,
       VO.XIDUSN,
       VO.SESSION_ID,
       VO.LOCKED_MODE
  FROM V$LOCKED_OBJECT VO, DBA_OBJECTS DO
 WHERE VO.OBJECT_ID = DO.OBJECT_ID

-- 해당 페이블에 락이 걸렸는지 확인

SELECT A.SID,
       A.SERIAL#,
       B.TYPE,
       C.OBJECT_NAME
  FROM V$SESSION A, V$LOCK B, DBA_OBJECTS C
 WHERE     A.SID = B.SID
       AND B.ID1 = C.OBJECT_ID
       AND B.TYPE = 'TM'
       AND C.OBJECT_NAME IN ('테이블명')

-- 락 발생 사용자와 SQL , OBJECT 조회

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

-- 락 발생 사용자 확인

  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

-- 접속 사용자 제거

 ALTER SYSTEM KILL SESSION 'SESSION_ID, SERIAL#';
 (ex > alter system kill session '26 , 6044' )

-- 현재 접속자의 SQL 분석

  SELECT DISTINCT A.SID,
                  A.SERIAL#,
                  A.MACHINE,
                  A.TERMINAL,
                  A.PROGRAM,
                  B.ADDRESS,
                  B.PIECE,
                  B.SQL_TEXT
    FROM V$SESSION A, V$SQLTEXT B
   WHERE A.SQL_ADDRESS = B.ADDRESS
ORDER BY A.SID,
         A.SERIAL#,
         B.ADDRESS,
         B.PIECE

결과 >>