Oracle 常用字典查詢速查手冊 (用戶/表/索引/序列/視圖/同義詞/約束/程式/觸發器)

一、用戶資訊

-- 當前用戶預設表空間
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(M)" FROM user_segments
WHERE segment_name = UPPER('&table_name');

-- 置於 KEEP/RECYCLE 池的表 (CACHE 屬性)
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(M)" FROM user_segments
WHERE segment_name = UPPER('&index_name');

四、序列

-- 所有序列 (LAST_NUMBER 為當前值)
SELECT * FROM user_sequences;

五、視圖

-- 視圖名稱
SELECT view_name FROM user_views;

-- 視圖定義 SQL (需配合 SET 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;

八、函數與程序

-- 狀態
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;

九、觸發器 (完整重建 DDL)

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;

速查表：常用字典視圖對照