##  DBA Command Post - ts_size.sql


col T_space  format a15    
col fname    format a34  
col Use_Pct  format 999.0 heading Used%
col Total    format 99999
col Used_Mg  format 9999.0
col Free_Mg  format 9999.0

define Blk_size=2048			/* Set Oracle Block Size */

break on T_Space on report
compute sum of Total   on report
compute sum of Used_Mg on report
compute sum of Free_Mg on report

select decode(x.online$,1,x.name,
              substr(rpad(x.name,14),1,14)||' OFF')  T_Space,
       replace(replace(A.file_name,'/databases/',''),'.dbf','') Fname,
       round((f.blocks*&Blk_Size)/(1024*1024)) Total, 
       round(sum(s.length*&Blk_Size)/(1024*1024),1) Used_Mg,
       round(((f.blocks*&Blk_Size)/(1024*1024)) 
         - nvl(sum(s.length*&Blk_Size)/(1024*1024),0), 1) Free_Mg,
       round( sum(s.length*&Blk_Size)/(1024*1024)
         / ((f.blocks*&Blk_Size)/(1024*1024)) * 100, 1) Use_Pct
from   sys.dba_data_files A, sys.uet$ s, sys.file$ f, sys.ts$ X
where  x.ts#     = f.ts# 
and    x.online$ in (1,2)					/* Online !! */
and    f.status$ = 2					/* Online !! */
and    f.ts#     = s.ts# (+)
and    f.file#   = s.file# (+)
and    f.file#   = a.file_id
group  by x.name, x.online$, f.blocks, A.file_name
/

select decode(x.online$,1,x.name,
              substr(rpad(x.name,14),1,14)||' OFF') T_Space,
       'Total 'Fname,
       round(sum(distinct (f.blocks+(f.file#/1000))*&Blk_Size)
               / (1024*1024) ) Total,
       round(sum(s.length*&Blk_Size)/(1024*1024),1) Used_Mg,
       round(sum(distinct (f.blocks+(f.file#/1000))*&Blk_Size)
               / (1024*1024)
           - nvl(sum(s.length*&Blk_Size)/(1024*1024),0), 1) Free_Mg,
       round(((sum(s.length*&Blk_Size)/(1024*1024) )
          / (sum(distinct (f.blocks+(f.file#/1000))*&Blk_Size)
               / (1024*1024) )) * 100, 1) Use_Pct
from   sys.uet$ s, sys.file$ f, sys.ts$ x
where  x.ts#     = f.ts#
and    x.online$ in (1,2)					/* Online !! */
and    f.status$ = 2					/* Online !! */
and    f.ts#     = s.ts# (+)
and    f.file#   = s.file# (+)
group  by x.name, x.online$
order  by 1
/
clear breaks
tti off
set verify on