1

I have to check automatically if a connection to another database in oracle is vaild. I want a trigger to perform different actions based on whether the connection is vaild while it's running or not. It takes about 40 seconds every time to return ORA-12170 (Connection timeout) if i try to check the connection by using a query including the database link to the remote database. Is there any way to set a much shorter time interval for the timeout or to abort the query if it takes that much time? I just want to get a quick response if the connection is working. The trigger is called several times every view seconds, so i can't wait for 40 seconds every time it's called when the connection is interrupted. Im using Oracle XE 11g.

Improve this question
4
  • The interval seems very short, is it really required to check the connection this number of times between every few seconds?
    – Husam Mohamed
    Feb 20, 2018 at 7:17
  • Perhaps if you say why you need to check the Oracle connection every few seconds it might help to pinpoint a solution.
    – BriteSponge
    Feb 20, 2018 at 10:23
  • I have to check it every time because i have to move data from one database to the other every time something is inserted in the local datatable. If the connection gets interrupted all the data sould be stored in the local datatable instead. It takes 40 seconds to get something (the error) back if there is no connection. Unfortunately this causes massive data loss because it prevents new data from being inserted into the local datatable while the query is still processing to return the connection error.
    – Gandalf The Gay
    Feb 20, 2018 at 10:42
  • It sounds to me like you're looking at things through the wrong end of the telescope. WHY do you have to MOVE data every time something is inserted? It really, really sounds like you are trying to implement a sub-optimal solution to some as-yet-unspecified business problem. I'll bet if you focus on the business need instead of the technique, you could find a much simpler solution.
    – EdStevens
    Feb 20, 2018 at 22:34

1 Answer 1

Reset to default
1

That timeout can be configured with the SQLNET.OUTBOUND_CONNECT_TIMEOUT parameter.

For example, place the below in sqlnet.ora, which results in outbound connections timing out after 10 seconds without answer:

SQLNET.OUTBOUND_CONNECT_TIMEOUT=10
Improve this answer
1
  • +1 and just in case someone is wondering where that sqlnet.ora file is, look in: $ORACLE_HOME\network\admin
    – Amit Naidu
    Dec 18, 2018 at 19:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.