블로그 이미지
bedbmsguru

Notice

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            
  • 36,498total
  • 0today
  • 9yesterday
2019.06.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

posted by bedbmsguru