isitdevops.com/databases

— spc_sum
— select summary of size of ts datafiles and free space
col sum_k      form 999,999,999
col sum_blks   form 99,999,999
col max_chnk_k form 99,999,999
col ts_name    form a20        word wrap
col num_chnk   form 999,999
break on ts_name nodup
set pause on
set pages 32 trimspool on
accept v_ts char prompt ‘Enter the tablespace (or leave null)> ‘
spool spc_sum.lst
select ddf.tablespace_name ts_name
      ,’alloc’             ord
      ,sum(ddf.blocks)     sum_blks
      ,sum(ddf.bytes/1024) sum_k
      ,max(ddf.bytes/1024) max_chnk_k
      ,count(*)            num_chnk
from dba_data_files ddf
where tablespace_name like upper(nvl(‘&v_ts’,’%’))||’%’
group by tablespace_name
union
select dfs.tablespace_name ts_name
      ,’free’              ord
      ,sum(dfs.blocks)     sum_blks
      ,sum(dfs.bytes/1024) sum_k
      ,max(dfs.bytes/1024) max_chnk_k
      ,count(*)            num_chnk
from dba_free_space dfs
where tablespace_name like upper(nvl(‘&v_ts’,’%’))||’%’
group by tablespace_name
order by ts_name,ord
/
spool off
clear colu
— EOF