Re: limit temp usage before error or out of space condition?
- From: Kellyn Pot'vin <kellyn.potvin@xxxxxxxxx>
- To: "Josh.Collier@xxxxxxxxxxxx" <Josh.Collier@xxxxxxxxxxxx>, "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 22 Oct 2013 14:22:56 -0700 (PDT)
You can easily set this up as a metric extension, but to be honest, I used to
do it as shell script. Here is the script and you can steal what you need from
it:
function mail_page {
echo|mailx -s "${HOST} resumable transactions found in ${ORACLE_SID} at
${DATE}" ${MAIL_LIST}
\rm -f ${OUT_FILE}
}
export ORAENV_ASK=NO
. oraenv > /dev/null
export ORAENV_ASK=YES
DATE=`date '+%m/%d/%y %H:%M:%S'`
MAIL_LIST="<email_add>"
HOST=`hostname`
WORKING_DIR=~/scripts
OUT_FILE=${WORKING_DIR}/`basename $0`_${ORACLE_SID}.lst
let ERR_CNT=0
ERR_CNT=$(sqlplus -s << EOF
/ as sysdba
set feedback off
set pagesize 0
set timing off
select count(*) from dba_resumable where suspend_time is not null and name
not like 'User SYSTEM(5)%';
EOF)
echo "error count is: " ${ERR_CNT}
if [ ${ERR_CNT} -ne 0 ]
then
mail_page
exit 2
else
echo "everything OK"
fi
exit 0
Kellyn Pot'Vin
Senior Technical Consultant
Enkitec
DBAKevlar.com
RMOUG Director of Training Days Conference
~Tombez sept fois, se relever huit!
On Tuesday, October 22, 2013 2:56 PM, Josh Collier <Josh.Collier@xxxxxxxxxxxx>
wrote:
Yes, I use resumable already. Just wanted to see if I could catch them before
they suspend. I have lowered the warning threshold on the full condition for
the tablespace. It doesn't look like OEM has anything to monitor temp usage by
query tho. I could write something in perl but I was hoping to avoid having to
build a wheel.
From: Kellyn Pot'vin [mailto:kellyn.potvin@xxxxxxxxx]
Sent: Tuesday, October 22, 2013 1:47 PM
To: oracle-l@xxxxxxxxxxxxx; Josh Collier
Subject: Re: limit temp usage before error or out of space condition?
Look into setting up resumable in your database. .. your life will be so much
better for it. ...:)
Sent from Yahoo Mail on
Android<http://overview.mail.yahoo.com/mobile/?.src=Android>
________________________________
From: Josh Collier
<Josh.Collier@xxxxxxxxxxxx<mailto:Josh.Collier@xxxxxxxxxxxx>>;
To: oracle-l@xxxxxxxxxxxxx<mailto:oracle-l@xxxxxxxxxxxxx>
<oracle-l@xxxxxxxxxxxxx<mailto:oracle-l@xxxxxxxxxxxxx>>;
Subject: limit temp usage before error or out of space condition?
Sent: Tue, Oct 22, 2013 8:38:02 PM
Does anyone have any ideas how to limit temp usage before the query at hand
fills up the temp tablespace and then suspends everyone else until the
condition is cleared? Is there a way to monitor queries that are using too much
temp and send an email? Maybe in OEM?
Thanks for your thoughts,
Josh C.
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
