Oracle 資料字典實用查詢大全：使用者、表、索引、約束、PL/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(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;

三、索引結構

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

四、序列

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

五、視圖

-- 視圖名稱
SELECT view_name FROM user_views;

-- 視圖定義 SQL（需先設定 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 = USER
  AND c.table_name = UPPER('&table_name')
  AND c.constraint_name = cc.constraint_name
ORDER BY cc.position;

八、函數與程序

-- 狀態
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(trigger_type,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;

十、進階：DBA_ 視圖與跨 Schema 查詢

具備 DBA 權限時，將 USER_ 換為 DBA_ 或 ALL_ 可查詢全庫物件。常用：

• DBA_SEGMENTS：全庫段大小統計。
• DBA_TAB_COLUMNS：全庫欄位定義。
• DBA_DEPENDENCIES：物件相依關係分析。