tab_check.txt

set lines 200
col group_number for 99
col state for a15
col name for a20
col total_gb for 999999.99
col free_gb for 999999.99
col free_percent for 99.99
col care for a5
set linesize 131
set pagesize 200
set termout off
set trimspool on
set serveroutput on
set lines 200 pages 300
col tablespace_name for a30
select u.*,round(p.avg_use_per_day_mb,1) avg_used_per_day_mb,round((max_size_mb-size_used_mb)/p.avg_use_per_day_mb) tbs_exhaust_days,
case when  u.MAX_FREE_RATE<7 and round((max_size_mb-size_used_mb)/p.avg_use_per_day_mb)<30 then '*'
                        else null end care
from (select a.tablespace_name,b.size_used_mb,a.data_size_mb,
         round(100-b.size_used_mb/a.data_size_mb*100) free_rate,
         a.max_size_mb, a.max_size_mb - b.size_used_mb free_mb,
         round(100-b.size_used_mb/a.max_size_mb*100) max_free_rate
         from
                 (select tablespace_name,round(sum(bytes/1024/1024)) data_size_mb,round(sum(case when maxbytes>bytes then maxbytes else bytes end)/1024/1024) max_size_mb
         from dba_data_files group by tablespace_name) a,
                 (select tablespace_name,round(sum(bytes/1024/1024)) size_used_mb
         from dba_segments group by tablespace_name) b
         where a.tablespace_name=b.tablespace_name order by 6 desc,4 desc) u,
         (select name,avg(use_per_day_mb)+0.0001 avg_use_per_day_mb 
                  from (select x.name,x.rdate,(x.used_blocks-lag(x.used_blocks) over (partition by name order by rdate))*y.block_size/1024/1024 use_per_day_mb