Oracle DBA 必備 18 類維護 SQL 大全：Session 追蹤、鎖等待、資源消耗、跟蹤文件一鍵查詢

前言：DBA 日常巡檢與故障排查的「瑞士軍刀」

整理 18 類高頻維護查詢，涵蓋 Session 識別、OS 進程關聯、SQL 文本提取、物件鎖定、資源消耗、阻塞鏈、等待事件、跟蹤文件定位等核心場景。所有查詢經實戰驗證，可直接收藏作為巡檢腳本庫。

1. 當前 Session 識別：SID、SERIAL#、AUDSID

SELECT SID, SERIAL# 
FROM V$SESSION 
WHERE AUDSID = SYS_CONTEXT('USERENV','SESSIONID');

2. Session 對應 OS 進程 ID（含背景進程）

SELECT p.SPID "OS Thread", b.NAME "Name-User", s.PROGRAM, s.SID, s.SERIAL#,
       s.OSUSER, s.MACHINE
FROM V$PROCESS p, V$SESSION s, V$BGPROCESS b
WHERE p.ADDR = s.PADDR
  AND p.ADDR = b.PADDR
  AND (s.SID = &1 OR p.SPID = &1)
UNION ALL
SELECT p.SPID, s.USERNAME, s.PROGRAM, s.SID, s.SERIAL#,
       s.OSUSER, s.MACHINE
FROM V$PROCESS p, V$SESSION s
WHERE p.ADDR = s.PADDR
  AND (s.SID = &1 OR p.SPID = &1)
  AND s.USERNAME IS NOT NULL;

3. 依 SID 查看正在執行的 SQL 文本

SELECT /*+ PUSH_SUBQ */
       COMMAND_TYPE, SQL_TEXT, SHARABLE_MEM, PERSISTENT_MEM, RUNTIME_MEM, SORTS,
       VERSION_COUNT, LOADED_VERSIONS, OPEN_VERSIONS, USERS_OPENING, EXECUTIONS,
       USERS_EXECUTING, LOADS, FIRST_LOAD_TIME, INVALIDATIONS, PARSE_CALLS,
       DISK_READS, BUFFER_GETS, ROWS_PROCESSED, SYSDATE START_TIME,
       SYSDATE FINISH_TIME, '>' || ADDRESS SQL_ADDRESS, 'N' STATUS
FROM V$SQLAREA
WHERE ADDRESS = (SELECT SQL_ADDRESS FROM V$SESSION WHERE SID = &SID);

4. 查詢物件被哪些 Session 佔用

SELECT p.SPID, s.SID, s.SERIAL# SERIAL_NUM, s.USERNAME USER_NAME,
       a.TYPE OBJECT_TYPE, s.OSUSER OS_USER_NAME, a.OWNER,
       a.OBJECT OBJECT_NAME,
       DECODE(SIGN(48 - COMMAND), 1, TO_CHAR(COMMAND), 'Action Code #' || TO_CHAR(COMMAND)) ACTION,
       p.PROGRAM ORACLE_PROCESS, s.TERMINAL TERMINAL, s.PROGRAM PROGRAM,
       s.STATUS SESSION_STATUS
FROM V$SESSION s, V$ACCESS a, V$PROCESS p
WHERE s.PADDR = p.ADDR
  AND s.TYPE = 'USER'
  AND a.SID = s.SID
  AND a.OBJECT = '&OBJ'
ORDER BY s.USERNAME, s.OSUSER;

5. 列出所有用戶連接

SELECT s.OSUSER OS_USER_NAME,
       DECODE(SIGN(48 - COMMAND), 1, TO_CHAR(COMMAND), 'Action Code #' || TO_CHAR(COMMAND)) ACTION,
       p.PROGRAM ORACLE_PROGRAM, s.STATUS SESSION_STATUS, s.TERMINAL TERMINAL,
       s.PROGRAM PROGRAM, s.USERNAME USER_NAME,
       s.FIXED_TABLE_SEQUENCE ACTIVITY_METER, '' QUERY, 0 MEMORY,
       0 MAX_MEMORY, 0 CPU_USAGE, s.SID, s.SERIAL# SERIAL_NUM
FROM V$SESSION s, V$PROCESS p
WHERE s.PADDR = p.ADDR
  AND s.TYPE = 'USER'
ORDER BY s.USERNAME, s.OSUSER;

6. 依 SID 查看 Session 資源消耗統計

SELECT n.NAME, v.VALUE, n.CLASS, n.STATISTIC#
FROM V$STATNAME n, V$SESSTAT v
WHERE v.SID = &SID
  AND v.STATISTIC# = n.STATISTIC#
ORDER BY n.CLASS, n.STATISTIC#;

7. Top Session：資源消耗排序（預設邏輯讀）

SELECT s.SCHEMANAME SCHEMA_NAME,
       DECODE(SIGN(48 - COMMAND), 1, TO_CHAR(COMMAND), 'Action Code #' || TO_CHAR(COMMAND)) ACTION,
       s.STATUS SESSION_STATUS, s.OSUSER OS_USER_NAME, s.SID, p.SPID,
       s.SERIAL# SERIAL_NUM, NVL(s.USERNAME,'[Oracle process]') USER_NAME,
       s.TERMINAL TERMINAL, s.PROGRAM PROGRAM, st.VALUE CRITERIA_VALUE
FROM V$SESSTAT st, V$SESSION s, V$PROCESS p
WHERE st.SID = s.SID
  AND st.STATISTIC# = 38  -- 38 = session logical reads (可改 12=cpu, 11=physical reads)
  AND ('ALL' = 'ALL' OR s.STATUS = 'ALL')
  AND p.ADDR = s.PADDR
ORDER BY st.VALUE DESC, p.SPID ASC, s.USERNAME ASC, s.OSUSER ASC;

8. 鎖情況詳查：類型、模式、物件關聯

SELECT /*+ RULE */
       ls.OSUSER OS_USER_NAME, ls.USERNAME USER_NAME,
       DECODE(ls.TYPE, 'RW','Row wait enqueue lock','TM','DML enqueue lock',
              'TX','Transaction enqueue lock','UL','User supplied lock') LOCK_TYPE,
       o.OBJECT_NAME OBJECT,
       DECODE(ls.LMODE, 1,NULL,2,'Row Share',3,'Row Exclusive',
              4,'Share',5,'Share Row Exclusive',6,'Exclusive',NULL) LOCK_MODE,
       o.OWNER, ls.SID, ls.SERIAL# SERIAL_NUM, ls.ID1, ls.ID2
FROM SYS.DBA_OBJECTS o,
     (SELECT s.OSUSER, s.USERNAME, l.TYPE, l.LMODE, s.SID, s.SERIAL#, l.ID1, l.ID2
      FROM V$SESSION s, V$LOCK l WHERE s.SID = l.SID) ls
WHERE o.OBJECT_ID = ls.ID1
  AND o.OWNER <> 'SYS'
ORDER BY o.OWNER, o.OBJECT_NAME;

9. 等待事件統計：類別與計數

SELECT ws.CLASS, ws.COUNT "COUNT", SUM(ss.VALUE) SUM_VALUE
FROM V$WAITSTAT ws, V$SYSSTAT ss
WHERE ss.NAME IN ('db block gets','consistent gets')
GROUP BY ws.CLASS, ws.COUNT;

10. Process/Session 狀態對應

SELECT p.PID, p.SPID, s.PROGRAM, s.SID, s.SERIAL#
FROM V$PROCESS p, V$SESSION s
WHERE s.PADDR = p.ADDR;

11. 10g+ 直接查阻塞者：BLOCKING_SESSION

SELECT SID, USERNAME, EVENT, BLOCKING_SESSION, SECONDS_IN_WAIT, WAIT_TIME
FROM V$SESSION
WHERE STATE = 'WAITING'
  AND WAIT_CLASS != 'Idle';

12. 會話阻塞鏈：遞歸縮進顯示

COL USER_NAME FOR A32
SELECT /*+ RULE */
       LPAD(' ', DECODE(l.XIDUSN,0,3,0)) || l.ORACLE_USERNAME USER_NAME,
       o.OWNER, o.OBJECT_NAME, s.SID, s.SERIAL#
FROM V$LOCKED_OBJECT l, DBA_OBJECTS o, V$SESSION s
WHERE l.OBJECT_ID = o.OBJECT_ID
  AND l.SESSION_ID = s.SID
ORDER BY o.OBJECT_ID, l.XIDUSN DESC;

COL USERNAME FOR A15
COL LOCK_LEVEL FOR A8
COL OWNER FOR A18
COL OBJECT_NAME FOR A32
SELECT /*+ RULE */
       s.USERNAME,
       DECODE(l.TYPE,'tm','table lock','tx','row lock',NULL) LOCK_LEVEL,
       o.OWNER, o.OBJECT_NAME, s.SID, s.SERIAL#
FROM V$SESSION s, V$LOCK l, DBA_OBJECTS o
WHERE l.SID = s.SID
  AND l.ID1 = o.OBJECT_ID(+)
  AND s.USERNAME IS NOT NULL;

13. 會話等待事件與當前等待

-- 歷史等待事件統計
SELECT se.SID, s.USERNAME, se.EVENT, se.TOTAL_WAITS, se.TIME_WAITED, se.AVERAGE_WAIT
FROM V$SESSION s, V$SESSION_EVENT se
WHERE s.USERNAME IS NOT NULL
  AND se.SID = s.SID
  AND s.STATUS = 'ACTIVE'
  AND se.EVENT NOT LIKE '%SQL*Net%'
ORDER BY s.USERNAME;

-- 當前等待
SELECT s.SID, s.USERNAME, sw.EVENT, sw.WAIT_TIME, sw.STATE, sw.SECONDS_IN_WAIT
FROM V$SESSION s, V$SESSION_WAIT sw
WHERE s.USERNAME IS NOT NULL
  AND sw.SID = s.SID
  AND sw.EVENT NOT LIKE '%SQL*Net%'
ORDER BY s.USERNAME;

14. 等待事件對應 File/Block 定位物件

COL EVENT FOR A24
COL P1TEXT FOR A12
COL P2TEXT FOR A12
COL P3TEXT FOR A12
SELECT SID, EVENT, P1TEXT, P1, P2TEXT, P2, P3TEXT, P3
FROM V$SESSION_WAIT
WHERE EVENT NOT LIKE '%SQL%'
  AND EVENT NOT LIKE '%rdbms%'
  AND EVENT NOT LIKE '%mon%'
ORDER BY EVENT;

-- Latch 等待對應
SELECT NAME, WAIT_TIME
FROM V$LATCH l
WHERE EXISTS (
  SELECT 1 FROM (
    SELECT SID, EVENT, P1TEXT, P1, P2TEXT, P2, P3TEXT, P3
    FROM V$SESSION_WAIT
    WHERE EVENT NOT LIKE '%SQL%' AND EVENT NOT LIKE '%rdbms%' AND EVENT NOT LIKE '%mon%'
  ) x WHERE x.P1 = l.LATCH#);

15. 由 File/Block 反查物件

COL OWNER FOR A18
COL SEGMENT_NAME FOR A32
COL SEGMENT_TYPE FOR A32
SELECT OWNER, SEGMENT_NAME, SEGMENT_TYPE
FROM DBA_EXTENTS
WHERE FILE_ID = &FILE_ID
  AND &BLOCK_ID BETWEEN BLOCK_ID AND BLOCK_ID + BLOCKS - 1;

16. 依 OS 進程 ID 找 Session 並開啟/關閉 SQL Trace

-- 1. 找 SID, SERIAL#
SELECT s.SID, s.SERIAL#
FROM V$SESSION s, V$PROCESS p
WHERE s.PADDR = p.ADDR
  AND p.SPID = &1;

-- 2. 開啟 Trace
EXEC DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(&SID, &SERIAL#, TRUE);

-- 3. 關閉 Trace
EXEC DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(&SID, &SERIAL#, FALSE);

17. 當前 Session 的 Trace 文件路徑

SELECT p1.VALUE || '/' || p2.VALUE || '_ora_' || p.SPID || '.ora' FILENAME
FROM V$PROCESS p, V$SESSION s, V$PARAMETER p1, V$PARAMETER p2
WHERE p1.NAME = 'user_dump_dest'
  AND p2.NAME = 'instance_name'
  AND p.ADDR = s.PADDR
  AND s.AUDSID = USERENV('SESSIONID')
  AND p.BACKGROUND IS NULL
  AND INSTR(p.PROGRAM, 'CJQ') = 0;

18. 鎖定物件總覽

SELECT DO.OBJECT_NAME, LO.SESSION_ID, LO.PROCESS, LO.LOCKED_MODE
FROM V$LOCKED_OBJECT LO, DBA_OBJECTS DO
WHERE LO.OBJECT_ID = DO.OBJECT_ID;

使用建議：封裝為巡檢腳本庫

1. 將上述查詢封裝為 Shell/Python 腳本，參數化 SID/SPID/OBJ/FILE_ID
2. 建立別名：alias lockchk='sqlplus -s / as sysdba @/scripts/lock_check.sql'
3. 巡檢自動化：排程跑 Top Session、長等待、鎖統計，異常郵件告警
4. 權限最小化：建立監控用戶僅授 SELECT_CATALOG_ROLE、EXECUTE ON DBMS_SYSTEM

總結

這 18 類查詢覆蓋了 DBA 90% 的日常排查場景。**熟練掌握 V$SESSION、V$PROCESS、V$LOCK、V$SQLAREA、V$SESSTAT、DBA_OBJECTS、DBA_EXTENTS 的關聯關係**，能在故障發生第一時間定位根因。建議每位 DBA 將常用查詢整理為個人「工具箱」，定期演練模擬故障排查，肌肉記憶勝過文檔查閱。