Query that provides tablespace usage report taking autoextend into consideration
Comments
-
This kind of query is most helpful. We use a slightly different query. If a tablespace is at 1800m and is allowed to grow to 2000m in increments of 500m it could look like there is room to grow when in fact there is not, since a single increment would take it past the limit. The following query takes that into account.
set pagesize 50000
column tablespace_name format a23 head "Tablespace Name"
column file_count format 9990 head "Files"
column tot_avail format 99,999,999,990 head "Available"
column tot_free format 99,999,999,990 head "Free"
column tot_used format 99,999,999,990 head "Used"
column big_hole format 9999,999,990 head "Biggest Hole"
column pct_used format 999.99 head "% Used"
column pctavail format 999.99 head "%Avail"
column auto_extend format a4 head "Auto"
column real_extend format a3 head "Can"
column hole_count format 9,990 head "Holes"
set linesize 132
set trimspool on
set echo off
set termout on
spool free.lis
select
t.tablespace_name,
t.file_count,
t.tot_avail,
f.tot_free,
f.big_hole,
f.hole_count,
t.tot_avail - nvl(f.tot_free,0) tot_used,
((t.tot_avail - nvl(f.tot_free,0)) * 100) / tot_avail pct_used,
((t.tot_avail - nvl(f.tot_free,0)) * 100) / afb4 pctavail,
t.auto_extend,
t.real_extend
from
(select
tablespace_name,
count(*) hole_count,
max(bytes) big_hole,
sum(bytes) tot_free
from
sys.dba_free_space
group by
tablespace_name) f,
(select
tablespace_name,
count(*) file_count,
sum(bytes) tot_avail,
sum(maxbytes) afb1, sum(increment_by) afb2, sum(bytes) afb3, sum(greatest(maxbytes,bytes)) afb4,
least(0,sum(maxbytes) - sum(increment_by*(bytes/blocks))-sum(bytes)) afb,
max(autoextensible) auto_extend,
max(decode(autoextensible,'YES',decode(least(0,maxbytes-(increment_by*8192)-bytes),0,'YES','NO'),'NO')) real_extend
from
sys.dba_data_files
group by
tablespace_name) t
where
t.tablespace_name = f.tablespace_name (+)
/
spool off0