#!/usr/bin/ksh
#whatfreespacegraph ()
#------------------------------------------------------------------------------#
#    Module Name:  whatfreespacegraph  (c) Copyright Michael Barone 2012       #
#                                                                              #
#    Purpose:      Database Freespace and Database Freespace Graph.            #
#                                                                              #
#    Maintenance                                                               #
#    Date          Author        Description                                   #
#    -----------   ------------  --------------------------------------------  #
#    10-Oct-2009   M.Barone      Module design/creation                        #
#------------------------------------------------------------------------------#
{
        DIRNAM=$(dirname $0)
        ORASID=$(echo $CONTEXT_NAME | cut -f1 -d'_')
        HNAME=$(uname -n)
 
        #----------------------------------------------------------------------#
        #  TEST:  Number ($#) of arguments passed to this function?            #
        #----------------------------------------------------------------------#
        if      [ $# = 0 ]
        then
            clear
            echo
            echo  " #--------------------------------------------------------#"
            echo  " # whatfreespacegraph:    Oracle Database Freespace Graph #"
            echo  " #--------------------------------------------------------#"
            echo
 
            #------------------------------------------------------------------#
            #  Trap:  Set CNTL-C CNTL-D CNTL-\ Before Password Protection      #
            #------------------------------------------------------------------#
            trap        "stty echo; return"     2
            trap        "stty echo; return"     3
            stty -echo
 
            read PASSWD?" Please Enter the Oracle APPS Passwd:  "
 
            stty echo
            echo
            #------------------------------------------------------------------#
            #  Trap:  Reset CNTL-C CNTL-D CNTL-\ After Password Protection     #
            #------------------------------------------------------------------#
            trap        2
            trap        3
 
            clear
        else
            PASSWD=$1
            clear
        fi
 
#------------------------------------------------------------------------------#
#  Initialize                                                                  #
#------------------------------------------------------------------------------#
        SPOOLNM=/tmp/whatfreespacegraph_$$.txt
 
#------------------------------------------------------------------------------#
#  SQL*Plus ("Here-Document")                                                  #
#------------------------------------------------------------------------------#
sqlplus  -s  /nolog  <<-END_FILE
 
        CONNECT  APPS/$PASSWD
 
        SPOOL ${SPOOLNM}
 
        clear   BREAKS
        clear   BUFFER
        clear   COLUMNS
        clear   COMPUTES
        clear   SCREEN
        clear   SQL
        clear   TIMING
 
        set     feedback        off
        set     flush           on
        set     heading         on
        set     pause           off
        set     space           1
        set     termout         on
        set     verify          on
        set     linesize        80
        set     pagesize        999
 
        prompt #---------------------------------------------------------------#
        prompt #- What (whatfreespacegraph)                                   -#
        prompt #---------------------------------------------------------------#
 
        COLUMN  datevalue       NOPRINT NEW_VALUE       DATEVAR
        COLUMN  timevalue       NOPRINT NEW_VALUE       TIMEVAR
 
        COLUMN  tblsp  FORMAT A18       HEADING 'Tablespace|Name'
        COLUMN  sizem  FORMAT 9999999   HEADING 'Used|Space(Mb)'
        COLUMN  freem  FORMAT 9999999   HEADING 'Free|Space(Mb)'
        COLUMN  msize  FORMAT 9999999   HEADING 'Max|Size(Mb)'
        COLUMN  mfree  FORMAT 9999999   HEADING 'Max|Free(Mb)'
        COLUMN  frpct  FORMAT 9999999   HEADING 'Free|Percent'
        COLUMN  uspct  FORMAT A12       HEADING 'Used|Percent|Graph'

        BREAK  on REPORT

 
        TTITLE  CENTER  '${ORASID} (${HNAME}) E-Business Freespace Graph'   -
                LEFT    DATEVAR                                     skip 1  -
                CENTER  'eBusiness Suite'                                   -
                LEFT    TIMEVAR                                     skip 2
 
        alter session set NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';

        COMPUTE sum of sizem freem msize mfree on report

        SELECT  TO_CHAR(sysdate, 'DD-MON-YY')                   datevalue,
                TO_CHAR(sysdate, 'HH24:MI:SS')                  timevalue,
                tablespace_name                                 tblsp,
                size_mb                                         sizem,
                free_mb                                         freem,
                max_size_mb                                     msize,
                max_free_mb                                     mfree,
                TRUNC((max_free_mb/max_size_mb) * 100) AS       frpct,
                RPAD(' '|| 
                RPAD('X',ROUND((max_size_mb-max_free_mb)/max_size_mb*10,0),
                     'X'),11,'-') AS                            uspct
        FROM   (SELECT  a.tablespace_name,
                        b.size_mb,
                        a.free_mb,
                        b.max_size_mb,
               a.free_mb + (b.max_size_mb - b.size_mb) AS       max_free_mb
        FROM   (SELECT tablespace_name,
                       TRUNC(SUM(bytes)/1024/1024) AS           free_mb
                FROM   dba_free_space
                GROUP BY tablespace_name) a,
               (SELECT tablespace_name,
                       TRUNC(SUM(bytes)/1024/1024) AS           size_mb,
                       TRUNC(SUM(GREATEST(bytes,maxbytes))/1024/1024) AS max_size_mb
                FROM   dba_data_files
                GROUP BY tablespace_name) b
        WHERE  a.tablespace_name = b.tablespace_name
       )
ORDER BY tablespace_name;

 
END_FILE
 
print
print  "Please see  /tmp/whatfreespacegraph_$$.txt"
print
}

