您现在的位置：程式碼之家 >> 數據庫知識 > >> Oracle 資料庫 >> Oracle 資料庫相關知識

Oracle DBA 核心巡檢 SQL 大全：表空間、回滾段、控制檔、日誌檔、字元集、SCN、Trace、登入 IP

編輯：Oracle 資料庫相關知識

匯總 Oracle DBA 日常巡檢、效能診斷、故障排查必備的 20+ 核心 SQL，涵蓋儲存、交易、備份、安全、追蹤等面向，每條附帶欄位說明與解讀重點。

1. 表空間名稱與總大小

SELECT t.tablespace_name,
       ROUND(SUM(d.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
ORDER BY total_mb DESC;

2. 表空間資料檔詳細（檔案 ID、路徑、大小）

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

3. 回滾段狀態與大小

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
FROM dba_rollback_segs r
JOIN v$rollstat v ON r.segment_id = v.usn
ORDER BY r.segment_name;

4. 查看特定回滾段正在執行的交易與 SQL

SELECT t.sql_text, r.name AS rollback_segment
FROM v$rollname r
JOIN v$transaction tx ON r.usn = tx.xidusn
JOIN v$session s ON tx.addr = s.taddr
JOIN v$sqltext t ON s.sql_address = t.address AND s.sql_hash_value = t.hash_value
WHERE r.usn = 1;  -- 修改 USN 針對特定回滾段

5. 控制檔與線上日誌檔

-- 控制檔
SELECT * FROM v$controlfile;

-- 線上日誌檔
COL member FORMAT A60
SELECT * FROM v$logfile;

6. 當前 Session 的 SID、SERIAL#、狀態

SELECT sid, serial#, status
FROM v$session
WHERE audsid = USERENV('SESSIONID');

7. 資料庫字元集與語言環境

SELECT USERENV('LANGUAGE') FROM dual;  -- 格式: LANGUAGE_TERRITORY.CHARACTERSET
SELECT * FROM nls_database_parameters WHERE parameter IN ('NLS_CHARACTERSET','NLS_NCHAR_CHARACTERSET');

8. 判斷當前優化器模式（RBO/CBO）

EXPLAIN PLAN FOR SELECT 1 FROM dual;
SELECT DECODE(NVL(position,-1),-1,'RBO',1,'CBO') FROM plan_table WHERE id = 0;

9. 系統當前最大 SCN（System Change Number）

SELECT MAX(ktuxescnw * POWER(2,32) + ktuxescnb) AS current_scn
FROM x$ktuxe;

10. 當前 Session 的 Trace 檔完整路徑

SELECT d.value || '/' || LOWER(RTRIM(i.instance, CHR(0))) || '_ora_' || p.spid || '.trc' AS trace_file
FROM (SELECT p.spid FROM v$mystat m, v$session s, v$process p WHERE m.statistic# = 1 AND s.sid = m.sid AND p.addr = s.paddr) p
CROSS JOIN (SELECT value FROM v$parameter WHERE name = 'user_dump_dest') d
CROSS JOIN (SELECT instance FROM v$thread WHERE thread# = (SELECT TO_NUMBER(value) FROM v$parameter WHERE name = 'thread')) i;

11. 客戶端登入 IP 位址

SELECT SYS_CONTEXT('USERENV','IP_ADDRESS') FROM dual;

12. 建立登入 IP 審計觸發器（生產環境建議部署）

CREATE TABLE session_history (
    username VARCHAR2(30), sid NUMBER, serial# NUMBER, audsid NUMBER,
    osuser VARCHAR2(30), action VARCHAR2(30), logon_time DATE,
    logoff_time DATE, ip_address VARCHAR2(40), terminal VARCHAR2(30),
    machine VARCHAR2(64), program VARCHAR2(64)
);

CREATE OR REPLACE TRIGGER logon_history
AFTER LOGON ON DATABASE
BEGIN
    INSERT INTO session_history
    SELECT username, sid, serial#, audsid, osuser, 'LOGON', SYSDATE, NULL,
           SYS_CONTEXT('USERENV','IP_ADDRESS'), terminal, machine, program
    FROM v$session WHERE audsid = USERENV('SESSIONID');
END;
/

-- 對應登出觸發器更新 logoff_time
CREATE OR REPLACE TRIGGER logoff_history
BEFORE LOGOFF ON DATABASE
BEGIN
    UPDATE session_history
    SET logoff_time = SYSDATE
    WHERE audsid = USERENV('SESSIONID') AND logoff_time IS NULL;
END;
/

13. 當前日期時間格式化

SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS') FROM dual;

14. 所有表空間對應資料檔與 AUTOEXTEND 狀態

SELECT DISTINCT file_name, tablespace_name, autoextensible
FROM dba_data_files
ORDER BY tablespace_name;

15. 表空間使用率（三種寫法任選一）

-- 寫法 1：DBA_FREE_SPACE
SELECT tablespace_name,
       ROUND(SUM(bytes)/1024/1024,2) AS free_mb
FROM dba_free_space
GROUP BY tablespace_name;

-- 寫法 2：SM$ 視圖（需 STATSPACK/AWR）
SELECT a.tablespace_name, a.bytes total_mb, b.bytes used_mb, c.bytes free_mb,
       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;

-- 寫法 3：綜合計算（最通用）
COL tablespace_name FORMAT A18
SELECT d.tablespace_name,
       ROUND(d.total_mb,2) total_mb,
       ROUND(d.total_mb - d.free_mb,2) used_mb,
       ROUND(d.free_mb,2) free_mb,
       ROUND((d.total_mb - d.free_mb)*100/d.total_mb,2) pct_used
FROM (
    SELECT f.tablespace_name,
           SUM(f.bytes)/1024/1024 AS free_mb,
           SUM(df.bytes)/1024/1024 AS total_mb
    FROM dba_free_space f
    RIGHT JOIN dba_data_files df ON f.file_id = df.file_id
    GROUP BY f.tablespace_name
) d
ORDER BY pct_used DESC;

16. 資料檔 HWM（可 Resize 最小值）與檔案頭大小

SELECT v1.file_name, v1.file_id,
       v1.total_mb,
       v3.free_mb,
       v1.total_mb - v3.free_mb AS used_mb_hwm,
       NVL(v2.data_mb,0) AS data_mb,
       v1.total_mb - v3.free_mb - NVL(v2.data_mb,0) AS file_header_mb
FROM (
    SELECT file_name, file_id, SUM(bytes)/1024/1024 total_mb
    FROM dba_data_files GROUP BY file_name, file_id
) v1
LEFT JOIN (
    SELECT file_id, SUM(bytes)/1024/1024 data_mb
    FROM dba_extents GROUP BY file_id
) v2 ON v1.file_id = v2.file_id
LEFT JOIN (
    SELECT file_id, SUM(bytes)/1024/1024 free_mb
    FROM dba_free_space GROUP BY file_id
) v3 ON v1.file_id = v3.file_id
ORDER BY v1.file_id;