RE: Database Link Timer

From: Aponte, Tony <AponteT_at_hsn.net>
Date: Tue, 6 Jul 2004 16:59:35 -0400
Message-ID: <5D48DB5EE592094FBE5373DFF0A4062935B513@exchsrv02.stpete.hsn.net>

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_at_labor.state.ny.us] Sent: Thursday, June 24, 2004 8:09 AM
To: 'oracle-l_at_freelists.org'
Subject: Database Link Timer

All,
=20
I have the following query in a stored package: =20
select 1 into loc_variables from dual_at_databaselink <mailto:dual_at_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_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://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_at_freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Tue Jul 06 2004 - 15:56:59 CDT