오라클 락 조회 및 접속자 쿼리 확인

SELECT
       SUBSTR(SO.OBJECT_NAME,1,12) "오브젝트명"
      , SO.OBJECT_TYPE
   /*   ,DECODE(SO.OBJECT_TYPE
                      , 1, 'INDEX'
                      , 2, 'TABLE'
                      , 3, 'CLUST'
                      , 4, 'VIEW '   6
                      , 5, 'SYNON'
                      , 6, 'SEQUE'
                      , 7, 'PROCE'
                      , 8, 'FUNCT'
                      , 9, 'PACKA'
                      ,11, 'PAC-B'
                      ,12, 'TRIGG'
                      ,TO_CHAR(SO.OBJECT_TYPE,'9999')
             )                 "TYPE"    */
      ,SUBSTR(VS.OSUSER,1,6)     "로그인"
      ,VP.TERMINAL               "터미날"
      ,SUBSTR(VP.SPID,1,5)       "UXPID"
      ,SUBSTR(VS.PROCESS,1,6)    "UXPPID"
      ,SUBSTR(VS.USERNAME,1,4)   "USER"
      ,SUBSTR(DECODE(VS.COMMAND,'2','INSERT'
                               ,'3','SELECT'
                               ,'6','UPDATE'
                               ,'7','DELETE'
                               ,VS.COMMAND
             ),1,6)
                                 "명령어"
      ,DECODE(VL.LMODE,1,'NULL',
                       2,'ROW-SHARE',
                       3,'ROW-EXCLUSIVE',
                       4,'SHARE',
                       5,'SHARE-ROW-EXCLU',
                       6,'EXCLU') "LOCK-상태"
      ,'ALTER SYSTEM KILL SESSION '||CHR(39)||VS.SID||','|| VS.SERIAL# ||CHR(39)||';'
  FROM
       V$LOCK    VL
     , V$SESSION VS
     , V$PROCESS VP
     , OBJ       SO
 WHERE VS.PADDR     = VP.ADDR
   AND VL.SID       = VS.SID
 --  AND VS.USERNAME != 'ORACLE8'
   AND VL.TYPE      = 'TM'
   AND SO.OBJECT_ID      = VL.ID1
 ORDER BY 1,2 ;

 

/* 락걸린 테이블 확인 */
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# || ''';' as KILL_CMD
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#’;
alter system kill session '26,6044';  -- '26(세션id),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;

--오라클 환경설정 값 확인(세션, 프로세스)
select * from v$resource_limit;

참조 주소 : http://www.explab.net/?p=384