Hello all,
i need a script for monitoring the tablespace fill up degree with consideration of the autoextend option.
I have the following triggers for getting an alarm;
- tablespace filled up with > 90% (<10% space left within the tablespace)
- size of the space left within the tablespace is < 20000MB (20GB)
- autoextend functionality must be covered -> (eg. TS is actual 2GB, but can grow until 32GB, the alarm should only be reoported if the tablespace is filled up with 28GB)
I got a basis from SQL developer and have adapted this with several adaptions :
SELECT "TABLESPACE_NAME", "MB_ALLOC", "MB_FREE", "MB_USED", "PCT_USED" FROM(
select * from (
select a.tablespace_name as tablespace_name,
round(a.bytes_alloc / 1024 / 1024) as MB_ALLOC,
round(nvl(b.bytes_free, 0) / 1024 / 1024) as mb_free,
round((a.bytes_alloc - nvl(b.bytes_free, 0)) / 1024 / 1024) as mb_used,
100 - round((nvl(b.bytes_free, 0) / a.bytes_alloc) * 100) as Pct_used
from ( select f.tablespace_name,
sum(f.bytes) bytes_alloc,
sum(decode(f.autoextensible, 'YES',f.maxbytes,'NO', f.bytes)) maxbytes
from dba_data_files f
group by tablespace_name) a,
( select f.tablespace_name,
sum(f.bytes) bytes_free
from dba_free_space f
group by tablespace_name) b
where a.tablespace_name = b.tablespace_name (+)
and (100 - round((nvl(b.bytes_free, 0) / a.bytes_alloc) * 100) > 90)
and (round(nvl(b.bytes_free, 0) / 1024 / 1024)) < 20000
)order by 5 asc
)
/
In the script is the option with the autoextend missing.
Does anyone have an idea how to implement this ?
Or does anayone have a complete script which covers my preconditions better than the scipt above ?
Any comments or proposals are welcome.
Thanks.
Regards,
Karlheinz