SELECT tablespace_name,
ROUND (used_bytes / (1024 * 1024)) used_mb,
ROUND (max_bytes / (1024 * 1024)) max_mb,
ROUND ( (used_bytes) / max_bytes * 100) percent_full
FROM (SELECT ddf.tablespace_name,
NVL (dfs.used_bytes, 0) used_bytes,
ddf.max_bytes
FROM ( SELECT tablespace_name, SUM (bytes) used_bytes
FROM dba_segments
GROUP BY tablespace_name) dfs,
( SELECT tablespace_name,
SUM (GREATEST (bytes, maxbytes)) max_bytes
FROM dba_data_files
GROUP BY tablespace_name
union
SELECT tablespace_name,
SUM (GREATEST (bytes, maxbytes)) max_bytes
FROM dba_temp_files
GROUP BY tablespace_name) ddf
WHERE ddf.tablespace_name = dfs.tablespace_name(+));
TABLESPACE_NAME USED_MB MAX_MB PERCENT_FULL
------------------------------ ---------- ---------- ------------
SYSAUX 1478 32768 5
UNDOTBS1 70 4096 2
USERS 44 4096 1
SYSTEM 669 32768 2
UNDOTBS2 18 4096 0
TEMP 0 4096 0
The script also includes the temporary tablespaces like TEMP and calculates the MAX_MB from the maxsize of the datafiles and not tha actual size.
