블로그 이미지
bedbmsguru

Notice

Recent Post

Recent Comment

Recent Trackback

Archive

calendar

1 2
3 4 5 6 7 8 9
10 11 12 13 14 15 16
17 18 19 20 21 22 23
24 25 26 27 28 29 30
31
  • total
  • today
  • yesterday
2019. 6. 20. 17:09 오라클

WITH sessions AS

 (SELECT /*+materialize*/

 sid, blocking_session, row_wait_obj#, sql_id

 FROM v$session) 

SELECT LPAD(' ', LEVEL ) || sid sid, object_name, 

substr(sql_text,1,40) sql_text 

FROM sessions s 

LEFT OUTER JOIN dba_objects 

ON (object_id = row_wait_obj#) 

LEFT OUTER JOIN v$sql 

USING (sql_id) 

WHERE sid IN (SELECT blocking_session FROM sessions) 

OR blocking_session IS NOT NULL 

CONNECT BY PRIOR sid = blocking_session 

START WITH blocking_session IS NULL;

 

https://oracle2amar.wordpress.com/2010/11/29/script-to-find-the-blocking-sqls-during-the-deadlock-situations/

 

Script to find the blocking SQLs during the deadlock situations

select distinct a.sid “waiting sid”, d.sql_text “waiting SQL”, a.ROW_WAIT_OBJ# “locked object”, a.BLOCKING_SESSION “blocking sid”, c.sql_text “…

oracle2amar.wordpress.com

 

--blockin 확인 SQL

 

SELECT 
       holding_session bsession_id, 
      waiting_session wsession_id, 
      b.username busername, 
      a.username wusername, 
      c.lock_type type, 
      mode_held, mode_requested,
      lock_id1, lock_id2 
FROM
sys.v_$session b, sys.dba_waiters c, sys.v_$session a 
WHERE
c.holding_session=b.sid and
c.waiting_session=a.sid

 

http://www.dba-oracle.com/t_find_blocking_sessions.htm

'오라클' 카테고리의 다른 글

ORACLE shrink 작업  (0) 2020.03.02
DBLink(DB Link 생성)  (0) 2019.08.22
Table Size 확인(Index, Lob데이터포함)  (0) 2019.05.10
Oracle 11g 설치 오류 PRVF-0002  (0) 2019.05.02
오라클 Object DDL 스크립트 추출  (0) 2019.03.06
posted by bedbmsguru