萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> Oracle 教學

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 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;
copyright © 萬盛學電腦網 all rights reserved