Oracle DBA 必備 12 條核心 SQL 查詢：表空間、對象、性能、鎖一網打盡

日常巡檢與故障排查高頻 SQL

1. 表空間名稱與總大小

SELECT t.tablespace_name,
       ROUND(SUM(bytes)/1024/1024,0) AS 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) AS file_size_mb
FROM   dba_data_files
ORDER BY tablespace_name, file_id;

3. 回滾段/Undo 段狀態

SELECT r.segment_name, r.tablespace_name, r.status,
       r.initial_extent/1024 AS init_kb,
       r.next_extent/1024    AS next_kb,
       r.max_extents,
       v.curext AS current_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. Redo 日志文件成員

SELECT member FROM v$logfile;

6. 表空間使用率 (含免費空間)

SELECT a.tablespace_name,
       ROUND(a.bytes/1024/1024,1) AS total_mb,
       ROUND(b.bytes/1024/1024,1) AS used_mb,
       ROUND(c.bytes/1024/1024,1) AS free_mb,
       ROUND(100*b.bytes/a.bytes,1) AS pct_used,
       ROUND(100*c.bytes/a.bytes,1) AS pct_free
FROM   (SELECT tablespace_name, SUM(bytes) bytes FROM dba_data_files GROUP BY tablespace_name) a,
       (SELECT tablespace_name, SUM(bytes) bytes FROM dba_segments GROUP BY tablespace_name) b,
       (SELECT tablespace_name, SUM(bytes) bytes FROM dba_free_space GROUP BY tablespace_name) c
WHERE  a.tablespace_name = b.tablespace_name
AND    a.tablespace_name = c.tablespace_name
ORDER BY pct_used DESC;

7. 數據庫對象統計 (按擁有者/類型/狀態)

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

8. 數據庫版本信息

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

9. 數據庫創建時間與歸檔模式

SELECT created, log_mode FROM v$database;

10. 長運行操作監控 (v$session_longops)

COL username FORMAT A12
COL opname   FORMAT A20
COL progress FORMAT A10
SELECT s.username, s.sid, s.opname,
       ROUND(s.sofar*100/NULLIF(s.totalwork,0),1) || '%' AS progress,
       s.time_remaining, q.sql_text
FROM   v$session_longops s, v$sql q
WHERE  s.time_remaining > 0
AND    s.sql_address = q.address
AND    s.sql_hash_value = q.hash_value;

11. 分區表參數詳細信息

SELECT partition_name, high_value, tablespace_name,
       pct_free, pct_used, ini_trans, max_trans,
       initial_extent, next_extent, min_extent, max_extent,
       num_rows, blocks, avg_row_len, last_analyzed
FROM   dba_tab_partitions
WHERE  table_owner = :owner AND table_name = :tname
ORDER BY partition_position;

12. 未提交事務與鎖等待

-- 鎖對象
SELECT * FROM v$locked_object;

-- 活躍事務
SELECT s.sid, s.serial#, s.username, t.start_time, t.used_ublk, t.used_urec
FROM   v$session s, v$transaction t
WHERE  s.taddr = t.addr;

進階技巧：將常用查詢封裝為腳本

創建 dba_check.sql，包含上述所有查詢，巡檢時一鍵運行：

@dba_check > dba_check_$(date +%F).lst