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

Oracle 常用資料字典查詢大全：九大類別、即查即用

編輯：Oracle 資料庫基礎

前言：資料字典是 DBA 的「導航儀」

Oracle 資料字典儲存了資料庫所有中繼資料。熟練運用 USER_*、ALL_*、DBA_* 三組視圖，能讓日常運維、問題排查、效能調優事半功倍。本文彙整九大高頻查詢場景，提供可直接複製執行的 SQL 範本。

一、使用者與權限

-- 當前使用者預設表空間
SELECT username, default_tablespace FROM user_users;

-- 當前使用者角色
SELECT * FROM user_role_privs;

-- 系統權限與物件權限
SELECT * FROM user_sys_privs;
SELECT * FROM user_tab_privs;

二、表

-- 所有表清單
SELECT * FROM user_tables;

-- 依關鍵字搜尋表名 (如含 LOG)
SELECT object_name, object_id FROM user_objects
WHERE INSTR(UPPER(object_name), 'LOG') > 0;

-- 表建立時間
SELECT object_name, created FROM user_objects
WHERE object_name = UPPER('&table_name');

-- 表大小 (MB)
SELECT SUM(bytes)/1024/1024 AS size_mb
FROM user_segments
WHERE segment_name = UPPER('&table_name');

-- 置於快取區的表 (CACHE 屬性)
SELECT table_name, cache FROM user_tables WHERE cache = 'Y';

三、索引

-- 索引清單與類型
SELECT index_name, index_type, table_name
FROM user_indexes ORDER BY table_name;

-- 索引包含欄位
SELECT * FROM user_ind_columns
WHERE index_name = UPPER('&index_name');

-- 索引大小 (MB)
SELECT SUM(bytes)/1024/1024 AS size_mb
FROM user_segments
WHERE segment_name = UPPER('&index_name');

四、序列

-- 所有序列狀態 (LAST_NUMBER 為下一個將分配的值)
SELECT * FROM user_sequences;

五、視圖

-- 視圖名稱清單
SELECT view_name FROM user_views;

-- 視圖定義原始碼 (需先設定 LONG)
SET LONG 20000
SELECT text FROM user_views WHERE view_name = UPPER('&view_name');

六、同義詞

SELECT * FROM user_synonyms;

七、約束條件

-- 表的約束總覽
SELECT constraint_name, constraint_type, search_condition, r_constraint_name
FROM user_constraints
WHERE table_name = UPPER('&table_name');

-- 约束對應欄位明細 (含順序)
SELECT c.constraint_name, c.constraint_type, cc.column_name
FROM user_constraints c, user_cons_columns cc
WHERE c.owner = UPPER('&table_owner')
  AND c.table_name = UPPER('&table_name')
  AND c.owner = cc.owner
  AND c.constraint_name = cc.constraint_name
ORDER BY cc.position;

八、函數與預存程序

-- 狀態檢查 (VALID/INVALID)
SELECT object_name, status FROM user_objects WHERE object_type = 'FUNCTION';
SELECT object_name, status FROM user_objects WHERE object_type = 'PROCEDURE';

-- 原始碼查詢
SELECT text FROM all_source
WHERE owner = USER AND name = UPPER('&plsql_name')
ORDER BY line;

九、觸發器

-- 完整觸發器定義產生腳本 (可直接執行重建)
SET LONG 50000
SET HEADING OFF
SET PAGESIZE 2000

SELECT 'CREATE OR REPLACE TRIGGER ' || trigger_name || CHR(10) ||
       DECODE(SUBSTR(triggering_event,1,1), 'A','AFTER','B','BEFORE','I','INSTEAD OF') || CHR(10) ||
       triggering_event || CHR(10) ||
       'ON ' || table_owner || '.' || table_name || CHR(10) ||
       DECODE(INSTR(trigger_type, 'EACH ROW'), 0, NULL, 'FOR EACH ROW' || CHR(10)) ||
       trigger_body
FROM user_triggers;

進階技巧：善用三層視圖前綴

前綴	可見範圍	適用角色
`USER_*`	當前 Schema 物件	開發人員、應用帳號
`ALL_*`	當前使用者有權限的物件	跨 Schema 查詢、審計
`DBA_*`	全資料庫所有物件	DBA、系統管理員