Had a nice issue - which demonstrated that looks can be deceiving.
My colleague asked me to increase the size of a certain tablespace as it was full.
I added a datafile in ASM for this tablespace but put it on autoextend = ON .
He complained that it was still on 100 % full.
A second I thought I messed up things (happens to the best :-)
Then another look solved the problem:
The query - which apparently was used in a lot of Nagios installations - checked only the
datafiles which were on autoextend = no .
The following query was used in Nagios.
select d.tablespace_name "TABLESPACE",
sum(d.bytes)/1048576 "SIZE (M)",
100 -(nvl(round((FREESPCE/(sum(d.bytes)/1048576))*100),0)) "USED (%)"
FROM dba_data_files d,
( SELECT round(sum(f.bytes)/1048576,2) FREESPCE,
FROM dba_free_space f
GROUP BY f.tablespace_name)
WHERE d.autoextensible IN 'NO' AND d.tablespace_name = Tablespc (+)
group by d.tablespace_name,FREESPCE,d.autoextensible
order by 1 desc
So, never belief a monitoring tool - unless you have double checked the underlying query.