RE: Database Link Timer

  • From: "Aponte, Tony" <AponteT@xxxxxxx>
  • To: <oracle-l@xxxxxxxxxxxxx>
  • Date: Tue, 6 Jul 2004 16:59:35 -0400

Sorry for the late reply and HTH.  Early in my career I hacked up a
shell script to do what you mentioned.  The script fires up SQLPlus in
the background and kills it after N seconds if it doesn't complete.  The
sample below simulates a 30 second delay.  The default timeout is 10
seconds but it's show below overridden with 15 to compensate for any
startup latency.

Regards
Tony APonte

cat run_sql_and_kill_after_10_seconds_if_not_complete.sh
#!/bin/sh
set -x
# Establish run-time environment.
ORACLE_SID=3D<<<>>>>; export ORACLE_SID
ORAENV_ASK=3DNO; export ORAENV_ASK
. oraenv
#-
#
AlarmHandler() {
 echo "Got SIGALARM, cmd took too long."
 KillSubProcs
 exit 14
}
#
KillSubProcs() {
 kill ${CHPROCIDS:-$!}
 if [ $? -eq 0 ] ; then
    echo "Sub-processes killed." ;
    exit 1
 fi
}
#
SetTimer() {
  DEF_TOUT=3D${1:-10};
  if [ $DEF_TOUT -ne 0 ] ; then
     sleep $DEF_TOUT && kill -14 $$ &
     CHPROCIDS=3D"$CHPROCIDS $!"
     TIMERPROC=3D$!
  fi
}
#
UnsetTimer() {
  kill $TIMERPROC
}
#
# main()
#
trap AlarmHandler 14
#
SetTimer 15
#
sqlplus internal/ <<EOF &
select * from dual;
exec DBMS_LOCK.SLEEP(30);
exit;
EOF
#
CHPROCIDS=3D"$CHPROCIDS $!"
wait $!
UnsetTimer
echo "All done."
exit 0

-----Original Message-----
From: Mercadante, Thomas F [mailto:thomas.mercadante@xxxxxxxxxxxxxxxxx]
Sent: Thursday, June 24, 2004 8:09 AM
To: 'oracle-l@xxxxxxxxxxxxx'
Subject: Database Link Timer


All,
=20
I have the following query in a stored package:
=20
select 1 into loc_variables from dual@databaselink
<mailto:dual@databaselink> ;
=20
What I am doing is checking the availabilty of the remote database
periodically.  The bad news is that we sometimes have network problems.
If
the above would fail, then I trap the error and move on.  But sometimes,
it
is just painfully slow (like it doesn't fail but hangs for a long time).
=20
Is there some way I can put a timer on the above that says if it can't
complete the query in 10 seconds, then fail?
=20
thanks
=20
Tom Mercadante=20
Oracle Certified Professional=20



----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Other related posts: