| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Database Link Timer (oh, one more thing)
oh, one more thing.
There is a light weight way to do this if you're on a system with daemons and signalling.
Start process 1, the time checker, this guy can spawn the sqlplus session so
it is easy to hand off the os pid.
This guy is set up to trap two signals (you can use one, but then you're
counting on never missing one to keep the meaning of alternate signals
straight.)
On getting signal one, you grab the time and start a run down timer that is
your idea of too long for the link test
On getting signal two, you grab the time and start a run down timer that is
your idea of too long since you've been notified of a test being made,
oh, and you mark the other run down timer "satisfied."
If you don't get a signal before a timer runs out, you know whether to report that the link is slow or that the child is missing in action
In the sqlplus session,
before the query, signal "I'm starting the link test."
run query
after the query signal "Done for now."
sleep for desire inter test time(up to you to pick a non-cpu intensive sleep
on your system)
loop
now if your primary systems don't run an OS that handles this easily, you can probably use a workstation that runs something that does handle this stuff routinely.
mwf
PS: there may be packaged commercial solutions as well.
-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Mercadante, Thomas F
Sent: Thursday, June 24, 2004 8:09 AM
To: 'oracle-l_at_freelists.org'
Subject: Database Link Timer
All,
I have the following query in a stored package:
select 1 into loc_variables from dual_at_databaselink <mailto:dual_at_databaselink> ;
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).
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?
thanks
Tom Mercadante
Oracle Certified Professional
 |
 |