Oracle 查詢表空間大小：DBA_SEGMENTS 權限問題與授權解決

透過 DBA_SEGMENTS 彙總表空間使用量是常見監控需求。但直接在 PL/SQL 程序中查詢常報 ORA-00942: table or view does not exist，主因是缺乏直接查詢權限。本文提供標準查詢與授權腳本。

一、標準查詢語句

SELECT TABLESPACE_NAME,
       SUM(BYTES) / 1024 / 1024 AS SIZE_MB,
       SUM(MAX_SIZE) / 1024 / 1024 AS MAX_SIZE_MB
FROM DBA_SEGMENTS
GROUP BY TABLESPACE_NAME
ORDER BY SIZE_MB DESC;

• BYTES：區段實際佔用空間（位元組）。
• MAX_SIZE：區段可擴展上限（若有設定）。
• 單位轉換為 MB 方便閱讀。

二、權限錯誤原因

DBA_SEGMENTS 屬 SYS 所有，非 PUBLIC 授予。一般使用者（含 DBA 角色）在 定義者權限 的存儲過程/函數/套件中查詢會失敗，因角色在命名區塊中停用。

三、授權解決方案

方案一：授予 SELECT 權限（最小權限原則）

GRANT SELECT ON DBA_SEGMENTS TO <username>;

方案二：授予所有權限（不建議生產環境）

GRANT ALL ON DBA_SEGMENTS TO <username>;

四、最佳實務：使用 AUTHID CURRENT_USER

在存儲過程定義時加入 AUTHID CURRENT_USER，以調用者權限執行，自動啟用角色權限：

CREATE OR REPLACE PROCEDURE GET_TS_USAGE
AUTHID CURRENT_USER
AS
BEGIN
  -- 此處可直接查詢 DBA_SEGMENTS，無需額外 GRANT
  FOR r IN (SELECT TABLESPACE_NAME, SUM(BYTES)/1024/1024 MB
            FROM DBA_SEGMENTS GROUP BY TABLESPACE_NAME) LOOP
    DBMS_OUTPUT.PUT_LINE(r.TABLESPACE_NAME || ':' || r.MB || 'MB');
  END LOOP;
END;

五、替代視圖（無需額外授權）

• USER_SEGMENTS：當前使用者擁有的區段（無需權限）。
• USER_TABLESPACES：當前使用者可存取的表空間配額。
• 若需全庫視角，仍需 DBA_SEGMENTS 或 DBA_DATA_FILES + DBA_FREE_SPACE 計算。

六、完整表空間使用率查詢（含剩餘空間）

SELECT d.TABLESPACE_NAME,
       ROUND(SUM(d.BYTES)/1024/1024,2) AS TOTAL_MB,
       ROUND(SUM(NVL(f.BYTES,0))/1024/1024,2) AS FREE_MB,
       ROUND(100 * SUM(NVL(f.BYTES,0)) / SUM(d.BYTES), 2) AS PCT_FREE
FROM DBA_DATA_FILES d
LEFT JOIN DBA_FREE_SPACE f
  ON d.TABLESPACE_NAME = f.TABLESPACE_NAME
 AND d.FILE_ID = f.FILE_ID
GROUP BY d.TABLESPACE_NAME
ORDER BY PCT_FREE;