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/
--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