-- df_use.sql
-- ----------
-- last known change:  April 22, 2003

set term off
@isql_prefs

var block_size number
begin
  select value
    into :block_size
    from v$parameter
   where name = 'db_block_size';
end;
/
set term on


accept ts_like char prompt "  Enter a tablespace like string [%]: "

break on report on tbs_nm skip 1

compute sum of   used on tbs_nm
compute sum of   megs on tbs_nm
compute sum of  avail on tbs_nm
compute sum of extend on tbs_nm

COLUMN FILE_NM FORMAT A38 heading "File name"
COLUMN TBS_NM FORMAT A16 TRUNCATE heading "Tablespace"
COLUMN MEGS FORMAT 99,990
COLUMN USED FORMAT 99,990 heading "   USED"
COLUMN AVAIL FORMAT 99,990
COLUMN chunk FORMAT 99,990
COLUMN extend FORMAT 99,990
COLUMN PRCNT_USED FORMAT a44 heading "0 * 1 * 2 * 3 * 4 * 5 * 6 * 7 * 8 * 9 * Full"


SELECT df.tablespace_name 		TBS_NM
     , df.file_name 			FILE_NM
     , df.bytes/1048576 		MEGS
     , round((ext.used_bytes/1048576), 0) USED
     , free.free_bytes/1048576 		AVAIL
     , free.free_chunk/1048576	 	CHUNK
     , (extra.maxextend*:block_size)/1048576
			                extend
     , rpad (' ' || rpad (
                    RPAD ('X',ROUND( ( (ext.used_bytes)/ greatest( NVL(extra.maxextend*:block_size,df.bytes), df.bytes) )*40 ,0 ) , 'X')
                   ,ROUND( ( (df.bytes)/greatest( NVL(extra.maxextend*:block_size,df.bytes), df.bytes) )*40 ,0 )
                   ,'-'
                   )
      ,41
      ,' '
      ) || '!'						PRCNT_USED
  FROM DBA_DATA_FILES DF
     , (SELECT file_id
             , SUM(DECODE(bytes,NULL,0,bytes)) used_bytes
          FROM dba_extents
         GROUP BY file_id
       ) ext
     , (SELECT sum(bytes) free_bytes
             , MAX(bytes) free_chunk
             , file_id
          FROM dba_free_space
         GROUP BY file_id
       ) free
     , sys.filext$ extra
 WHERE df.file_id = ext.file_id (+)
   AND df.file_id = free.file_id (+)
   AND df.file_id = extra.file# (+)
   AND df.tablespace_name like nvl(upper('&ts_like'), '%') escape '\'
union all
SELECT df.tablespace_name          TBS_NM
     , df.file_name                        FILE_NM
     , df.bytes/1048576            MEGS
     , tmp_use.used   USED
     , tmp_use.free   AVAIL
     , to_number(null)   CHUNK
     , (maxbytes)/1048576          extend
     , rpad(' ' ||
            rpad(
                 RPAD ('X'
                      ,ROUND(
                             (
                              (tmp_use.used *40)/
                              (greatest( NVL(maxbytes,df.bytes), df.bytes) /1048576)
                             )
                             ,0
                       )
                      ,'X'
                 )
                 ,ROUND( ( (df.bytes)/greatest( NVL(maxbytes,df.bytes), df.bytes) )*40 ,0 )
                 ,'-'
            )
      ,41
      ,' '
      ) || '!'                                             PRCNT_USED
  FROM DBA_temp_FILES DF
     , (select file_id
             , sum (decode (owner, 0, 0, bytes)) /1048576 used
             , sum (decode (owner, 0, bytes, 0)) /1048576 free
          from v$temp_extent_map
         group by file_id) tmp_use
 WHERE df.file_id = tmp_use.file_id (+)
   AND df.tablespace_name like nvl(upper('&ts_like'), '%') escape '\'
 ORDER BY TBS_NM
        , FILE_NM
/