您现在的位置：程式碼之家 >> 數據庫知識 > >> Oracle 資料庫 >> Oracle 資料庫基礎

Oracle 數據字典高效利用指南：從基礎查詢到分類實戰

編輯：Oracle 資料庫基礎

一、認識數據字典

Oracle 數據字典是資料庫核心組件，隨資料庫建立而生成、隨變更而更新，本質是 SYS 用戶下的一組表與視圖（名稱全大寫）。它儲存：用戶資訊、權限、所有資料庫物件資訊、約束條件、統計分析視圖等。不可手動修改字典內容。

兩大入口視圖：

DICTIONARY（同義詞 DICT）：所有字典表名稱與說明
DICT_COLUMNS：所有字典表的欄位名稱與說明

二、快速探索字典結構

1. 依關鍵字搜尋相關字典表

SELECT * FROM DICTIONARY WHERE INSTR(COMMENTS, 'index') > 0;

2. 查看特定表的欄位含義

SELECT COLUMN_NAME, COMMENTS FROM DICT_COLUMNS WHERE TABLE_NAME = 'USER_INDEXES';

善用這兩招，無需翻閱文件即可掌握字典細節。

三、分類實戰：DBA/開發常用查詢彙總

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(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');

-- 置於記憶體的表（CACHE）
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(M)" FROM USER_SEGMENTS WHERE SEGMENT_NAME = UPPER('&index_name');

4. 序列

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

5. 視圖

-- 視圖名稱
SELECT VIEW_NAME FROM USER_VIEWS;

-- 視圖定義 SQL（需先設定 LONG）
SET LONG 2000;
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
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;

8. 函數與預存程序

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

9. 觸發器完整定義

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;