Re: Connections which go bad
From: Mark D Powell <Mark.Powell2_at_hp.com>
Date: Tue, 12 Jan 2010 07:20:30 -0800 (PST)
Message-ID: <62b5ecbe-2323-402c-8d1e-6ce854c4b9b8_at_r5g2000yqb.googlegroups.com>
On Jan 12, 9:59 am, ddf <orat..._at_msn.com> wrote:
> On Jan 12, 3:41 am, Troels Arvin <tro..._at_arvin.dk> wrote:
>
>
>
>
>
> > Hello,
>
> > I'm having trouble with JDBC transactions to an Oracle which are dropped
> > after a while of inactivity.
>
> > The situation can be reproduced using sqlplus:
>
> > E.g. at time t1, I connect with sqlplus and run a simple query like
> > ===================================================
> > SQL> SELECT banner FROM v$version;
>
> > BANNER
> > ---------------------------------------------------
> > Oracle Database 11g Release 11.1.0.7.0 - 64bit Production
> > PL/SQL Release 11.1.0.7.0 - Production
> > CORE 11.1.0.7.0 Production
> > TNS for Linux: Version 11.1.0.7.0 - Production
> > NLSRTL Version 11.1.0.7.0 - Production
> > ===================================================
>
> > All fine. Now, I leave the terminal and return an hour or two later, and
> > perform a "SELECT banner FROM v$version;" again. The sqlplus session is
> > silent for around five minutes, and then responds:
> > ===================================================
> > SELECT banner FROM v$version
> > *
> > ERROR at line 1:
> > ORA-03113: end-of-file on communication channel
> > Process ID: 5103
> > Session ID: 218 Serial number: 22182
> > ===================================================
>
> > The sqlplus client in this case is v. 11.2 64 bit on Linux.
>
> > Can some parameter be set so that a keep-alive like method is employed on
> > the wire to keep connections alive?
>
> > --
> > Regards,
> > Troels Arvin
>
> I expect one is already set to disconnect inactive sessions; look in
> your sqlnet.ora file for sqlnet.expire_time and note its setting as it
> governs when the connection is considered 'dead' and disconnected.
> Comment this entry to disable this functionality.
>
> David Fitzjarrell- Hide quoted text -
>
> - Show quoted text -
Date: Tue, 12 Jan 2010 07:20:30 -0800 (PST)
Message-ID: <62b5ecbe-2323-402c-8d1e-6ce854c4b9b8_at_r5g2000yqb.googlegroups.com>
On Jan 12, 9:59 am, ddf <orat..._at_msn.com> wrote:
> On Jan 12, 3:41 am, Troels Arvin <tro..._at_arvin.dk> wrote:
>
>
>
>
>
> > Hello,
>
> > I'm having trouble with JDBC transactions to an Oracle which are dropped
> > after a while of inactivity.
>
> > The situation can be reproduced using sqlplus:
>
> > E.g. at time t1, I connect with sqlplus and run a simple query like
> > ===================================================
> > SQL> SELECT banner FROM v$version;
>
> > BANNER
> > ---------------------------------------------------
> > Oracle Database 11g Release 11.1.0.7.0 - 64bit Production
> > PL/SQL Release 11.1.0.7.0 - Production
> > CORE 11.1.0.7.0 Production
> > TNS for Linux: Version 11.1.0.7.0 - Production
> > NLSRTL Version 11.1.0.7.0 - Production
> > ===================================================
>
> > All fine. Now, I leave the terminal and return an hour or two later, and
> > perform a "SELECT banner FROM v$version;" again. The sqlplus session is
> > silent for around five minutes, and then responds:
> > ===================================================
> > SELECT banner FROM v$version
> > *
> > ERROR at line 1:
> > ORA-03113: end-of-file on communication channel
> > Process ID: 5103
> > Session ID: 218 Serial number: 22182
> > ===================================================
>
> > The sqlplus client in this case is v. 11.2 64 bit on Linux.
>
> > Can some parameter be set so that a keep-alive like method is employed on
> > the wire to keep connections alive?
>
> > --
> > Regards,
> > Troels Arvin
>
> I expect one is already set to disconnect inactive sessions; look in
> your sqlnet.ora file for sqlnet.expire_time and note its setting as it
> governs when the connection is considered 'dead' and disconnected.
> Comment this entry to disable this functionality.
>
> David Fitzjarrell- Hide quoted text -
>
> - Show quoted text -
I would say disabling unused connected sessions may well be a good thing. If someone is going to be gone 2 hours then he or she should close their session and open a new one upon returning.
However, the sqlnet parameters are all connections or none and if you have applications that connect then can go silent for hours at a time this may be undesirable.
Sessions can also be terminated via the user profile which has an idle time setting and also through the use of the database resource manager so if you do not find any sqlnet settings that seem to apply check these.
HTH -- Mark D Powell -- Received on Tue Jan 12 2010 - 09:20:30 CST