Tuesday, March 17, 2009

Find Tablespace Usage Percent

I have written a sql query which would output the usage percent of all the tablespaces in a given database. Although lots of tablespace usage queries are available, most of them, if not all, utilise dba_free_space view as the tool of measurement.

It is well known that this view is inadequate when datafiles have autoextend feature turned on. I have tried to address this inadequacy in my query.


Caveat: This query is not optimised for performance. Hence, you might observe slow performance.




set linesize 100
set pagesize 10000
set feedback off verify off
col tablespace_name format a18
col num_files format 999
col total_space format 9999999.99
col free_space format 9999999.99
col used_percent format 999.99



select /*+ parallel(tbs) */
tbs.tablespace_name,
count(tbs.file_id) num_files,
round(sum(TOTAL_SPACE),2) TOTAL_SPACE_MB,
sum(FREE_SPACE) FREE_SPACE_MB,
round(((sum(tbs.USED_SPACE)/sum(tbs.TOTAL_SPACE)) * 100),2) USED_PERCENT
from
(select
ddf.file_id file_id,
ddf.file_name,
ddf.tablespace_name tablespace_name,
round(decode(ddf.autoextensible,'NO',ddf.bytes/1024/1024,'YES',ddf.maxbytes/1024/1024),2) "TOTAL_SPACE",
ddf.autoextensible,
case
when ddf.maxbytes < ddf.bytes
then round(((ddf.bytes/1024/1024) - nvl((dfs.free_space),0)),2)
else round(ddf.bytes/1024/1024,2)
END "USED_SPACE",
case
when ddf.maxbytes < ddf.bytes
then nvl(dfs.free_space,0)
else round(((ddf.maxbytes/1024/1024) - (ddf.bytes/1024/1024)),2)
END "FREE_SPACE"
/*, case autoextensible
when 'NO' then round(((((ddf.bytes/1024/1024) - nvl(dfs.free_space,0)) / (ddf.bytes/1024/1024)) * 100),2)
when 'YES' then round((((ddf.bytes/1024/1024) / (ddf.maxbytes/1024/1024)) * 100),2)
END '%' "USED_PERCENT" */
from dba_data_files ddf,
(select file_id, round(sum(bytes)/1024/1024,2) free_space from dba_free_space group by file_id) dfs
where
ddf.file_id = dfs.file_id(+)
) tbs
group by tbs.tablespace_name
order by 5 desc;



No comments: