SELECT 
    o.owner AS "對象所屬用戶",
    o.object_name AS "對象名稱",
    s.sid AS "會話SID",
    s.serial# AS "會話SERIAL#",
    s.username AS "用戶名",
    s.osuser AS "操作系統(tǒng)用戶",
    s.machine AS "客戶端機器名",
    s.program AS "使用的程序",
    l.locked_mode AS "鎖定模式"
FROM 
    v$locked_object l
JOIN 
    dba_objects o ON l.object_id = o.object_id
JOIN 
    v$session s ON l.session_id = s.sid;

WITH locked_objects AS (
    SELECT 
        l.session_id,
        o.owner,
        o.object_name,
        o.object_type,
        l.locked_mode
    FROM 
        v$locked_object l
    JOIN 
        dba_objects o ON l.object_id = o.object_id
),
waiting_sessions AS (
    SELECT 
        s.sid,
        s.serial#,
        s.username,
        s.osuser,
        s.machine,
        s.program,
        s.status,
        s.logon_time,
        s.event,
        s.seconds_in_wait,
        s.wait_class,
        s.blocking_session,
        lo.owner,
        lo.object_name,
        lo.object_type,
        lo.locked_mode
    FROM 
        v$session s
    LEFT JOIN 
        locked_objects lo ON s.blocking_session = lo.session_id
    WHERE 
        s.wait_class != 'Idle' -- 排除空閑會話
        AND s.blocking_session IS NOT NULL -- 只選擇有阻塞會話的記錄
)
SELECT 
    ws.sid AS "等待會話SID",
    ws.serial# AS "等待會話SERIAL#",
    ws.username AS "等待會話用戶名",
    ws.osuser AS "操作系統(tǒng)用戶",
    ws.machine AS "客戶端機器名",
    ws.program AS "使用的程序",
    ws.status AS "會話狀態(tài)",
    ws.logon_time AS "登錄時間",
    ws.event AS "等待事件",
    ws.seconds_in_wait AS "等待時間(秒)",
    ws.wait_class AS "等待類別",
    ws.blocking_session AS "阻塞會話SID",
    lo.session_id AS "持有鎖的會話SID",
    lo.owner AS "對象所屬用戶",
    lo.object_name AS "對象名稱",
    lo.object_type AS "對象類型",
    CASE lo.locked_mode
        WHEN 0 THEN 'None'
        WHEN 1 THEN 'Null (NULL)'
        WHEN 2 THEN 'Row Share (SS)'
        WHEN 3 THEN 'Row Exclusive (SX)'
        WHEN 4 THEN 'Share (S)'
        WHEN 5 THEN 'Share Row Exclusive (SSX)'
        WHEN 6 THEN 'Exclusive (X)'
        ELSE 'Unknown'
    END AS "鎖定模式描述"
FROM 
    waiting_sessions ws
JOIN 
    locked_objects lo ON ws.blocking_session = lo.session_id
ORDER BY 
    ws.blocking_session, ws.sid;

-- 查詢阻塞樹結構
SELECT 
    LPAD(' ', 5 * (LEVEL - 1)) || s.username AS username,
    LPAD(' ', 5 * (LEVEL - 1)) || s.inst_id || ',' || s.sid AS inst_sid,
    s.serial#,
    s.sql_id,
    s.row_wait_obj#,
    s.wait_class,
    s.event,
    s.p1,
    s.p2,
    s.p3,
    s.seconds_in_wait,
    s.blocking_instance || ',' || s.blocking_session AS blocking_inst_sid
FROM 
    gv$session s
WHERE 
    s.blocking_session IS NOT NULL
    OR (s.inst_id || ',' || s.sid) IN (
        SELECT DISTINCT blocking_instance || ',' || blocking_session 
        FROM gv$session
        WHERE blocking_instance IS NOT NULL AND blocking_session IS NOT NULL
    )
START WITH 
    s.blocking_session IS NULL -- 從不被其他會話阻塞的會話開始
CONNECT BY PRIOR 
    (s.inst_id || ',' || s.sid) = (s.blocking_instance || ',' || s.blocking_session)
ORDER SIBLINGS BY 
    s.inst_id, s.sid;

SELECT 
    s.sid AS "會話SID",
    s.serial# AS "會話SERIAL#",
    s.username AS "用戶名",
    s.status AS "會話狀態(tài)",
    s.sql_id AS "SQL_ID",
    q.sql_text AS "SQL語句"
FROM 
    v$session s
LEFT JOIN 
    v$sql q ON s.sql_id = q.sql_id
WHERE 
    s.sid = &sid; -- 替換為實際的會話SID