Oracle 資料字典與資料庫物件查詢 SQL 大全

Oracle 資料字典視圖分類與查詢模式

Oracle 提供三組前綴視圖，權限範圍遞增：

• USER_*：當前使用者擁有的物件 (無需特權)
• ALL_*：當前使用者可存取的物件 (含授權)
• DBA_*：全資料庫所有物件 (需 DBA 權限/SELECT_CATALOG_ROLE)

一、使用者與權限查詢

-- 當前使用者資訊
SELECT username, default_tablespace, temporary_tablespace, profile, account_status
FROM user_users;

-- 角色權限
SELECT * FROM user_role_privs;

-- 系統權限
SELECT * FROM user_sys_privs;

-- 物件權限 (被授予/授予他人)
SELECT * FROM user_tab_privs;
SELECT * FROM user_tab_privs_made;
SELECT * FROM user_tab_privs_recd;

二、表與儲存空間查詢

-- 所有表基本資訊
SELECT table_name, tablespace_name, num_rows, blocks, avg_row_len, last_analyzed
FROM user_tables
ORDER BY table_name;

-- 依名稱模式查表
SELECT object_name, object_id, created, last_ddl_time
FROM user_objects
WHERE object_type = 'TABLE' AND object_name LIKE '%LOG%';

-- 表建立時間
SELECT object_name, created FROM user_objects 
WHERE object_name = UPPER('&table_name') AND object_type = 'TABLE';

-- 表大小 (MB) - 含索引/LOB
SELECT segment_name, SUM(bytes)/1024/1024 AS size_mb
FROM user_segments
WHERE segment_name = UPPER('&table_name')
GROUP BY segment_name;

-- 僅表本身大小 (排除索引/LOB)
SELECT table_name, 
       (blocks * 8192) / 1024 / 1024 AS size_mb  -- 假設 8K 區塊
FROM user_tables
WHERE table_name = UPPER('&table_name');

-- 快取表 (KEEP buffer pool)
SELECT table_name, cache FROM user_tables WHERE cache = 'Y';

三、索引查詢

-- 所有索引基本資訊
SELECT index_name, index_type, table_name, uniqueness, status, tablespace_name
FROM user_indexes
ORDER BY table_name, index_name;

-- 索引欄位組成
SELECT index_name, column_name, column_position, descend
FROM user_ind_columns
WHERE index_name = UPPER('&index_name')
ORDER BY column_position;

-- 索引大小
SELECT segment_name, SUM(bytes)/1024/1024 AS size_mb
FROM user_segments
WHERE segment_name = UPPER('&index_name')
GROUP BY segment_name;

-- 未使用索引偵測 (需啟用監控)
ALTER INDEX index_name MONITORING USAGE;
-- 一段時間後
SELECT index_name, used FROM v$object_usage WHERE used = 'NO';

四、序列 (Sequence) 查詢

SELECT sequence_name, min_value, max_value, increment_by, 
       cycle_flag, order_flag, cache_size, last_number
FROM user_sequences;

五、檢視、同義詞、PL/SQL 物件

-- 檢視定義
SELECT view_name, text_length, text
FROM user_views
WHERE view_name = UPPER('&view_name');

-- 同義詞
SELECT synonym_name, table_owner, table_name, db_link
FROM user_synonyms;

-- 函數/程序/套件/觸發器 原始碼
SELECT name, type, line, text
FROM user_source
WHERE name = UPPER('&object_name')
ORDER BY name, type, line;

六、約束條件查詢

-- 所有約束
SELECT constraint_name, constraint_type, table_name, status, 
       r_constraint_name, delete_rule
FROM user_constraints
ORDER BY table_name, constraint_name;

-- 檢查約束條件定義
SELECT constraint_name, search_condition
FROM user_constraints
WHERE constraint_type = 'C' AND table_name = UPPER('&table_name');

-- 外鍵關聯明細
SELECT a.table_name AS child_table, a.column_name AS child_col,
       c.table_name AS parent_table, c.column_name AS parent_col,
       b.constraint_name, b.delete_rule
FROM user_cons_columns a
JOIN user_constraints b ON a.constraint_name = b.constraint_name
JOIN user_cons_columns c ON b.r_constraint_name = c.constraint_name
WHERE b.constraint_type = 'R'
ORDER BY b.constraint_name, a.position;

七、表空間與資料檔管理

-- 表空間使用率
SELECT tablespace_name,
       ROUND(SUM(bytes)/1024/1024, 2) AS total_mb,
       ROUND(SUM(bytes - NVL(free_bytes, 0))/1024/1024, 2) AS used_mb,
       ROUND(SUM(NVL(free_bytes, 0))/1024/1024, 2) AS free_mb,
       ROUND(100 * SUM(NVL(free_bytes, 0))/SUM(bytes), 2) AS pct_free
FROM (
    SELECT tablespace_name, bytes, 0 AS free_bytes FROM dba_data_files
    UNION ALL
    SELECT tablespace_name, 0, bytes FROM dba_free_space
)
GROUP BY tablespace_name
ORDER BY pct_free;

-- 資料檔自動擴充狀態
SELECT file_name, tablespace_name, 
       ROUND(bytes/1024/1024, 2) AS size_mb,
       autoextensible, 
       ROUND(maxbytes/1024/1024, 2) AS max_mb
FROM dba_data_files;

八、效能與維護相關查詢

-- 失效物件
SELECT object_name, object_type, status
FROM user_objects
WHERE status = 'INVALID';

-- 長執行查詢 (需診斷包授權)
SELECT sql_text, elapsed_time/1e6 AS elapsed_sec, executions
FROM v$sql
WHERE elapsed_time > 10e6  -- 超過 10 秒
ORDER BY elapsed_time DESC;

-- 表統計資訊收集狀態
SELECT table_name, num_rows, blocks, avg_row_len, sample_size, last_analyzed
FROM user_tables
WHERE last_analyzed IS NULL OR last_analyzed < SYSDATE - 30;

-- 鎖等待查詢
SELECT s.sid, s.serial#, s.username, s.status, s.machine, s.program,
       o.object_name, l.locked_mode, l.type
FROM v$locked_object l
JOIN v$session s ON l.session_id = s.sid
JOIN dba_objects o ON l.object_id = o.object_id;

九、實用管理腳本：刪除使用者所有表

BEGIN
  -- 先刪除外鍵
  FOR r IN (SELECT constraint_name, table_name 
            FROM user_constraints WHERE constraint_type = 'R')
  LOOP
    EXECUTE IMMEDIATE 'ALTER TABLE ' || r.table_name || 
                      ' DROP CONSTRAINT ' || r.constraint_name;
  END LOOP;
  
  -- 再刪除表
  FOR t IN (SELECT table_name FROM user_tables)
  LOOP
    EXECUTE IMMEDIATE 'DROP TABLE ' || t.table_name || ' CASCADE CONSTRAINTS PURGE';
  END LOOP;
END;
/
-- PURGE: 直接釋放空間不進回收筒 (10g+)

十、統計所有表列數 (高效方法)

-- 方法 1: 統計資訊 (需先收集統計)
EXEC DBMS_STATS.GATHER_SCHEMA_STATS('SCOTT', options => 'GATHER AUTO');
SELECT table_name, num_rows FROM user_tables ORDER BY num_rows DESC;

-- 方法 2: 動態 SQL 精確計數 (較慢)
DECLARE
  v_sql VARCHAR2(4000);
  v_cnt NUMBER;
BEGIN
  FOR t IN (SELECT table_name FROM user_tables)
  LOOP
    v_sql := 'SELECT COUNT(*) FROM ' || t.table_name;
    EXECUTE IMMEDIATE v_sql INTO v_cnt;
    DBMS_OUTPUT.PUT_LINE(t.table_name || ': ' || v_cnt);
  END LOOP;
END;
/
-- 方法 3: 快速估算 (10g+)
SELECT table_name, 
       ROUND(blocks * 8192 / GREATEST(avg_row_len, 1)) AS est_rows
FROM user_tables;