#!/usr/bin/ksh
# whatasm ()
#------------------------------------------------------------------------------#
# Module Name: whatasm #
# #
# Purpose: Report on the Oracle Automatic Storage Management. #
# #
# 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)

 clear
 echo
 echo " #--------------------------------------------------------#"
 echo " # whatasm: Oracle Database ASMM Parameters #"
 echo " #--------------------------------------------------------#"
 echo
 echo " USPS LogOn as oracle-account
 echo " Connecting as SYSDBA"
 sleep 2

 clear
 ORANAM=${ORASID}

#------------------------------------------------------------------------------#
# SQL*Plus ("Here-Document") #
#------------------------------------------------------------------------------#
sqlplus -s /nolog <<-END_FILE

 CONNECT / AS SYSDBA

 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

 SPOOL /tmp/whatasm_$$.txt

 CLEAR SCREEN

 prompt ================================================================
 prompt == Automated Storage Management V$ASM_DISKGROUP (View) ==
 prompt ================================================================
 BREAK ON REPORT
 COMPUTE SUM LABEL 'Total' OF fremb ON REPORT
 COMPUTE SUM LABEL 'Total' OF totmb ON REPORT

 COLUMN datevalue NOPRINT NEW_VALUE DATEVAR
 COLUMN timevalue NOPRINT NEW_VALUE TIMEVAR
 COLUMN nammb FORMAT A20 Heading 'ASM Name'
 COLUMN state FORMAT A8 Heading 'ASM Name'
 COLUMN fremb FORMAT 99999999999 Heading 'ASM Free(Mb)'
 COLUMN totmb FORMAT 999999999999 Heading 'ASM Total(Mb)'
 COLUMN hotmb FORMAT 999999 Heading 'Hot(Mb)'
 COLUMN colmb FORMAT 9999999 Heading 'Cold(Mb)'
 COLUMN votfl FORMAT A6 Heading 'Voting|Files'

 TTITLE CENTER 'Automated Storage Managment (ASM)' -
 LEFT DATEVAR -
 RIGHT 'Page:' FORMAT 999 SQL.pno skip 1 -
 LEFT TIMEVAR -
 CENTER 'whatasm -- Sorted by NAME' skip 2

 SELECT TO_CHAR(sysdate, 'DD-MON-YY') datevalue,
 TO_CHAR(sysdate, 'HH24:MI:SS') timevalue,
 name nammb,
 state,
 free_mb fremb,
 total_mb totmb,
 hot_used_mb hotmb,
 cold_used_mb colmb,
 voting_files votfl
 FROM v\$asm_diskgroup
 ORDER BY name;

 EXIT;
END_FILE

print
print "Please see /tmp/whatasm_$$.txt"
print
}