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

Comments embedded.
On Oct 8, 8:16 am, shilpa <shilpa.uttar..._at_gmail.com> wrote:
> On Oct 8, 4:44 pm, Tim X <t..._at_nospam.dev.null> wrote:
>
>
>
>
>
> > shilpa <shilpa.uttar..._at_gmail.com> writes:
> > > On Aug 31, 11:45 am, sriv..._at_hotmail.com 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- Hide quoted text -
>
> > - Show quoted text -
>
> Hi,
>
> First I want to identify Lost database connection at database level.
> Based on that I want to perform some updataions in my tables.
>

What sort of 'updataions' (which isn't a word)?

> 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.
>

I'll ask again, what SORT of updates are you wanting to perform?

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

Why are you not coding the application properly to encapsulate all related changes into a single unit of work? Should a connection be lost the unit of work would be incomplete, a rollback would occur.and you would not have 'mismatch tables'. Also, in 10g and later releases you can AUDIT CONNECT and get all of this information from the DBA_AUDIT_TRAIL view and the RETURNCODE of the last operation, which includes 'lost' connections.

> Thanks
>
> Shilpa- Hide quoted text -
>
> - Show quoted text -

David Fitzjarrell Received on Wed Oct 08 2008 - 12:27:02 CDT