Oracle 鎖表與進程查詢全攻略：快速定位並解除阻塞

資料庫鎖爭用是影響效能與並發的常見問題。掌握如何快速查詢被鎖定的表、關聯的 Session 以及背後的作業系統進程，並具備安全釋放鎖定的能力，是 DBA 與開發人員的必備技能。本文整理了從鎖定物件追蹤到進程終止的完整 SQL 工具箱，並補充實用的空間查詢與資料清理指令。

核心查詢：鎖定物件與 Session 關聯

以下查詢整合了 V$LOCKED_OBJECT、V$SESSION、V$PROCESS 及 ALL_OBJECTS，可一次取得被鎖物件名稱、Session ID、Oracle 使用者、OS 使用者、以及對應的作業系統進程 ID (SPID)：

SELECT p.spid,
       a.serial#,
       c.object_name,
       b.session_id,
       b.oracle_username,
       b.os_user_name
FROM   v$process p,
       v$session a,
       v$locked_object b,
       all_objects c
WHERE  p.addr = a.paddr
AND    a.process = b.process
AND    c.object_id = b.object_id;

關鍵欄位說明：

• SPID：作業系統層面的進程 ID，可用於 OS 層面的 kill -9（最後手段）。
• SERIAL#：搭配 SID 用於 ALTER SYSTEM KILL SESSION。
• OBJECT_NAME：直接定位被鎖定的表或物件。

輔助查詢：檢視所有連線 Session

快速列出目前資料庫所有連線的基本資訊：

SELECT sid, serial#, username, osuser
FROM   v$session;

解除鎖定：終止 Session

確認目標 SID 與 SERIAL# 後，執行：

ALTER SYSTEM KILL SESSION 'sid,serial#';

注意：此操作會導致該 Session 回滾未提交交易，請謹慎評估影響範圍。

實用延伸：空間佔用與資料清理

1. 當前使用者下各 Segment 空間佔用 (MB)

SELECT segment_name, SUM(bytes)/1024/1024 AS size_mb
FROM   user_extents
GROUP  BY segment_name;

2. 全資料庫物件空間佔用 (含 Owner)

SELECT owner, segment_name, SUM(bytes/1024/1024) AS size_mb
FROM   dba_segments
GROUP  BY owner, segment_name;

3. 索引空間佔用細查 (指定表空間與表名模糊比對)

SELECT a.segment_name,
       a.tablespace_name,
       b.table_name,
       a.bytes/1024/1024 AS mbytes,
       a.blocks
FROM   user_segments a, user_indexes b
WHERE  a.segment_name = b.index_name
AND    a.segment_type = 'INDEX'
AND    a.tablespace_name = 'APPINDEX'
AND    b.table_name LIKE '%PREP%'
ORDER  BY table_name, a.bytes/1024/1024 DESC;

4. 移除欄位中的換行符 (CHR(10))

UPDATE ywj_yxglobj
SET    table_name = REPLACE(table_name, CHR(10), '')
WHERE  table_name LIKE 'ACCT_INFO%';

操作建議

1. 優先使用 ALTER SYSTEM KILL SESSION 釋放鎖，避免 OS 層面強制終止導致資料不一致。
2. 定期監控 V$LOCKED_OBJECT 並設定告警，主動發現長時間阻塞。
3. 結合應用端連線池設定，避免長交易持有鎖過久。