/* ************************************************ */
/*                                                  */
/* fnctn:   Total Free Space Available as well as   */
/*   the number of extents for all database objects */
/*                                                  */
/* name:    @c:\oracle\sql\tot_fs01.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 Available' -
       right 'Page: ' format 999 sql.pno skip 2
rem set echo on

spool tot_fs01.rslt

/* **************************** */
/* build views to hold the data */
/* **************************** */

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;

/* *************************** */
/* format the report to detail */
/*   the freespace available   */
/* *************************** */

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
     AND   D.TABLESPACE_NAME LIKE '%TEMP%';

DROP VIEW SUM_DBA_DATA_FILES;

DROP VIEW SUM_DBA_FREE_SPACE;

/* ************************* */
/* format the extent numbers */
/* ************************* */

set pagesize 60 linesize 132 verify off
set space 6
ttitle left 'Date: ' format a18 dba_date -
 center 'Count Of Extents For Database Objects' -
       right 'Page: ' format 999 sql.pno skip 2
rem set echo on

column tablespace heading Tablespace
column object heading Object
column owner heading Owner
column type heading Type
column extents heading Extents
column bytes heading Bytes

select substr(tablespace_name,1,15) tablespace,
       substr(segment_name,1,32) object,
       substr(owner,1,15) owner, segment_type type,
       count(extent_id) extents, sum(bytes) bytes
from dba_extents
where tablespace_name like '%TEMP%'
group by tablespace_name, segment_name, owner, segment_type
order by tablespace_name, segment_name, owner;

spool off;
