Oracle 數據字典實戰查詢手冊：從 DICTIONARY 到 USER_ 視圖的高效元數據檢索

數據字典核心認知

• 數據字典 = SYS 用戶下的基表 + 公開視圖（DBA_/ALL_/USER_ 前綴）。
• 隨數據庫創建而生，隨 DDL 變更而動態維護，不可手工修改。
• 名稱全大寫，查詢時建議用 UPPER() 匹配。

元數據導航入口

-- 所有字典表/視圖名稱與說明
SELECT * FROM dictionary;  -- 同義詞 dict

-- 字典表列名稱與說明
SELECT * FROM dict_columns;  -- 同義詞 dict_cols

快速搜尋：找包含「索引」說明的字典對象

SELECT * FROM dictionary WHERE INSTR(UPPER(comments), 'INDEX') > 0;

查看 USER_INDEXES 各列含義

SELECT column_name, comments
FROM dict_columns
WHERE table_name = 'USER_INDEXES';

高頻查詢場景速查（按類別）

1. 用戶與權限

-- 當前用戶默認表空間
SELECT username, default_tablespace FROM user_users;

-- 當前用戶角色
SELECT * FROM user_role_privs;

-- 系統權限
SELECT * FROM user_sys_privs;

-- 表級權限
SELECT * FROM user_tab_privs;

2. 表對象

-- 所有表
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');

-- 緩存區保留的表
SELECT table_name, cache FROM user_tables WHERE INSTR(cache, 'Y') > 0;

3. 索引

-- 索引名、類型、所屬表
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');

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

4. 序列

SELECT * FROM user_sequences;  -- LAST_NUMBER 為當前值

5. 視圖

-- 視圖名
SELECT view_name FROM user_views;

-- 視圖定義 SQL（長文本）
SET LONG 20000
SELECT text FROM user_views WHERE view_name = UPPER('&view_name');

6. 同義詞

SELECT * FROM user_synonyms;

7. 約束條件

-- 表的約束詳情
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, cc.position
FROM user_constraints c
JOIN user_cons_columns cc ON c.owner = cc.owner AND c.constraint_name = cc.constraint_name
WHERE c.table_name = UPPER('&table_name')
ORDER BY cc.position;

8. 存儲過程/函數/包

-- 對象狀態
SELECT object_name, status FROM user_objects WHERE object_type = 'FUNCTION';
SELECT object_name, status FROM user_objects WHERE object_type = 'PROCEDURE';
SELECT object_name, status FROM user_objects WHERE object_type = 'PACKAGE';
SELECT object_name, status FROM user_objects WHERE object_type = 'PACKAGE BODY';

-- 源代碼
SELECT text FROM all_source WHERE owner = USER AND name = UPPER('&plsql_name') ORDER BY line;

進階：動態構建 DDL 逆向腳本

-- 導出某用戶所有表 DDL
SET LONG 1000000 PAGESIZE 0 LINESIZE 32767 FEEDBACK OFF HEADING OFF
SELECT DBMS_METADATA.GET_DDL('TABLE', table_name) FROM user_tables;

-- 導出完整 Schema
EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'STORAGE', FALSE);
EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'TABLESPACE', FALSE);
SELECT DBMS_METADATA.GET_DDL('SCHEMA', USER) FROM dual;

最佳實踐建議

1. 優先查 USER_ 視圖：僅當前用戶對象，無權限過濾開銷，速度最快。
2. 跨用戶查詢用 ALL_：可見對象（含授權），DBA_ 需 DBA 權限。
3. 大表段統計用 DBA_SEGMENTS：USER_SEGMENTS 同理，注意 BYTES 為段分配空間，非實際數據大小。
4. 長文本設置 SET LONG：查詢 USER_VIEWS.TEXT、ALL_SOURCE.TEXT、DBMS_METADATA 輸出前必設。
5. 善用 DICTIONARY/DICT_COLUMNS：探索未知視圖結構的「地圖」。

結語

Oracle 數據字典是數據庫的「說明書」，熟練使用 DICTIONARY、DICT_COLUMNS、USER_/ALL_/DBA_ 三層視圖體系、DBMS_METADATA 逆向工程、SET LONG 長文本處理，能讓 DBA 和開發在無文檔、無圖形工具的純命令行環境下，依然高效完成對象探索、結構對比、權限審計、DDL 導出等核心任務。建議將常用查詢封裝為個人 sql_snippets 库，構建屬於自己的「字典查詢工具箱」。