vegatripy

Update with this query

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 /

Reviewed 11 years ago