Oracle 系統表與資料字典視圖完全手冊：DBA_/ALL_/USER_ 三層視圖體系與核心查詢速查

三層視圖前綴語義

查詢效能：USER_ > ALL_ > DBA_ (後兩者涉及權限檢查與更大字典掃描)。

核心動態效能視圖 (V$ / GV$)

• 記憶體結構：V$SGA、V$SGASTAT、V$PGASTAT。
• Instance 狀態：V$INSTANCE、V$VERSION、V$PARAMETER。
• Session/Process：V$SESSION、V$PROCESS、V$SESSION_WAIT、V$SESSION_LONGOPS。
• SQL 共享池：V$SQL、V$SQLAREA、V$SQL_PLAN、V$SQLSTATS。
• 儲存/檔案：V$DATAFILE、V$LOGFILE、V$CONTROLFILE、V$TEMPFILE。
• 備份/恢復：V$BACKUP、V$RMAN_STATUS、V$ARCHIVED_LOG、V$RECOVERY_FILE_DEST。
• RAC：GV$ 版本含 INST_ID 欄位，跨實例聚合查詢。

靜態資料字典視圖分類速查

1. 表與欄位

• DBA_TABLES、DBA_TAB_COLUMNS、DBA_TAB_COL_STATISTICS、DBA_TAB_HISTOGRAMS、DBA_TAB_PARTITIONS、DBA_TAB_SUBPARTITIONS。
• 統計資訊：DBA_TAB_STATISTICS (10g+ 統一視圖)。

2. 索引與約束

• DBA_INDEXES、DBA_IND_COLUMNS、DBA_IND_PARTITIONS、DBA_IND_STATISTICS。
• DBA_CONSTRAINTS、DBA_CONS_COLUMNS (PK/FK/UK/CHECK/NOT NULL)。

3. 視圖/同義詞/序列

• DBA_VIEWS、DBA_SYNONYMS、DBA_SEQUENCES。
• 通用物件視圖：DBA_OBJECTS (含 TYPE、STATUS、CREATED、LAST_DDL_TIME)。

4. 使用者/角色/權限

• DBA_USERS (含 DEFAULT_TABLESPACE、TEMPORARY_TABLESPACE、PROFILE、ACCOUNT_STATUS、PASSWORD_VERSIONS)。
• DBA_ROLES、DBA_ROLE_PRIVS、DBA_SYS_PRIVS、DBA_TAB_PRIVS、DBA_COL_PRIVS。
• DBA_PROFILES (密碼/資源限制)。

5. 表空間/資料檔/段/Undo

• DBA_TABLESPACES、DBA_DATA_FILES、DBA_TEMP_FILES。
• DBA_SEGMENTS、DBA_EXTENTS、DBA_FREE_SPACE。
• DBA_ROLLBACK_SEGS (舊版)、DBA_UNDO_EXTENTS、V$UNDOSTAT。

6. PL/SQL 程式單元

• DBA_SOURCE、DBA_PROCEDURES、DBA_DEPENDENCIES、DBA_ERRORS、DBA_IDENTIFIERS (11g+ PL/Scope)。

7. 排程/作業

• DBA_JOBS (舊版 DBMS_JOB)、DBA_SCHEDULER_JOBS、DBA_SCHEDULER_JOB_RUN_DETAILS。

8. 審計/安全

• DBA_AUDIT_TRAIL、DBA_FGA_AUDIT_TRAIL、DBA_COMMON_AUDIT_TRAIL (12c+ 統一審計)。

高頻實戰查詢範本

-- 1. 找出最大的 10 個段
SELECT * FROM (
  SELECT owner, segment_name, segment_type, bytes/1024/1024 MB
  FROM dba_segments
  ORDER BY bytes DESC
) WHERE ROWNUM <= 10;

-- 2. 檢查無效物件
SELECT owner, object_name, object_type, status
FROM dba_objects
WHERE status = 'INVALID'
ORDER BY owner, object_type;

-- 3. 查詢長時間執行的 SQL
SELECT sql_id, elapsed_time/1e6 sec, executions, sql_text
FROM v$sqlstats
WHERE elapsed_time/1e6 > 10
ORDER BY elapsed_time DESC;

-- 4. 表空間使用率
SELECT tablespace_name,
       ROUND(used_mb,1) used_mb,
       ROUND(free_mb,1) free_mb,
       ROUND(pct_used,1) pct_used
FROM (
  SELECT df.tablespace_name,
         df.bytes/1024/1024 total_mb,
         (df.bytes - NVL(fs.bytes,0))/1024/1024 used_mb,
         NVL(fs.bytes,0)/1024/1024 free_mb,
         ROUND((df.bytes - NVL(fs.bytes,0))/df.bytes*100,1) pct_used
  FROM (SELECT tablespace_name, SUM(bytes) bytes FROM dba_data_files GROUP BY tablespace_name) df
  LEFT JOIN (SELECT tablespace_name, SUM(bytes) bytes FROM dba_free_space GROUP BY tablespace_name) fs
    ON df.tablespace_name = fs.tablespace_name
);

字典視圖定義來源

• 核心建立腳本：$ORACLE_HOME/rdbms/admin/catalog.sql、catproc.sql。
• 擴展功能：catexp.sql (Exp/Imp)、cataudit.sql (審計)、catsnap.sql (AWR 快照)、catqm.sql (LogMiner) 等。
• 升級/降級：catupgrd.sql、catreq.sql。

總結

熟練運用 DBA_/ALL_/USER_ 三層視圖與 V$/GV$ 動態視圖，是 DBA 日常巡檢、效能調優、故障排查、自動化運維腳本開發的基礎語言。