Tip
2010.05.30 14:46

Lock Check

조회 수 11795 추천 수 0 댓글 0


################################################################################################################
select s.sid as sid,s.serial# as serial#,s.username as username,s.logon_time as logon_time,s.machine as machine,
      decode(l.type,'MR','Media Recovery',
             'RT','Redo Thread',
             'UN','User Name',
             'TX','Transaction',
             'TM','DML',
             'UL','P/L SQL User Block',
             'DX','Distrbuted Xaction',
             'CF','Control File',
             'IS','Instance State',
             'FS','File Set',
             'IR','Instance Recovery',
             'ST','Disk Space Transaction',
             'TS','Temp Segment',
             'IV','Library Cache Invalidation',
             'LS','Log State or Switch',
             'RW','Row Wait',
             'TE','Extend Table',
             'Tt','Temp Table','모름') as lock_type,
       decode(l.lmode,0,'None',
              1,'Null',
              2,'Row-S(SS)',
              3,'Row-X(SX)',
              4,'Share',
              5,'S/Row-X(SSX)',
              6,'Exclusive','모름') as lock_mode,
       decode(l.request,0,'None',
              1,'Null',
              2,'Row-S(SS)',
              3,'Row-X(SX)',
              4,'Share',
              5,'S/Row-X(SSX)',
              6,'Exclusive','모름') as request,
       s.status as status,s.program as program,s.osuser as osuser,q.sql_text as sql_text
from   v$session s, v$lock l, v$sql q
where  l.sid = s.sid
and    s.sql_address = q.address
and    s.sql_hash_value = q.hash_value
order by s.sid; 
###################################################################################################################

set verify off linesize 100

COL TODAY FORMAT A20 NEW_VALUE CURR_TIME
col TODAY NOPRINT
SELECT TO_CHAR(SYSDATE,'YYYY/MM/DD HH24:MI:SS' ) TODAY FROM DUAL;

PROMPT ===========================================
PROMPT ==  LOCK WAIT SESSION STATUS             ==
PROMPT => DATE : [ &CURR_TIME ]   
PROMPT ===========================================

COL WMACHINE FORMAT A14
COL SID      FORMAT 9999
COL SER#     FORMAT 99999
COL PROGRAM  FORMAT A15
COL OWN_OBJECT FORMAT A22
COL SQLCMD   FORMAT A6

SELECT DECODE(B.LOCKWAIT,NULL,' v ',' w>> ')||
       SUBSTR(B.MACHINE,1,12) AS WMACHINE,
       B.PROCESS                  AS PGM_PSS,
       SUBSTR(B.PROGRAM,1,15) AS PROGRAM,
       B.SID,B.SERIAL# AS SER#,
       DECODE(B.COMMAND,0,NULL,2,'INSERT',6,'UPDATE',7,'DELETE',
              B.COMMAND )         AS SQLCMD,
       SUBSTR(DECODE(C.OWNER,NULL,A.TYPE||'..ing',
                   C.OWNER||'.'||C.OBJECT_NAME),1,22 ) AS OWN_OBJECT
FROM V$SESSION B, DBA_OBJECTS C,
 ( SELECT SID, ID1,ID2,TYPE
   FROM V$LOCK A
   WHERE A.TYPE NOT IN ( 'MR','RT' )
   AND       ( A.ID1, A.ID2 )
   IN ( SELECT D.ID1, D.ID2 FROM V$LOCK D
         WHERE D.ID1 = A.ID1 AND D.ID2 = A.ID2
         AND   D.REQUEST > 0  ) ) A
WHERE A.SID = B.SID
AND   B.ROW_WAIT_OBJ# = C.OBJECT_ID (+)
ORDER BY ID1,ID2,WMACHINE
/
===================================================================

 -sid -> pid 확인

select  *  from  v$process
 where  addr  in
            ( select  paddr  from  v$session  where  sid = '&sid' ) ;

 -pid로 sid 확인

select a.sid, a.serial#
from v$session a, v$process b
where a.paddr= b.addr and b.spid=23039;

-sid 로 쿼리확인

select a.sql_text
     from v$sqltext a, v$session b
     where a.address=b.sql_address and
                   a.hash_value=b.sql_hash_value and
                   b.sid=386
             order by a.piece;


select b.schemaname, b.sid, b.serial#, b.machine, a.locked_mode
     , b.program, c.OBJECT_name, c.OBJECT_type, b.status
  from V$LOCKED_OBJECT a, v$session b, ALL_OBJECTS c
 where a.session_id = b.sid
   and a.OBJECT_id = c.OBJECT_id
;


alter system kill session '&sid,&serial'


List of Articles
번호 분류 제목 글쓴이 날짜 조회 수
공지 Q&A Oracle관련 게시물만 Sean 2014.04.09 84920
60 Tip Complete Checklist for Manual Upgrades to 11gR2 [ID 837570.1] 담벼락 2011.03.23 17574
59 Tip NL JOIN에 대하여... 1 고구마 2011.03.21 13176
58 Tip Outer Join의 정확한 이해 file 고구마 2011.03.21 13028
57 Tip 쿼리 튜닝 사례 고구마 2011.03.21 14449
56 Tip windows os 에서 운영중 listener.log 삭제하기 2 file dbkill 2011.02.21 22296
55 Tip partition table에 index 생성시 perfstat 2011.01.28 18169
54 Tip DB계정에 profile설정 perfstat 2011.01.05 14762
53 Tip em 실행시 에러 발생 조치 1 dbkill 2010.12.15 27311
52 Tip NetCA 실행시 VM 관련 에러 대처법 2 송기성 2010.12.09 18207
» Tip Lock Check 승현짱 2010.05.30 11795
50 Tip 11g Newfeature 승현짱 2010.05.30 11477
49 Tip EXPORT/IMPORT 이용한 TABLE과 INDEX 분리하는 방법 고구마 2010.05.24 25472
48 Tip TABLE(INDEX) 다른 TABLESPACE로 옮기기(8i) 고구마 2010.05.24 23009
47 Tip reoder column order 1 유주환 2010.05.19 11394
46 Tip RECOVERY 2 고구마 2010.05.19 19916
45 Tip TEMP TABLESPACE 사용현황 조회하기 1 고구마 2010.05.19 44234
44 Tip 통계복원하기 고구마 2013.05.13 8055
43 Tip 유저별 Segment 개수 및 사이즈 조회 쿼리, n2bfree 2013.03.12 11077
42 Tip online reorg script 담벼락 2013.01.10 10630
41 Tip 오라클11g에서 SE1,SE,EE제품의 옵션비교입니다. file perfstat 2012.03.07 27016
Board Pagination Prev 1 2 3 4 5 6 7 8 Next
/ 8