SELECT tablespace_name,
SUM(bytes)/1024/1024 AS total_mb,
SUM(max_size)/1024/1024 AS max_mb
FROM dba_segments
GROUP BY tablespace_name
ORDER BY total_mb DESC;
bytes 為目前佔用位元組,max_size 為區塊上限(含 AUTOEXTEND MAXSIZE)。單位轉換為 MB 方便閱讀。
錯誤:ORA-00942: table or view does not exist(針對 DBA_SEGMENTS)。
一般用戶無 SELECT ANY DICTIONARY 或針對 DBA_SEGMENTS 的直接授權。
-- 方案 A:精確授權(推薦)
GRANT SELECT ON dba_segments TO app_user;
-- 方案 B:全量字典授權(謹慎使用)
GRANT SELECT ANY DICTIONARY TO app_user;
-- 方案 C:建立視圖包裝(最安全)
CREATE VIEW app_tablespace_usage AS
SELECT tablespace_name, SUM(bytes)/1024/1024 AS used_mb
FROM dba_segments GROUP BY tablespace_name;
GRANT SELECT ON app_tablespace_usage TO app_user;
SELECT
df.tablespace_name,
df.total_mb,
(df.total_mb - fs.free_mb) AS used_mb,
fs.free_mb,
ROUND((df.total_mb - fs.free_mb) / df.total_mb * 100, 2) AS pct_used
FROM
(SELECT tablespace_name, SUM(bytes)/1024/1024 AS total_mb
FROM dba_data_files GROUP BY tablespace_name) df
JOIN
(SELECT tablespace_name, SUM(bytes)/1024/1024 AS free_mb
FROM dba_free_space GROUP BY tablespace_name) fs
USING (tablespace_name)
ORDER BY pct_used DESC;
DBA_HIST_TBSPC_SPACE_USAGE(需診斷包授權)或自建歷史表。DBA_DATA_FILES.AUTOEXTENSIBLE、MAXBYTES 判斷是否仍可自動擴展。掌握 DBA_SEGMENTS、DBA_FREE_SPACE、DBA_DATA_FILES 三視圖聯查,配合最小權限授權視圖,即可構建穩健的表空間容量監控體系。