Search Exchange
Search All Sites
Nagios Live Webinars
Let our experts show you how Nagios can help your organization.Login
Directory Tree
Directory
vegatripy
byvegatripy, August 11, 2015
The problem with the original query is if you have a tablespace with mixed autoextend and fixed size datafiles (it's rare, but it's possible).
If you want to fix it, you can replace the query from lines 247 to 282 with this one that I've made:
select
z.TABLESPACE_NAME,
round(((Mbytes_used - Mbytes_free) / Mbytes_used) * 100) usage_pct,
round(decode(MAXMBYTES, 34359721984, 0, (Mbytes_used - Mbytes_free) / MAXMBYTES * 100)) max_pct,
case
when (select count(distinct AUTOEXTENSIBLE) from dba_data_files where TABLESPACE_NAME = z.TABLESPACE_NAME) > 1 then 'YES/NO'
else (select distinct AUTOEXTENSIBLE from dba_data_files where TABLESPACE_NAME = z.TABLESPACE_NAME)
end as AUTOEXTENSIBLE
from
( select TABLESPACE_NAME,
sum (Mbytes_used) Mbytes_used,
sum (Mbytes_free) Mbytes_free,
sum (MAXMBYTES) MAXMBYTES
from
(
select substr(df.TABLESPACE_NAME,1,length(df.TABLESPACE_NAME)-4) TABLESPACE_NAME,
df.BYTES/1024/1024 Mbytes_used,
nvl(fs.BYTES/1024/1024, 0) Mbytes_free,
df.MAXBYTES/1024/1024 MAXMBYTES,
df.AUTOEXTENSIBLE
from
(
select TABLESPACE_NAME||LPAD(FILE_ID,4,0) TABLESPACE_NAME,
sum(BYTES) BYTES,
AUTOEXTENSIBLE,
decode(AUTOEXTENSIBLE, 'YES', sum(MAXBYTES), sum(BYTES)) MAXBYTES
from dba_data_files
group by TABLESPACE_NAME||LPAD(FILE_ID,4,0),
AUTOEXTENSIBLE
)
df
LEFT OUTER JOIN
(
select a.TABLESPACE_NAME||LPAD(FILE_ID,4,0) TABLESPACE_NAME,
sum(a.BYTES) BYTES
from dba_free_space a
group by TABLESPACE_NAME||LPAD(FILE_ID,4,0)
)
fs
ON df.TABLESPACE_NAME=fs.TABLESPACE_NAME
order by df.TABLESPACE_NAME desc
) a
group by TABLESPACE_NAME
) z
order by 1 asc
/
If you want to fix it, you can replace the query from lines 247 to 282 with this one that I've made:
select
z.TABLESPACE_NAME,
round(((Mbytes_used - Mbytes_free) / Mbytes_used) * 100) usage_pct,
round(decode(MAXMBYTES, 34359721984, 0, (Mbytes_used - Mbytes_free) / MAXMBYTES * 100)) max_pct,
case
when (select count(distinct AUTOEXTENSIBLE) from dba_data_files where TABLESPACE_NAME = z.TABLESPACE_NAME) > 1 then 'YES/NO'
else (select distinct AUTOEXTENSIBLE from dba_data_files where TABLESPACE_NAME = z.TABLESPACE_NAME)
end as AUTOEXTENSIBLE
from
( select TABLESPACE_NAME,
sum (Mbytes_used) Mbytes_used,
sum (Mbytes_free) Mbytes_free,
sum (MAXMBYTES) MAXMBYTES
from
(
select substr(df.TABLESPACE_NAME,1,length(df.TABLESPACE_NAME)-4) TABLESPACE_NAME,
df.BYTES/1024/1024 Mbytes_used,
nvl(fs.BYTES/1024/1024, 0) Mbytes_free,
df.MAXBYTES/1024/1024 MAXMBYTES,
df.AUTOEXTENSIBLE
from
(
select TABLESPACE_NAME||LPAD(FILE_ID,4,0) TABLESPACE_NAME,
sum(BYTES) BYTES,
AUTOEXTENSIBLE,
decode(AUTOEXTENSIBLE, 'YES', sum(MAXBYTES), sum(BYTES)) MAXBYTES
from dba_data_files
group by TABLESPACE_NAME||LPAD(FILE_ID,4,0),
AUTOEXTENSIBLE
)
df
LEFT OUTER JOIN
(
select a.TABLESPACE_NAME||LPAD(FILE_ID,4,0) TABLESPACE_NAME,
sum(a.BYTES) BYTES
from dba_free_space a
group by TABLESPACE_NAME||LPAD(FILE_ID,4,0)
)
fs
ON df.TABLESPACE_NAME=fs.TABLESPACE_NAME
order by df.TABLESPACE_NAME desc
) a
group by TABLESPACE_NAME
) z
order by 1 asc
/