With this script you can monitor tablespaces sizes in oracle database. This script returns next columns:
Tablespace - tablespace name
Size MB - currently allocated space of tablespace in megabytes
Free MB - free size from allocated space in tablespace in megabytes
Max MB - size of maximum space until tablespace will extend in megabytes
Maximum free space in MB - free space in tablespace until it will be full, this is real value for monitring because it is showing real free space in tablespace in megabytes
Used MB - what space is used frm allocated space by segments in tablespace in megabytes
Used % - percentage of used space in tablespace, this is also real monitoring value
Status - oracle offers best practice for tablespace used percentage values 85% - caution, 90% - alarm, 97% - critical, so this script is depend on this values.
(
select df.tablespace_name "Tablespace",
round(df.tablespace_size / 1024 / 1024, 2) "Size MB",
round(nvl(fs.free_space, 0) / 1024 / 1024, 2) "Free MB",
round(df.tablespace_maxsize / 1024 / 1024, 2) "Max MB",
round((df.tablespace_maxsize - (df.tablespace_size - round(nvl(fs.free_space, 0), 2)))
/ 1024 / 1024,2) "Maximum free space in MB",
round((df.tablespace_size / 1024 / 1024 - fs.free_space / 1024 / 1024),2) "Used MB",
to_char(round((df.tablespace_size - round(nvl(fs.free_space, 0), 2)) /
df.tablespace_maxsize * 100,
2),
'990D99') || ' %' "Used %",
case
when round((df.tablespace_size - round(nvl(fs.free_space, 0), 2)) /
df.tablespace_maxsize * 100,
2) > 95 then
'CRITICAL - 95%'
when round((df.tablespace_size - round(nvl(fs.free_space, 0), 2)) /
df.tablespace_maxsize * 100,
2) > 90 then
'ALARM - 90%'
when round((df.tablespace_size - round(nvl(fs.free_space, 0), 2)) /
df.tablespace_maxsize * 100,
2) > 85 then
'CAUTION - 85%'
else
'OK < 85%'
end "Status"
from (select
tablespace_name,
sum(bytes) tablespace_size,
sum(decode(maxbytes, 0, bytes, null, bytes, maxbytes)) tablespace_maxsize
from dba_data_files
group by tablespace_name) df,
(select
tablespace_name, sum(bytes) free_space
from dba_free_space
group by tablespace_name) fs
where df.tablespace_name = fs.tablespace_name(+)
)
UNION
(
select
ts.tablespace_name "Tablespace",
round(ts.tablespace_size / 1024 / 1024, 2) "Size MB",
round(nvl(tfs.free_space, 0) / 1024 / 1024, 2) "Free MB",
round(ts.tablespace_maxsize / 1024 / 1024, 2) "Max MB",
round((ts.tablespace_maxsize - (ts.tablespace_size - tfs.free_space))
/ 1024 / 1024, 2) "Maximum free space in MB",
round((ts.tablespace_size / 1024 / 1024 - tfs.free_space / 1024 / 1024),2)
"Used MB",
to_char(round((ts.tablespace_size - tfs.free_space) /
ts.tablespace_maxsize * 100,
2),
'990D99') || ' %' "Used %",
case
when round((ts.tablespace_size - tfs.free_space) /
ts.tablespace_maxsize * 100,
2) > 95 then
'CRITICAL - 95%'
when round((ts.tablespace_size - tfs.free_space) /
ts.tablespace_maxsize * 100,
2) > 90 then
'ALARM - 90%'
when round((ts.tablespace_size - tfs.free_space) /
ts.tablespace_maxsize * 100,
2) > 85 then
'CAUTION - 85%'
else
'OK < 85%'
end "Status"
from (select
tablespace_name,
sum(bytes) tablespace_size,
sum(decode(maxbytes, 0, bytes, null, bytes, maxbytes)) tablespace_maxsize
from dba_temp_files
group by tablespace_name) ts,
(select
tablespace_name, free_space from dba_temp_free_space) tfs
where ts.tablespace_name = tfs.tablespace_name
)
order by 7 desc, 1 asc;
note :
If it'll work slow on your database, main reason for this is your recyclebin, if there is too many objects then dba_free_space view will work slow, solution for this is to purge dba_recyclebin
