Oracle 實用技巧精選：無用索引偵測、長運行操作監控、ORA-01555 解決方案

技巧二：監控長時間運行操作進度 (V$SESSION_LONGOPS)

適用場景

• 大表索引建立 (CREATE INDEX)
• 大量資料載入 (INSERT /*+ APPEND */, CTAS)
• 分區維護 (MOVE, SPLIT, EXCHANGE)
• RMAN 備份/恢復

查詢腳本

-- 先取得目標 Session SID
SELECT sid, serial#, username, sql_id FROM v$session WHERE username = 'APP_USER' AND status = 'ACTIVE';

-- 查詢進度 (以 SID=123 為例)
SELECT sid,
       opname,
       target,
       sofar,
       totalwork,
       ROUND(sofar/totalwork*100,2) AS pct_done,
       elapsed_seconds,
       time_remaining,
       start_time,
       last_update_time,
       message
FROM   v$session_longops
WHERE  sid = 123
AND    totalwork > 0
ORDER  BY start_time;

輸出範例解讀

SID OPNAME           TARGET              SOFAR   TOTALWORK  PCT_DONE  ELAPSED  REMAINING  MESSAGE
123 Table Scan       SALES.PK_SALES_ID   732     243260     0.30%     45       14950     Table Scan: SALES.PK_SALES_ID: 732 out of 243260 Blocks done

**注意**：TIME_REMAINING 為估算值，受 I/O 波動影響大，參考即可。

技巧三：SET TRANSACTION USE ROLLBACK SEGMENT 解決 ORA-01555

錯誤本質

ORA-01555: snapshot too old — 查詢需要的 Undo 已被覆寫。**原因**：長查詢 + 高併發 DML + **Undo 表空間過小 / UNDO_RETENTION 過短 / 自動管理下保證不足**。

臨時繞過方案 (指定大 Rollback Segment)

-- 僅限手動 Undo 管理模式 (9i- 或 手動模式)
SET TRANSACTION USE ROLLBACK SEGMENT rb_large;
DELETE FROM huge_table WHERE created < SYSDATE - 365;
COMMIT;
-- 提交後自動釋放指定

根治方案 (自動 Undo 管理 9i+ 預設)

1. 增大 Undo 表空間：ALTER TABLESPACE undotbs ADD DATAFILE ... SIZE 10G AUTOEXTEND ON MAXSIZE 50G;
2. 調大 UNDO_RETENTION：ALTER SYSTEM SET UNDO_RETENTION = 3600 SCOPE=BOTH; (秒，視最長查詢而定)。
3. 啟用 RETENTION GUARANTEE：ALTER TABLESPACE undotbs RETENTION GUARANTEE; — **犧牲 DML 失敗換取查詢不報 1555**，謹慎用於報表專用庫。
4. 避免長查詢與高峰 DML 重疊**：排程錯開、唯讀備庫跑報表 (Active Data Guard)。

結語

三大實用技巧：**索引監控清理無用索引**、**V$SESSION_LONGOPS 可視化長操作進度**、**Undo 管理根治 ORA-01555**。將前兩者納入 **例行巡檢腳本 (每週/每月)**，Undo 參數納入 **容量規劃模型**，讓資料庫健康度可量化、可預測。