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;
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
'오라클' 카테고리의 다른 글
| 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 |