Re: Can we trigger event for abnormal loss of connection to oracle databse?

From: Tim X <>
Date: Wed, 08 Oct 2008 22:44:00 +1100
Message-ID: <>

shilpa <> writes:

> On Aug 31, 11:45 am, wrote:
>> Hi shilpa,
>> When your application looses the connection/session, you are not
>> connected to the database !
>> So there is no question of executing any database code isnt it ?
>> You have to handle this condition in your application code.
>> regards
>> srivenu
> Hi Srivenu,
> Sorry for too late reply..i was on other things and
> missed this topic...
> I want event which is triggered when connection lost. So when database
> level event trigger will able to catch and execute stored procedure.

Its not clear exactly what you mean (at least not to me). Are you saying that you want some mechanism to fire off when a client loses connection to your database or are you talking about something like a database link becoming unusable because the other end is down?

For the database link, its probably doable, but for a client? Problems I can see straight away include

  1. How would you know which of the connections to the database are of interest? i.e. an app connecting via JDBC compared to a connection from sqlplus or which of possibly many sqlplus or JDBC or whatever connection methods) to consider.
  2. How will you distinguish between planned/deliberate closing of the session compared to loss of connection from something like a network failure? Is this an issue?
  3. Do you have to worry about multiple connections. for example, its not unusual for a web application to have multiple connections running at the same time.

I think you need to provide more details of what you want to achieve. It is likely that your desired outcome can be achieved through a much easier approach. Maybe describe the problem and why you think that being able to fire off a trigger when the connection is lost will solve the problem. this will enable us to consider possible solutions that are not constrained by the rrequirement to detect lost connections. For example, maybe the client can implement some sort of 'is alive' process whereby it updates a timestamp at regular intervals and if more than x number of intervals go by without an update, the database can assume the connection has been lost/terminated and take any necessary action? If the client cannot be modified in such a manner, maybe there is some other mechanism or heuristic, such as assuming a lost connection if there is no DML of a particular type or on a particular object for a specified amount of time? It could even be that the solution doesn't even involve determination or approximation of lost connectivity and all you really need to do is monitor for some change in state that isn't related to connections at all.

My gut is telling me that your question is not on the right track. This is partly because there are so many variables involved in a 'database connection'. You have the lower level network layer (TCP/IP), the various Oracle network interfaces and to some extent, limited abilities within the database to determine much about the connections. I'd also be wary of any setup which required some sort of action to be taken when a connection is lost in the sense that generally, reliance on active connections or the need to take some sort of speical action when a connection is lost, would make me very nervous as these are the sort of things that can create unexpected or unpredictable outcomes and that is seldom a good thing.

Of course, there are many different requirements and many different problems, so your question couuld easily be legitimate and have a solid grounding. However, without more details, you are unlikely to get much assistance or any suggestions that are going to provide any substantial aid.

On the other hand, if you present an interesting problem, its quite likely that others who read this group will find it a challenge and will both willingly and eagerly try to help solve it.

HTH Tim  

tcross (at) rapttech dot com dot au
Received on Wed Oct 08 2008 - 06:44:00 CDT

Original text of this message