Oracle DBA 常用維護指令彙整

前言

日常 DBA 作業涵蓋死鎖排查、資料檔狀態監控、排程作業檢查、壞塊處理等。本文彙整實戰常用的 SQL 指令與處理流程，供快速參考。

一、死鎖偵測與解除

1. 查詢被封鎖的會話與 SQL

-- 方法 A：v$locked_object + v$session + v$sql
SELECT s.username, s.sid, s.serial#, s.machine, s.program,
       l.object_id, o.object_name, o.object_type,
       sq.sql_text
FROM v$locked_object l
JOIN v$session s ON l.session_id = s.sid
JOIN dba_objects o ON l.object_id = o.object_id
LEFT JOIN v$sql sq ON s.sql_id = sq.sql_id
WHERE s.status = 'ACTIVE';

-- 方法 B：直接找出持有鎖與等待鎖的會話
SELECT DECODE(request, 0, 'Holder: ', 'Waiter: ') || sid AS sess,
       id1, id2, lmode, request, type
FROM v$lock
WHERE (id1, id2, type) IN (
    SELECT id1, id2, type FROM v$lock WHERE request > 0
)
ORDER BY id1, request;

2. 判斷是否為死鎖

SELECT username, lockwait, status, machine, program
FROM v$session
WHERE sid IN (SELECT session_id FROM v$locked_object);

• lockwait 有值 = 正在等待鎖。
• status = ACTIVE 且有 lockwait = 可能為死鎖鏈一環。

3. 解除死鎖 (依序嘗試)

-- 步驟 1：標記會話為 KILL (正常交易回滾)
ALTER SYSTEM KILL SESSION 'sid,serial#';

-- 步驟 2：若標記後仍未釋放 (狀態為 KILLED)，強制終結 OS 進程
-- 先查詢對應 OS 進程號 (SPID)
SELECT s.sid, p.spid
FROM v$session s, v$process p
WHERE s.sid = :target_sid AND s.paddr = p.addr;

-- Windows: orakill SID spid
-- Linux/Unix: kill -9 spid

注意： ALTER SYSTEM KILL SESSION 只標記，實際終結需等待回滾完成。緊急情況下需 OS 層級 kill -9，但可能導致資料不一致，極不建議常態化使用。

二、資料檔狀態檢查

SELECT file_name, tablespace_name, status, 
       ROUND(bytes/1024/1024,2) AS size_mb,
       autoextensible, maxbytes
FROM dba_data_files
ORDER BY tablespace_name, file_id;

• STATUS 應為 AVAILABLE。若為 RECOVER、OFFLINE、INVALID 需立即處理。
• 搭配 v$datafile_header 檢查檔案頭 SCN、檢查點一致性。

三、排程作業 監控

SELECT job, log_user, what, last_date, next_date, 
       interval, failures, broken
FROM dba_jobs
WHERE failures > 0 OR broken = 'Y';

• failures > 0：連續失敗次數，達 16 次自動標記 broken = 'Y' 停止執行。
• 檢查 last_date 是否符合預期排程時間。
• 11g+ 建議改用 DBMS_SCHEDULER (查詢 dba_scheduler_jobs、dba_scheduler_job_run_details)。

四、壞塊處理流程

1. 識別壞塊資訊 (Alert Log)

ORA-01578: ORACLE data block corrupted (file # 7, block # 12345)
ORA-01110: data file 7: '/oradata/users01.dbf'

2. 定位受影響物件

SELECT tablespace_name, segment_type, owner, segment_name
FROM dba_extents
WHERE file_id = 7
  AND 12345 BETWEEN block_id AND block_id + blocks - 1;

3. 依物件類型決定修復策略

4. DBMS_REPAIR 搶救流程

-- 步驟 1：標記跳過壞塊
EXEC DBMS_REPAIR.SKIP_CORRUPT_BLOCKS('SCHEMA', 'TABLE_NAME');

-- 步驟 2：搶救正常資料到新表
CREATE TABLE table_bak AS SELECT * FROM table_name;

-- 步驟 3：刪除原表、重命名
DROP TABLE table_name;
ALTER TABLE table_bak RENAME TO table_name;

-- 步驟 4：重建索引、授權、同義詞等

五、其他實用檢查指令

六、緊急處理原則

1. 先觀察再動作：確認影響範圍、業務優先級。
2. 保留證據：Alert Log、Trace File、SQL Text、執行計畫。
3. 最小影響原則：優先 KILL 阻塞會話、重建索引、搶救資料，避免整庫重啟。
4. 事後複盤：根因分析 (RCA)、修正預防措施、更新 Runbook。

提示：將上述常用查詢建立為 SQL*Plus 腳本或 Shell 腳本 (如 check_locks.sh、check_badblocks.sh)，納入每日巡檢自動化，異常主動告警。