Monitoring tablespace usage is a critical part of Oracle database administration. As DBAs, we need to keep an eye on how much space is used, how much remains, and how it aligns with what’s physically allocated on disk. We’ll explore a query that gives you a clear view of your tablespace usage, including how much space is actually used by segments, the total allocated file space, and the maximum size the tablespace can reach (considering autoextensibility).
select df.tablespace_name Tablespace,
nvl(totalusedspace,0) Used_MB,
(df.totalspace – nvl(tu.totalusedspace,0)) Free_MB, df.actualspace as Actual_File_Space,
df.totalspace Total_MB,
round(100 * ( (nvl(tu.totalusedspace,0))/ df.totalspace),2) Pct_Used,
round(100 * ( (df.totalspace – nvl(tu.totalusedspace,0))/ df.totalspace),2)
Pct_Free
from
(
select tablespace_name,round(sum(decode(autoextensible,’YES’,maxbytes,’NO’,bytes)) / 1048576) TotalSpace, round (sum(bytes)/1048576) ActualSpace
from dba_data_files
group by tablespace_name
) df
,
(
select round(sum(bytes)/(1024*1024)) totalusedspace, tablespace_name
from dba_segments
group by tablespace_name
) tu
where df.tablespace_name = tu.tablespace_name(+)
order by Pct_Free
Example Output
| Tablespace | Used_MB | Free_MB | Actual_File_Space | Total_MB | Pct_Used | Pct_Free |
|---|---|---|---|---|---|---|
| USERS | 512 | 488 | 600 | 1000 | 51.20 | 48.80 |
| SYSTEM | 780 | 220 | 800 | 1000 | 78.00 | 22.00 |