Oracle Tablespace Check Autoextend? Options

[Edward.Moscardin...]

Hi,

I was wondering if you could provide the pl/sql query used to monitor Oracle tablespace usage. I'm testing it out, and it doesn't seem to take into account Oracle's autoextend feature.


Thanks,

Edward Moscardini

[Joel Pereira]

Hey Edward,

Oracle tablespaces with autoextend on will simply return how much free space they have at that moment (when the monitor runs). It doesn't try to perform any estimate of how much free space it "may" have if/when it auto-extends. So this means that a tablespace with auto-extend turned on will always appear to look almost full.

Here's the query used in the Oracle Tablespace monitor:

CODE

SELECT DISTINCT 'zz', df.tablespace_name, SUM (df.BYTES) "Total Bytes", MAX( fs.total_bytes) "Bytes Free",
(MAX(fs.total_bytes)/SUM (df.BYTES))*100 "% Free"
FROM SYS.dba_data_files df, SYS.dba_free_space_coalesced fs
WHERE ((df.tablespace_name = fs.tablespace_name))
GROUP BY df.tablespace_name
ORDER BY df.tablespace_name

[Edward.Moscardin...]

Hi Joel,

All most all of our tablespaces have autoextend turned on, so we're getting false positives returned from uptime continuously. Could we work with you to create a monitor that takes autoextend into account when monitoring the oracle tablespaces? If you need a query to perform this:

CODE

select distinct
a.tablespace_name,
SUM(a.bytes)/1024/1024 "CurMb",
SUM(decode(b.maxextend, null, A.BYTES/1024/1024, b.maxextend*8192/1024/1024)) "MaxMb",
(SUM(a.bytes)/1024/1024 - round(c."Free"/1024/1024)) "TotalUsed",
(SUM(decode(b.maxextend, null, A.BYTES/1024/1024, b.maxextend*8192/1024/1024)) - (SUM(a.bytes)/1024/1024 - round(c."Free"/1024/1024))) "TotalFree",
round(100*(SUM(a.bytes)/1024/1024 - round(c."Free"/1024/1024))/(SUM(decode(b.maxextend, null, A.BYTES/1024/1024, b.maxextend*8192/1024/1024)))) "UPercent"
from
dba_data_files a,
sys.filext$ b,
(SELECT d.tablespace_name , sum(nvl(c.bytes,0)) "Free" FROM dba_tablespaces d,DBA_FREE_SPACE c where d.tablespace_name = c.tablespace_name(+) group by d.tablespace_name) c
where a.file_id = b.file#(+)
and a.tablespace_name = c.tablespace_name
GROUP by a.tablespace_name, c."Free"/1024
order by round(100*(SUM(a.bytes)/1024/1024 - round(c."Free"/1024/1024))/(SUM(decode(b.maxextend, null, A.BYTES/1024/1024, b.maxextend*8192/1024/1024)))) desc

Thanks,

Edward Moscardini

[Edward.Moscardin...]

I opened case 00025148 for this enhancement(fix).

As a work around we created an oracle function from the query below and used "Oracle Query Monitor" to run that function and report on the returned text.

We're in the process of altering the query to add the names of the tablespaces in crit/warn status.

CODE

select
decode(
max(round(100*(SUM(a.bytes)/1024/1024 - round(c."Free"/1024/1024))/(SUM(decode(b.maxextend, null, A.BYTES/1024/1024, b.maxextend*8192/1024/1024))))),
100, 'CRIT',
99, 'CRIT',
98, 'CRIT',
97, 'CRIT',
96, 'CRIT',
95, 'CRIT',
94, 'WARN',
93, 'WARN',
92, 'WARN',
91, 'WANN',
90, 'WARN',
89, 'WARN',
88, 'WARN',
87, 'WARN',
86, 'WARN',
85, 'WARN',
  'OK') STATUS
from
dba_data_files a,
sys.filext$ b,
(SELECT d.tablespace_name , sum(nvl(c.bytes,0)) "Free" FROM dba_tablespaces d,DBA_FREE_SPACE c where d.tablespace_name = c.tablespace_name(+) group by d.tablespace_name) c
where a.file_id = b.file#(+)
and a.tablespace_name = c.tablespace_name
GROUP by a.tablespace_name, c."Free"/1024