Oracle 常用管理查詢 SQL 收藏：Session、鎖、空間、效能診斷

DBA 日常運維核心查詢集錦

整理實戰高頻管理查詢，涵蓋連線監控、鎖診斷、空間分析、效能排查等核心場景，建議收藏備查。

一、Session 連線監控

-- 所有活躍連線
SELECT * FROM V$SESSION WHERE USERNAME IS NOT NULL;

-- 各用戶連線數統計
SELECT USERNAME, COUNT(*) 
FROM V$SESSION WHERE USERNAME IS NOT NULL 
GROUP BY USERNAME;

-- 當前用戶 Session 詳細資訊
SELECT USERNAME, SID, SERIAL#, STATUS, OSUSER, MACHINE, PROGRAM, LOGON_TIME
FROM V$SESSION WHERE USERNAME = 'YSPJ';

二、鎖與阻塞診斷

-- 被鎖定的物件 (Library Cache Lock)
SELECT * FROM V$DB_OBJECT_CACHE 
WHERE OWNER = '目標用戶' AND LOCKS != '0';

-- 鎖定資源的 Session
SELECT * FROM V$LOCKED_OBJECT;

-- 未提交交易 (隱含鎖持有者)
SELECT * FROM V$TRANSACTION;

-- 綜合阻塞查詢：封鎖者 vs 被封鎖者
SELECT 
  B.SESSION_ID AS BLOCKER_SID,
  B.ORACLE_USERNAME AS BLOCKER_USER,
  W.SESSION_ID AS WAITER_SID,
  W.ORACLE_USERNAME AS WAITER_USER,
  O.OBJECT_NAME,
  DECODE(W.LOCKED_MODE, 
    2, 'Row Share', 3, 'Row Exclusive', 
    4, 'Share', 5, 'Share Row Excl', 6, 'Exclusive') AS LOCK_MODE
FROM V$LOCKED_OBJECT W
JOIN V$LOCKED_OBJECT B ON W.OBJECT_ID = B.OBJECT_ID AND B.LOCKED_MODE = 6
JOIN DBA_OBJECTS O ON O.OBJECT_ID = W.OBJECT_ID
WHERE W.SESSION_ID != B.SESSION_ID;

三、資源與授權監控

-- 當前/歷史峰值 Session 數
SELECT SUM(SESSIONS_CURRENT) CUR_SESSIONS,
       SUM(SESSIONS_HIGHWATER) HIGH_SESSIONS
FROM V$LICENSE;

四、儲存空間分析

-- 表所屬表空間
SELECT TABLESPACE_NAME FROM USER_TABLES WHERE TABLE_NAME = 'BILL_ORG';

-- 資料檔案路徑與大小
SELECT TABLESPACE_NAME, FILE_ID, BYTES/1024/1024 AS SIZE_MB, FILE_NAME
FROM DBA_DATA_FILES ORDER BY FILE_ID;

-- 表佔用空間大小
SELECT SUM(BYTES)/(1024*1024) AS SIZE_MB
FROM USER_SEGMENTS WHERE SEGMENT_NAME = UPPER('BILL_MAIN');

五、物件與程式碼查詢

-- 當前用戶所有表
SELECT * FROM ALL_TABLES WHERE OWNER = 'YSPJ';

-- 存儲過程/函數/套件原始碼
SELECT TEXT FROM ALL_SOURCE 
WHERE OWNER = USER AND NAME = UPPER('PROC_NAME')
ORDER BY LINE;

六、SQL 效能追蹤

-- 共享池中 SQL 文本
SELECT SQL_TEXT FROM V$SQL WHERE SQL_TEXT LIKE '%關鍵字%';

-- 特定 SID 正在執行的 SQL 詳細統計
SELECT COMMAND_TYPE, SQL_TEXT, EXECUTIONS, DISK_READS, BUFFER_GETS, ROWS_PROCESSED
FROM V$SQLAREA
WHERE ADDRESS = (SELECT SQL_ADDRESS FROM V$SESSION WHERE SID = 11);

七、客戶端連線資訊

SELECT 
  SYS_CONTEXT('USERENV','TERMINAL') TERMINAL,
  SYS_CONTEXT('USERENV','HOST') HOST,
  SYS_CONTEXT('USERENV','OS_USER') OS_USER,
  SYS_CONTEXT('USERENV','IP_ADDRESS') IP_ADDRESS
FROM DUAL;

八、殺除 Session 實戰

-- 1. 找出目標 SID, SERIAL#
SELECT SID, SERIAL#, USERNAME FROM V$SESSION WHERE USERNAME = 'YSPJ';

-- 2. 執行殺除 (需 ALTER SYSTEM 權限)
ALTER SYSTEM KILL SESSION 'SID,SERIAL#';

-- 3. 立即終止 (11g+)
ALTER SYSTEM KILL SESSION 'SID,SERIAL#' IMMEDIATE;

實務提醒

• V$ 視圖需 SELECT_CATALOG_ROLE 或 DBA 權限
• 殺除 Session 會觸發交易回滾，大交易可能需較長時間
• 定期清理長時間 INACTIVE Session 釋放資源