Tablespace info

Hello friends,

Came across this script to calculate the tablespace data and display in a neat format.

REM ***************************************************************************
REM Script  : tablespaceinfo.sql
REM Usage   : @tablespaceinfo (no parameters)
REM Purpose : To report space usage information for all the tablespaces in database.
REM Versions: 11g
REM
REM Additional info:
REM  Explantion of Columns in Result Set:
REM  Tablespace:         The Name of the tablespace
REM  Megs Max Size:      Maximum size the tablespace will occupy on disk.
REM  Megs Allocated:     Space currently allocated on disc.
REM  Megs used:          Space currently be used within the Megs Allocated.
REM  Megs Free Till Max: Free space untill the tablespace  extends
REM                        + Free space in what has not yet been allocated
REM  Megs Free Till Ext: Free space until the tablespace extends.
REM  %Used:              Percentage of used space based on the Megs Max Size value.
REM  %Free:              Percentabe of free space based on the Megs Max Size value.
REM ***************************************************************************

REM set parameters
SET linesize 300 pagesize 1000 feedback off echo off TRIMSPOOL ON
SET tab off
SET underline =

REM ttitle left -
REM "Tablespace Usage Report" skip 2

COLUMN tablespace_name     heading "Tablespace Name"      format a30 truncate justify c
COLUMN total_maxspace_mb   heading "Megs|Max Size"        format 9G999G999 justify c
COLUMN total_allocspace_mb heading "Megs|Allocated"       format 9G999G999 justify c
COLUMN used_space_mb       heading "Megs|Used"            format 9G999G999 justify c
COLUMN free_space_ext_mb   heading "Megs Free|Current"    format 9G999G999 justify c
COLUMN free_space_mb       heading "Megs Free|Till Max"   format 9G999G999 justify c
COLUMN pct_used            heading "% Used|Till MAX"      format 999 justify c
COLUMN pct_free            heading "%|Free"               format 999 justify c
COLUMN warning             heading "WARNING"              format a12 justify c

break on report
COMPUTE SUM label "Total Size:" of total_maxspace_mb total_allocspace_mb used_space_mb -
    free_space_ext_mb free_space_mb (used_space_mb/total_maxspace_mb)*100 on report

select /*+ALL_ROWS */
       rpad(alloc.tablespace_name,30,'.') tablespace_name,
       alloc.total_maxspace_mb,
       alloc.total_allocspace_mb,
       (alloc.total_allocspace_mb - free.free_space_mb) used_space_mb,
       free.free_space_mb free_space_ext_mb,
       free.free_space_mb+(alloc.total_maxspace_mb-alloc.total_allocspace_mb) free_space_mb,
       ((alloc.total_allocspace_mb - free.free_space_mb)/alloc.total_maxspace_mb)*100 pct_used,
     -- ((free.free_space_mb+(alloc.total_maxspace_mb-alloc.total_allocspace_mb))/alloc.total_maxspace_mb)*100 pct_free
     CASE WHEN ((alloc.total_allocspace_mb - free.free_space_mb)/alloc.total_maxspace_mb)*100 > 90
          THEN '<*******'
          WHEN free.free_space_mb > 8192
                AND ((alloc.total_allocspace_mb - free.free_space_mb)/alloc.total_maxspace_mb)*100 < 50
          THEN 'SHRINK THIS'
          ELSE NULL END warning
 FROM (SELECT tablespace_name,
              ROUND(SUM(CASE WHEN maxbytes = 0 THEN bytes ELSE maxbytes END)/1048576) total_maxspace_mb,
              ROUND(SUM(bytes)/1048576) total_allocspace_mb
         FROM dba_data_files
    --        WHERE file_id NOT IN (SELECT FILE# FROM v$recover_file)
        GROUP BY tablespace_name) alloc,
      (SELECT tablespace_name,
              SUM(bytes)/1048576 free_space_mb
         FROM dba_free_space
    --       WHERE file_id NOT IN (SELECT FILE# FROM v$recover_file)
        GROUP BY tablespace_name) free
WHERE alloc.tablespace_name = free.tablespace_name (+)
ORDER BY pct_used DESC;
PROMPT

CLEAR COLUMNS
CLEAR BREAKS
CLEAR COMPUTES
TTITLE OFF