/* ************************************************ */
/*                                                  */
/* fnctn:   Total Free Space                        */
/*                                                  */
/* name:    @/home/oracle/free_spc.sql              */
/* date:    25-DEC-95                               */
/* creator: Rick Kupcunas (Oracle)                  */
/*                                                  */
/* ************************************************ */

set feedback off
set termout off
break on today
column today new_value dba_date
select to_char(sysdate, 'mm/dd/yy hh:miam') today
       from dual;

break on instance
column instance new_value instance_name
select substr(name,1,4) instance
       from v$database;
clear breaks
set termout on

set pagesize 60 linesize 80 verify off
set space 2
ttitle left 'Date: ' format a18 dba_date -
       center 'Total Free Space - ' format a4 instance_name -
       right 'Page: ' format 999 sql.pno skip 2
rem set echo on

spool free_spc.rslt

column FileNum	    format 999	    heading 'File|Num'
column FileName     format A32      heading 'File Name' 
column Tablespace   format A8       heading 'Tblspace'
column Allocated    format 99,999    heading 'Size|(MB)' 
column div1         format A3       heading ' | '
column Used         format 99,999    heading 'Used|(MB)'
column Unused       format 99,999    heading 'Unused|(MB)'
column Pct_Used     format 999.99   heading 'Used|(%)'
column div2         format A3       heading ' | ' 
column TotalFree    format 9,999    heading 'Free|(MB)'
column Pct_Free     format 999.99   heading 'Free|(%)'
column MaxFree      format 9,999    heading 'MaxFree'
column status       format A3       heading 'STA'

column oneMB noprint new_value MB

compute sum of Allocated on tablespace 
compute sum of Allocated on report 
compute sum of Used      on tablespace 
compute sum of Used      on report 
compute sum of TotalFree on tablespace 
compute sum of TotalFree on report 
compute sum of Unused    on tablespace 
compute sum of Unused    on report 

break on Tablespace;

select x.tablespace_name                       Tablespace,
       /* substr(to_char(x.file_id,999), 1,4)     FileNum, */
       x.file_name                             FileName,
       x.bytes/(1024*1024)                     Allocated,
       (x.bytes-sum(y.bytes))/(1024*1024)      Used,
       x.bytes/(1024*1024) - (x.bytes-sum(y.bytes))/(1024*1024)      
                                               Unused,
    /*   ((x.bytes-sum(y.bytes))/
       x.bytes)*100                            Pct_Used,
       sum(y.bytes)/(1024*1024)                TotalFree,
       (sum(y.bytes)/x.bytes)*100              Pct_Free,
       max(y.bytes)/(1024*1024)                MaxFree,  */
       substr(x.status,1,3)                   status
from sys.dba_data_files x , 
     sys.dba_free_space  y 
where x.file_id = y.file_id 
group by substr(to_char(x.file_id,999), 1,4),
         x.file_name,
         x.tablespace_name,
         x.bytes,
         x.status  
order by 1,2,3;

commit;
spool off;
