Wednesday, April 13, 2011

space alert on filesystem

Links to this post
#!/bin/ksh
LOG=/export/home/space.log
cat /dev/null > $LOG
df -g | sort -rnk 4 | grep -vE '^-|Free|proc' | awk '{sub (/%/,"",$4);print $4 "\t" $7}' | while rea
d output;
do
echo $output
usep=$(echo $output | awk '{ print $1}' | cut -d'%' -f1 )
partition=$(echo $output | awk '{ print $2 }' )
#echo $usep
#echo $partition
if [ $usep -ge 70 ]; then
echo "Alert :$usep% used on $partition on $(hostname) \n" >> $LOG
fi
done
####################
if [[ -s $LOG ]]; then
#echo "sending mail"
mailx -s "space alert on $(hostname)" jignesh.kankrecha@xxx.com < $LOG
else
#echo "not sending"
fi;

Thursday, April 7, 2011

Oracle output into excel sheet

Links to this post
Hi All

this is the proto-type format of shell script embedded with SQL script , which output into a excel sheet.
=========================================
#!/bin/ksh
echo $ORACLE_HOME
myfile=/home/oracle/awr/perf.sql
emailfile=/home/oracle/awr/MY_TEST_FILE.xls
echo $myfile
echo $emailfile
$ORACLE_HOME/bin/sqlplus -s xxx/yyy@conn_identifier @$myfile
cat $emailfile | uuencode $emailfile | mailx -s "report" jignesh.kankrecha@jpmchase.com
exit;

============================================

####################################
put $myfile which is /home/oracle/awr/perf.sql in below format
####################################
SET MARKUP HTML ON ENTMAP ON SPOOL ON PREFORMAT OFF;
set linesize 125
set numwidth 10
set pagesize 50
col TEXT FOR A30
col ELAPSED_TIME_SEC for 99999999
SET ECHO OFF;
SPOOL MY_TEST_FILE.xls;
--your SQL STATEMENT HERE;
spool off;
exit;
####################################