Oracle DBA 日常維護必備 10 大查詢腳本

整理 10 個高頻維護查詢，涵蓋表空間、資料檔、回滾段、控制檔、日誌檔、空間使用率、物件狀態、版本、建立資訊、長時間運作 SQL 捕捉，可直接納入巡檢腳本或監控系統。

1. 表空間名稱與總大小

SELECT t.tablespace_name,
       ROUND(SUM(bytes/1024/1024),0) ts_size_mb
FROM dba_tablespaces t, dba_data_files d
WHERE t.tablespace_name = d.tablespace_name
GROUP BY t.tablespace_name;

2. 表空間資料檔明細

SELECT tablespace_name, file_id, file_name,
       ROUND(bytes/1024/1024,0) total_mb
FROM dba_data_files
ORDER BY tablespace_name;

3. 回滾段名稱、狀態與儲存參數

SELECT r.segment_name, r.tablespace_name, r.status,
       r.initial_extent/1024 initial_kb,
       r.next_extent/1024 next_kb,
       r.max_extents, v.curext cur_extents
FROM dba_rollback_segs r, v$rollstat v
WHERE r.segment_id = v.usn(+)
ORDER BY r.segment_name;

4. 控制檔路徑

SELECT name FROM v$controlfile;

5. 線上日誌檔路徑

SELECT member FROM v$logfile;

6. 表空間使用率（兩種寫法）

-- 簡易版
SELECT tablespace_name, ROUND(SUM(bytes)/1024/1024,2) free_mb
FROM dba_free_space
GROUP BY tablespace_name;

-- 詳細版（含已用/未用百分比）
SELECT a.tablespace_name,
       a.bytes total_bytes, b.bytes used_bytes, c.bytes free_bytes,
       ROUND(b.bytes*100/a.bytes,2) pct_used,
       ROUND(c.bytes*100/a.bytes,2) pct_free
FROM sys.sm$ts_avail a, sys.sm$ts_used b, sys.sm$ts_free c
WHERE a.tablespace_name = b.tablespace_name
  AND a.tablespace_name = c.tablespace_name;

7. 資料庫物件統計

SELECT owner, object_type, status, COUNT(*)
FROM all_objects
GROUP BY owner, object_type, status;

8. 資料庫版本

SELECT version FROM product_component_version
WHERE SUBSTR(product,1,6) = 'Oracle';

9. 建立日期與歸檔模式

SELECT created, log_mode FROM v$database;

10. 捕捉長時間運作 SQL

COL username FORMAT A12
COL opname FORMAT A16
COL progress FORMAT A8
SELECT username, sid, opname,
       ROUND(sofar*100/totalwork,0) || '%' progress,
       time_remaining, sql_text
FROM v$session_longops, v$sql
WHERE time_remaining <> 0
  AND sql_address = address
  AND sql_hash_value = hash_value;