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

From: shilpa <>
Date: Wed, 8 Oct 2008 06:16:21 -0700 (PDT)
Message-ID: <>

On Oct 8, 4:44 pm, Tim X <> wrote:
> 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.
> Tim
> --
> tcross (at) rapttech dot com dot au- Hide quoted text -
> - Show quoted text -


First I want to identify Lost database connection at database level. Based on that I want to perform some updataions in my tables.

Like one event LOGOFF in case of ORACLE . But don't know how to catch this event and perform updations. Please tell me solution for the same.

More specifically when connection lost i want to run stored procedure which will update my dayabase entries if any mismatch in tables.


Shilpa Received on Wed Oct 08 2008 - 08:16:21 CDT

Original text of this message