Shell Programming and Scripting

 #! /bin/ksh
host=`uname -n`
rptFile="/user_files/monitor/data/chkdbSpace.txt"
Emailist="myemail@dom.com\
"
sqlplus -s id/passwd << -
clear columns
column tablespace format a25
column total_mb format 999,999,999.99
column used_mb format 999,999,999,999.99
column free_mb format 999,999,999.99
column pct_used format 999.99
column graph format a25 heading "GRAPH (X=5%)"
column status format a10
compute sum of total_mb on report
compute sum of used_mb on report
compute sum of free_mb on report
break on report
SET WRAP OFF
SET FEEDBACK OFF
SET ECHO OFF
SET LINESIZE 130
SET PAGESIZE 50
SPOOL $rptFile
select total.ts tablespace,
DECODE(total.mb,null,'OFFLINE',dbat.status) status,
total.mb total_mb,
NVL(total.mb - free.mb,total.mb) used_mb,
NVL(free.mb,0) free_mb,
DECODE(total.mb,NULL,0,NVL(ROUND((total.mb - free.mb)/(total.mb)*100,2),100)) pct_used
from
(select tablespace_name ts, sum(bytes)/1024/1024 mb from dba_data_files group by tablespace_name) total,
(select tablespace_name ts, sum(bytes)/1024/1024 mb from dba_free_space group by tablespace_name) free,
dba_tablespaces dbat
where total.ts=free.ts(+) and
total.ts=dbat.tablespace_name
UNION ALL
select sh.tablespace_name,
'TEMP',
SUM(sh.bytes_used+sh.bytes_free)/1024/1024 total_mb,
SUM(sh.bytes_used)/1024/1024 used_mb,
SUM(sh.bytes_free)/1024/1024 free_mb,
ROUND(SUM(sh.bytes_used)/SUM(sh.bytes_used+sh.bytes_free)*100,2) pct_used
FROM v\$temp_space_header sh
GROUP BY tablespace_name
order by 6
/
SPOOL OFF
ttitle off
rem clear columns
-
###As an attachment in Solaris:
( echo 'see attached file';uuencode /user_files/monitor/data/chkdbSpace.txt chkdbSpace.txt ) | mailx -s "$host SMG DB Space Report" "$Emailist"
exit 0

TABLESPACE STATUS TOTAL_MB USED_MB FREE_MB PCT_USED
------------------------- ---------- --------------- ------------------- --------------- --------
WNP_201410_IDX_TS ONLINE 2,700.00 1.00 2,699.00 .04
WNP_201409_IDX_TS ONLINE 2,350.00 1.00 2,349.00 .04

....etc

TABLESPACE STATUS TOTAL_MB USED_MB FREE_MB PCT_USED 
------------------------- ---------- --------------- ------------------- --------------- -------- 
WNP_201410_IDX_TS ONLINE 2,700.00 1.00 2,699.00 .04 WNP_201409_IDX_TS ONLINE 2,350.00 1.00 2,349.00 .04 WNP_201408_IDX_TS ONLINE 2,100.00 1.00 2,099.00 .05