| 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) (really last thing)
all except run query would be host commands. some languages handle
co-routines in a single overall process, so you could do that, as an
alternative to two separate processes. the point of doing this from within a
sqlplus session with host is to avoid the cost of connection and building a
program that knows anything at all about Oracle, you could also do this with
oci stuff from a C program.
mwf
-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Mark W. Farnham
Sent: Thursday, June 24, 2004 10:19 AM
To: oracle-l_at_freelists.org
Subject: 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
 |
 |