Oracle DBA 必讀：高頻疑難雜症 10 大實戰解決方案

前言：從「救火」到「防火」的思維轉變

Oracle 資料庫運行久了，總會遇到各種詭異問題：壞塊、FORM/REPORT 異常、動態查詢、約束管理、歸檔模式切換、表空間擴容、動態顯示域... 本文精選 10 個高頻實戰場景，提供標準化解決腳本與最佳實務，助你從「被動救火」轉向「主動防火」。

案例一：表遭遇壞塊，如何最大程度挽救好塊資料？

標準流程

1. 定位壞塊：DBVERIFY 掃描資料檔，獲取壞塊 ID（假設 1234）。
2. 映射段對象：

   SELECT segment_name, segment_type, block_id, blocks
   FROM dba_extents
   WHERE 1234 BETWEEN block_id AND (block_id + blocks - 1);
   

3. 分類處理：
   • 表段：建立臨時表搶救好塊資料（利用 ROWID 排除壞塊範圍）。
   • 索引段：直接 DROP INDEX ...; CREATE INDEX ...; 重建，無需搶救資料。
4. 搶救腳本：

   CREATE TABLE good_table AS
   SELECT * FROM bad_table
   WHERE rowid NOT IN (
       SELECT /*+ INDEX(bad_table, any_index) */ rowid
       FROM bad_table
       WHERE substr(rowid, 1, 8) = '000004D2'  -- 壞塊相對檔案號+塊號轉十六進位
   );
   

進階建議

• 啟用 DB_BLOCK_CHECKSUM=FULL、DB_LOST_WRITE_PROTECT=TYPICAL 早期發現壞塊。
• 配合 RMAN BLOCKRECOVER 單塊恢復，若有完整備份與歸檔。

案例二：FORM 調用 REPORT 產生的「殭屍後台視窗」如何自動關閉？

Forms/Reports 架構中，REPORT BACKGROUND ENGINE 為共享後台服務，Forms 退出後該視窗殘留。解法：在 RUN_REPORT_OBJECT 參數列表第一個參數加入：

Add_Parameter(pl_id, 'ORACLE_SHUTDOWN', TEXT_PARAMETER, 'Yes');

關鍵：必須為第一個參數，否則無效。此參數指示 Reports Server 作業完成後自動關閉引擎視窗。

案例三：REPORT 如何根據 FORM 傳入條件動態顯示記錄？

三步實現

1. Report 數據模型 → 使用者參數 → 新增 W_CLAUSE (CHAR, 初始值 NULL)。
2. 修改查詢 Q_1：SELECT * FROM dept &&W_CLAUSE。
3. Form 傳參：SET_REPORT_OBJECT_PROPERTY(rep_id, REPORT_OTHER, 'PARAMFORM=NO W_CLAUSE="WHERE dept=1"')。

原理：&&W_CLAUSE 為詞法參數，運行時直接替換 SQL 片段，支援任意複雜 WHERE 子句（含子查詢、函數）。

案例四：如何快速查詢某表的所有約束資訊？

-- 核心約束資訊
SELECT constraint_name, table_name, constraint_type, status, r_owner, r_constraint_name
FROM all_constraints
WHERE table_name = UPPER('YOUR_TABLE') AND owner = UPPER('SCHEMA');

-- 約束包含欄位明細
SELECT ac.constraint_name, acc.column_name, acc.position
FROM all_cons_columns acc
JOIN all_constraints ac ON ac.constraint_name = acc.constraint_name AND ac.owner = acc.owner
WHERE acc.table_name = UPPER('YOUR_TABLE') AND acc.owner = UPPER('SCHEMA')
ORDER BY ac.constraint_name, acc.position;

ALL_CONS_COLUMNS 視圖提供欄位級明細，包含複合索引欄位順序。

案例五：NOARCHIVELOG 切換至 ARCHIVELOG 模式標準作業程序 (SOP)

-- 1. 確認 init.ora / spfile 歸檔目標目錄有效
LOG_ARCHIVE_DEST_1 = 'LOCATION=/u01/app/oracle/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES)'

-- 2. 關閉資料庫 -> 掛載 -> 開啟歸檔 -> 開啟資料庫
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;

-- 3. 驗證
ARCHIVE LOG LIST;
-- 輸出應顯示：Database log mode: Archive Mode, Automatic archival: Enabled

-- 4. 11g+ 多歸檔目的地
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='LOCATION=/u02/app/oracle/arch2' SCOPE=BOTH;

注意：切換前必須完成一次完整冷備份，切換後立即執行熱備份建立基線。

案例六：表空間擴容兩大招式：加檔 vs 擴檔

最佳實務：啟用 AUTOEXTEND ON MAXSIZE UNLIMITED，配合監控告警（使用率 > 85% 觸發擴容工單），實現零人工干預擴容。

案例七：REPORT 動態顯示域：傳入參數即時顯示於報表版面

1. Report 數據模型 → 使用者參數 → 新增 RQ (CHAR)。
2. 版面模型 → 建立顯示域 F_1 → 來源指向 RQ → 設為「不可見」。
3. 版面模型 → 建立文字域 D_1 → 內容輸入 &&F_1 → 調整位置寬度。

運行時，D_1 即顯示 Form 傳入的 RQ 值（如日期、報表標題）。

案例八：如何查詢當前 Session 正在執行的 SQL 完整文本？

SELECT s.sid, s.serial#, s.username, s.machine, s.sql_id, t.sql_text
FROM v$session s
JOIN v$sqltext t ON t.address = s.sql_address AND t.hash_value = s.sql_hash_value
WHERE s.status = 'ACTIVE' AND s.type = 'USER'
ORDER BY s.sid, t.piece;

搭配 DBMS_MONITOR.SESSION_TRACE_ENABLE 可生成 10046 Trace 深度分析。

案例九：如何識別並殺掉長時間阻塞的 Session？

-- 1. 找阻塞鏈
SELECT blocking_session, sid, serial#, seconds_in_wait, event, sql_id
FROM v$session
WHERE blocking_session IS NOT NULL;

-- 2. 獲取完整 SQL
SELECT sql_text FROM v$sql WHERE sql_id = '&SQL_ID';

-- 3. 優雅終止 (先嘗試)
ALTER SYSTEM KILL SESSION 'SID,SERIAL#' IMMEDIATE;

-- 4. 強制終止 (OS 層面)
-- 查詢 SPID
SELECT spid FROM v$process WHERE addr = (SELECT paddr FROM v$session WHERE sid = &SID);
-- OS 執行: kill -9 SPID

原則：優先 IMMEDIATE 讓事務回滾釋放鎖；僅在資源耗盡時用 OS kill。

案例十：定期收集統計資訊自動化排程

BEGIN
  DBMS_SCHEDULER.CREATE_JOB (
    job_name        => 'GATHER_STATS_DAILY',
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'BEGIN DBMS_STATS.GATHER_DATABASE_STATS_OPTIONS( options => ''GATHER AUTO'' ); END;',
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'FREQ=DAILY; BYHOUR=2; BYMINUTE=0; BYSECOND=0',
    enabled         => TRUE,
    comments        => 'Daily auto stats gather for CBO'
  );
END;
/

利用 GATHER AUTO 讓 Oracle 自動判斷哪些物件統計過期，避免全庫掃描浪費資源。

總結

這 10 個場景覆蓋了儲存、備份、應用整合、效能、維護等核心領域。建議將每個解決方案封裝為標準化 Runbook（含腳本、驗證步驟、回滾方案），納入團隊知識庫，新人入職即可上手，資深 DBA 專注架構優化而非重複造輪子。