Tablespace info 2

Another script to do the same task, data about the tablespaces.

REM ***************************************************************************
REM Category: Admin
REM Purpose : To report space usage information for all the tablespaces in database.
REM Versions: 9i, 10g, 11g
REM
REM Additional info:
REM  Explantion of Columns in Result Set:
REM  tablespace_name:    The Name of the tablespace
REM  total:              Sum of data files bytes.
REM  used:               Space currently used.
REM  free:               Free space available.
REM  pct_used:           Percentage of Free space.
REM  warning             Highlight warning when above 90%
REM ***************************************************************************

SET linesize 300 pagesize 1000 feedback off echo off TRIMSPOOL ON
SET tab off

column tablespace_name format a30 justify c heading 'TABLESPACE_NAME'
column total format 9G999G999 justify c heading 'TOTAL(MB)'
column used format 9G999G999 justify c heading 'USED(MB)'
column free format 9G999G999 justify c heading 'FREE(MB)'
column pct_used format 990.99 justify c heading 'PCT_USED'
column warning format a10 justify c heading 'WARNING'
compute sum of total on report
compute sum of used on report
compute sum of free on report
break on report

SELECT rpad(a.tablespace_name,30,'.') tablespace_name,
       a.total/1024/1024 total,
       (a.total - nvl(b.free,0))/1024/1024 used,
       nvl(b.free, 0)/1024/1024 free,
       ((a.total - nvl(b.free,0)) / a.total) * 100 pct_used,
       decode(sign(((1-nvl(b.free,0)/a.total)*100)-90),0,' ',1,'<*******',' ') warning
FROM
      (SELECT tablespace_name
             ,SUM(bytes) total
       FROM dba_data_files
       GROUP BY tablespace_name) a
     ,(SELECT tablespace_name
             ,SUM(bytes) free
       FROM dba_free_space
       GROUP BY tablespace_name) b
WHERE a.tablespace_name = b.tablespace_name(+)
ORDER BY a.tablespace_name;