What you see is not what you expected

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,
f.tablespace_name Tablespc
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.