To send Diskspace report on the mail
MAX=95
EMAIL=USER@domain.com
PART=sda1
USE=`df -h |grep $PART | awk '{ print $5 }' | cut -d'%' -f1`
if [ $USE -gt $MAX ]; then
echo "Percent used: $USE" | mail -s "Running out of disk space" $EMAIL
fi
To send tablespace report on the mail
1)#!/bin/bash
export ORACLE_SID=M2MP2SA1
export ORACLE_HOME=/oragdsp_db/db
Email="sender mail id,receiver mail id"
#Attachment_File="/tmp/output1.csv"
#Subject="Tablespace_Report"
RETVAL=`sqlplus / as sysdba <<EOF
set lines 2000 pages 2000
SELECT /*+ first_rows */ d.tablespace_name "TS Name", NVL(a.bytes / 1024 / 1024, 0) "Total size MB", NVL(a.bytes - NVL(f.bytes, 0), 0)/1024/1024 "Used Size MB", NVL((a.bytes - NVL(f.bytes, 0)) / a.bytes * 100, 0) "Used %",
a.autoext "Autoextend On", NVL(f.bytes, 0) / 1024 / 1024 "Free size MB", d.status "Status", a.count "# of Datafiles", d.contents "Contents", d.extent_management "EXT MGMT", d.segment_space_management "Seg Space MGMT" FROM sys.dba_tablespaces d, (select tablespace_name,
sum(bytes) bytes, count(file_id) count, decode(sum(decode(autoextensible, 'NO', 0, 1)), 0, 'NO', 'YES') autoext from dba_data_files group by tablespace_name) a,
(select tablespace_name, sum(bytes) bytes from dba_free_space group by tablespace_name) f WHERE d.tablespace_name = a.tablespace_name(+) AND d.tablespace_name = f.tablespace_name(+) AND NVL((a.bytes - NVL(f.bytes, 0)) / a.bytes * 100, 0) >80
AND NOT d.contents = 'UNDO' AND NOT (d.extent_management = 'LOCAL' AND d.contents = 'TEMPORARY') AND d.tablespace_name like '%%';
exit;
EOF`
export EMAIL='sender mail id';
echo "$RETVAL" >>/tmp/output1.csv
grep -w "no rows selected" /tmp/output1.csv 1>>/dev/null
if [ $? != 0 ]
then
echo "$RETVAL" | mutt -a "/tmp/output1.csv" -s "Subject line" -- "$Email" < /tmp/output1.csv
else
echo "No table space usage is above threshold"
fi
2)
#!/bin/bash
# Set the environment variables
export ORACLE_HOME=/opt/oracle/product/102/DB
export ORALCE_SID=oradb
export PATH=$PATH:$ORACLE_HOME/bin
# Declar the vaiable
NOTIFY_LIST=user_name@abc.com
DIFF_FILE=/tmp/pass_exp.log
# Get expiry date
EXP_DATE=`chage -l oracle grep "Password Expires" awk '{print $4"-"$3"-"$5}'sed 's/,//'`
# Calculte in how many days password will expire
sqlplus usr/pass@$ORACLE_SID<<>
--CREATE TABLE pwd_expire (expire_date date);
INSERT INTO pwd_expire values (TO_DATE('$EXP_DATE','DD-MON-YYYY'));
COMMIT;
SET ECHO OFF FEEDBACK OFF
SPOOL $DIFF_FILE
SELECT ROUND(expire_date-sysdate) diff FROM pwd_expire;
SPOOL OFF
--DROP TABLE pwd_expire;
TRUNCATE TABLE pwd_expire;
EXIT
EOF
DAYS=`tail -2 $DIFF_FILEhead -1awk '{print $1}'`
if [ $DAYS -le 5 ]
then
mailx -s "Oracle Password will expires in $DAYS" $NOTIFY_LIST < /dev/null
fi
