Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Database Link Timer (oh, one more thing)

RE: Database Link Timer (oh, one more thing)

From: Mark W. Farnham <mwf_at_rsiz.com>
Date: Thu, 24 Jun 2004 10:18:49 -0400
Message-ID: <KNEIIDHFLNJDHOOCFCDKAEPGEOAA.mwf@rsiz.com>


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



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 Thu Jun 24 2004 - 09:22:13 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US