前言
日常 DBA 與開發除錯頻繁需要查詢：使用者擁有哪些表、函數、當前連線 Session、表空間使用率等。本文彙整高頻資料字典查詢，可直接建立為工具腳本。
1. 物件與程式碼查詢
| 需求 | SQL |
|---|
| 當前使用者所有表/視圖/同義詞 (含註解) | SELECT * FROM user_tab_comments; |
| 所有使用者表/視圖 (需權限) | SELECT * FROM all_tab_comments; |
| 當前使用者表欄位定義 (含註解) | SELECT * FROM user_col_comments; |
| 所有使用者表欄位定義 | SELECT * FROM all_col_comments; |
| 詳細欄位屬性 (資料型別、長度、精度、可空、預設值) | SELECT * FROM user_tab_columns; / all_tab_columns |
| 函數/程序/套件/觸發器原始碼 | SELECT * FROM user_source ORDER BY name, type, line; |
2. 使用者與權限查詢
| 需求 | SQL |
|---|
| 所有資料庫使用者 | SELECT * FROM all_users; / dba_users (DBA) |
| 當前 Session 連線資訊 | SELECT sid, serial#, username, program, machine, logon_time, status FROM v$session WHERE username IS NOT NULL; |
| 當前 Session 擁有的系統權限 | SELECT * FROM session_privs; |
| 使用者角色授予 (DBA) | SELECT * FROM dba_role_privs; |
| 系統權限授予 (DBA) | SELECT * FROM dba_sys_privs; |
| 物件權限授予 (DBA) | SELECT * FROM dba_tab_privs; |
| 當前使用者角色 | SELECT * FROM user_role_privs; |
3. 表空間使用率監控 (核心 DBA 指標)
SELECT
a.file_id AS file_no,
a.tablespace_name,
ROUND(a.bytes/1024/1024, 2) AS total_mb,
ROUND((a.bytes - SUM(NVL(b.bytes,0)))/1024/1024, 2) AS used_mb,
ROUND(SUM(NVL(b.bytes,0))/1024/1024, 2) AS free_mb,
ROUND(SUM(NVL(b.bytes,0))/a.bytes * 100, 2) AS pct_free
FROM dba_data_files a
LEFT JOIN dba_free_space b ON a.file_id = b.file_id
GROUP BY a.tablespace_name, a.file_id, a.bytes
ORDER BY a.tablespace_name, a.file_id;
- 若
PCT_FREE < 10% 需擴充資料檔或啟用 Autoextend。
- 搭配
DBA_TEMP_FILES / V$TEMP_SPACE_HEADER 監控暫存表空間。
4. 進階：表空間自動擴充檢查
SELECT tablespace_name, file_name, autoextensible, maxbytes/1024/1024 AS max_mb
FROM dba_data_files
WHERE autoextensible = 'YES';
命名慣例提醒
USER_*：當前使用者擁有的物件 (無需權限)。ALL_*：當前使用者可存取的物件 (含其他使用者授權)。DBA_*：全庫所有物件 (需 DBA 權限/SELECT_CATALOG_ROLE)。V$*：動態效能視圖 (記憶體結構、Session、鎖、SQL 等)。
