一、用戶資訊
-- 當前用戶預設表空間
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 POOL 的表
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');
四、序列
SELECT * FROM user_sequences; -- LAST_NUMBER 為當前值
五、視圖
SELECT view_name FROM user_views;
SET LONG 2000;
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');
九、觸發器 (完整重建 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;