Temp tablespace usage in oracle

Script to monitor temp tablespace usage in oracle

Create a folder

mkdir -p /u01/app/oracle/script/chk_tmp_usage

copy below scripts and replace <SID> with your SID and paste these scripts in /u01/app/oracle/script/chk_tmp_usage

Now you will have three scripts in /u01/app/oracle/script/chk_tmp_usage folder

tmp_usage_<SID>.sh
select_<SID>.sql
temp_<SID>.sql

crontab entry

*/15 * * * * /u01/app/oracle/script/chk_tmp_usage/tmp_usage_<SID>.sh <SID> <ORACLE_HOME> >> /u01/app/oracle/script/chk_tmp_usage/mail_<SID>.log 2>&1

vi /u01/app/oracle/script/chk_tmp_usage/tmp_usage_<SID>.sh
#!/usr/bin/bash

export DATO=`date +%Y%m%d.%H%M%S`
export ORACLE_SID=$1
export ORACLE_HOME=$2
export PATH=$ORACLE_HOME/bin:$PATH
PATH=$PATH:$ORACLE_HOME/bin
SCRIPT_DIR=/u01/app/oracle/script/chk_tmp_usage
LOGFILE=${SCRIPT_DIR}/temp_${ORACLE_SID}.log
MAIL_TO="xyz@gmail.com"
cd ${SCRIPT_DIR}

${ORACLE_HOME}/bin/sqlplus '/ as sysdba' @${SCRIPT_DIR}/select_${ORACLE_SID}.sql ${SCRIPT_DIR}
VAL=`cat ${SCRIPT_DIR}/select_${ORACLE_SID}.log`
for i in $VAL
do
        if [ $i -gt 70 ]
        then
                ${ORACLE_HOME}/bin/sqlplus '/ as sysdba' @${SCRIPT_DIR}/temp_${ORACLE_SID}.sql ${SCRIPT_DIR}
        else
                echo "temp usage normal"
        fi
done

VAR=`ls ${SCRIPT_DIR}`
if [ -f  "temp_${ORACLE_SID}.log" ]
then

/bin/mailx  -a ${LOGFILE} -s "Temp Usage - DB : ${ORACLE_SID} on ${DATO}" $MAIL_TO <<EOF
Hi,

Please find attached logfile
This is an automated email please do not reply to this. For any information please contact xyz@gmail.com.


Regards,
XYZ
EOF

rm -f ${SCRIPT_DIR}/temp_${ORACLE_SID}.log
fi

~]$ vi select_<SID>.sql
spool &1/select_<SID>.log
set head off
SELECT ROUND(((TABLESPACE_SIZE - NVL(FREE_SPACE,0)) / TABLESPACE_SIZE) * 100) Used from dba_temp_free_space;
spool off;
exit

~]$ vi temp_<SID>.sql
spool &1/temp_<SID>.log
set lines 9999
set feedback off
set trimspool on trimout on
col MODULE for a40
col MACHINE for a20
col SID_N_SERIAL for a12
col SPID for a10
col PROGRAM for a25
col OSUSER for a12
col TBLSPC for a6
col USERNAME for a10
select
   s.sid || ',' || s.serial# sid_n_serial,
   s.username,
   s.osuser,
   p.spid,
   s.module,
   s.machine,
   p.program,
   (sum (t.blocks) * tbs.block_size / 1024 / 1024) AS mb_used,
   t.tablespace as "TBLSPC"
 from
   v$sort_usage    t,
   v$session       s,
   dba_tablespaces tbs,
   v$process       p
 where
   t.session_addr = s.saddr
and
   s.paddr = p.addr
and
   t.tablespace = tbs.tablespace_name
group by
   s.sid,
   s.serial#,
   s.username,
   s.osuser,
   p.spid,
   s.module,
   s.machine,
   p.program,
   tbs.block_size,
   t.tablespace
having (sum (t.blocks) * tbs.block_size / 1024 / 1024) > 1000
order by
   mb_used desc;
spool off;
exit