/* ************************************************ */
/*                                                  */
/* fnctn:   Total Free Space                        */
/*                                                  */
/* name:    @c:\oracle\sql\freet.sql                */
/* date:    12-MAY-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:mi AM') today
       from dual;
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' -
       right 'Page: ' format 999 sql.pno skip 2
rem set echo on

spool freet.rslt

CREATE VIEW SUM_DBA_DATA_FILES (SUM_BYTES, SUM_BLOCKS, TABLESPACE_NAME) AS
       SELECT SUM(BYTES), SUM(BLOCKS), TABLESPACE_NAME
       FROM SYS.DBA_DATA_FILES
       GROUP BY TABLESPACE_NAME;

CREATE VIEW SUM_DBA_FREE_SPACE (SUM_BYTES, SUM_BLOCKS, TABLESPACE_NAME) AS
       SELECT SUM(BYTES), SUM(BLOCKS), TABLESPACE_NAME
       FROM SYS.DBA_FREE_SPACE
       GROUP BY TABLESPACE_NAME;

COLUMN TABLESPACE_NAME HEADING "  Tablespace|     Name" format a15
COLUMN SUM_BYTES HEADING "Total   |Bytes   "
COLUMN SUM_BLOCKS HEADING "Total |Blocks " format 999999
COLUMN ABS(F.SUM_BYTES) HEADING "Free   |Bytes   "
COLUMN ABS(F.SUM_BLOCKS) HEADING "Free  |Blocks " format 999999
COLUMN ROUND(1-F.SUM_BYTES/D.SUM_BYTES,4)*100 HEADING "%  Used" FORMAT 990.99
COLUMN 100-(ROUND(1-F.SUM_BYTES/D.SUM_BYTES,4)*100) HEADING "%  Free" FORMAT 990.99

SELECT D.TABLESPACE_NAME, D.SUM_BYTES, D.SUM_BLOCKS, ABS(F.SUM_BYTES),
     ABS(F.SUM_BLOCKS), ROUND(1 - F.SUM_BYTES / D.SUM_BYTES,4) * 100,
     100 - (ROUND(1 - F.SUM_BYTES / D.SUM_BYTES,4) * 100)
     FROM SUM_DBA_DATA_FILES D, SUM_DBA_FREE_SPACE F
     WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME;

ttitle off
set space 3
COLUMN SUM(D.SUM_BYTES) HEADING "Database Bytes" format 999999999999
COLUMN SUM(D.SUM_BLOCKS) HEADING "Database Blocks"
COLUMN SUM(F.SUM_BYTES) HEADING "Free    Bytes" format 999999999999
COLUMN SUM(F.SUM_BLOCKS) HEADING "Free Blocks"
COLUMN ROUND(1-SUM(F.SUM_BYTES)/SUM(D.SUM_BYTES),4)*100 HEADING "% Used" FORMAT 90.99
COLUMN 100-(ROUND(1-SUM(F.SUM_BYTES)/SUM(D.SUM_BYTES),4)*100) HEADING "% Free" FORMAT 90.99

SELECT SUM(D.SUM_BYTES), SUM(D.SUM_BLOCKS), SUM(F.SUM_BYTES), SUM(F.SUM_BLOCKS),
     ROUND(1 - SUM(F.SUM_BYTES) / SUM(D.SUM_BYTES),4) * 100,
     100 - (ROUND(1 - SUM(F.SUM_BYTES) / SUM(D.SUM_BYTES),4) * 100)
     FROM SUM_DBA_DATA_FILES D, SUM_DBA_FREE_SPACE F
     WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME;

DROP VIEW SUM_DBA_DATA_FILES;

DROP VIEW SUM_DBA_FREE_SPACE;

exit
