Oracle DBA 日常巡檢 SQL 工具箱：表空間、回滾段、控制文件、日誌、對象、版本、連接管理全覆蓋

核心巡檢分類與標準 SQL

1. 表空間容量與使用率

-- 1.1 表空間總大小 (MB)
SELECT t.tablespace_name,
       ROUND(SUM(bytes)/1024/1024, 0) AS total_mb
FROM dba_tablespaces t
JOIN dba_data_files d ON t.tablespace_name = d.tablespace_name
GROUP BY t.tablespace_name;

-- 1.2 數據文件詳情
SELECT tablespace_name, file_id, file_name,
       ROUND(bytes/1024/1024, 0) AS file_mb
FROM dba_data_files
ORDER BY tablespace_name, file_id;

-- 1.3 表空間使用率 (含自動擴展最大值)
SELECT a.tablespace_name,
       ROUND(a.max_mb, 0) AS max_mb,
       ROUND(a.used_mb, 0) AS used_mb,
       ROUND(a.free_mb, 0) AS free_mb,
       ROUND(100 * a.used_mb / a.max_mb, 1) AS pct_used
FROM (
  SELECT t.tablespace_name,
         SUM(DECODE(d.autoextensible, 'YES', d.maxbytes, d.bytes))/1024/1024 AS max_mb,
         SUM(d.bytes)/1024/1024 - SUM(f.bytes)/1024/1024 AS used_mb,
         SUM(f.bytes)/1024/1024 AS free_mb
  FROM dba_tablespaces t
  JOIN dba_data_files d ON t.tablespace_name = d.tablespace_name
  LEFT JOIN dba_free_space f ON d.file_id = f.file_id
  GROUP BY t.tablespace_name
) a
ORDER BY pct_used DESC;

2. 回滾段 / UNDO 狀態

SELECT r.segment_name,
       r.tablespace_name,
       r.status,
       r.initial_extent/1024 AS initial_kb,
       r.next_extent/1024 AS next_kb,
       r.max_extents,
       v.curext AS current_extents,
       v.rssize/1024/1024 AS rssize_mb,
       v.hwmsize/1024/1024 AS hwm_mb
FROM dba_rollback_segs r
JOIN v$rollstat v ON r.segment_id = v.usn
ORDER BY r.segment_name;

3. 控制文件與日誌文件

-- 控制文件
SELECT name FROM v$controlfile;

-- 聯機日誌文件
SELECT group#, member, status FROM v$logfile ORDER BY group#;

-- 日誌組狀態
SELECT group#, sequence#, bytes/1024/1024 AS size_mb, status, archived
FROM v$log;

4. 數據庫對象統計

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

5. 數據庫版本與創建信息

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

-- 創建時間、歸檔模式
SELECT created, log_mode FROM v$database;

6. 用戶連接管理

-- 當前會話
SELECT username, sid, serial#, status, machine, program, logon_time
FROM v$session
WHERE username IS NOT NULL
ORDER BY logon_time;

-- 強制斷開會話
ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;

-- 關聯 OS 進程 (Linux/Unix)
SELECT s.sid, s.serial#, s.username, p.spid AS os_pid
FROM v$session s
JOIN v$process p ON s.paddr = p.addr
WHERE s.sid = &target_sid;

-- OS 層面 kill
-- kill -9 

SQL*Plus 實用技巧

常用 DDL 模板

-- 創建表
CREATE TABLE test (
  id   NUMBER PRIMARY KEY,
  name VARCHAR2(50),
  dt   DATE DEFAULT SYSDATE
);

-- 增加列
ALTER TABLE test ADD (age NUMBER(3));

-- 修改列
ALTER TABLE test MODIFY (name VARCHAR2(100));

-- 刪除列
ALTER TABLE test DROP COLUMN age;

-- 截斷表 (釋放空間、重置 HWM、不可回滾)
TRUNCATE TABLE test;

結語

將上述 SQL 封裝為 dba_daily_check.sql、dba_weekly_check.sql 定時任務輸出 HTML/CSV 報告，配合閾值告警（表空間 > 85%、UNDO 活躍事務 > 1 小時、無效對象 > 0），即可構建輕量級自動化巡檢體系。關鍵在於：標準化查詢、自動化輸出、異常即時通知、歷史趨势分析。