Skip to Main Content
  • Questions
  • How does the profile parameter 'idle_time' work?

Breadcrumb

Easter

Question and Answer

Tom Kyte

Thanks for the question, Suresh.

Asked: April 03, 2001 - 9:55 pm UTC

Answered by: Tom Kyte - Last updated: March 26, 2020 - 2:51 am UTC

Category: Database - Version: 8.1.6

Viewed 100K+ times! This question is

You Asked

Hi,

We need to use the profile parameter idle_time in our database. Even if we specify 10 minutes as the parameter value, the user session expires after 11 minutes or 12 minutes. It appears that some oracle process periodically checks the user sessions for idle time out. But we are not able to find out any more details on this parameter. Can you tell us which Oracle process takes care of expiring the user session and in which V$ view the user session is marked as expired?

Thanks for your help.

and we said...

PMON is responsible for sniping the sessions. It will do this periodically. HOWEVER

A session will continue to show as idle even after the idle_time for that user,
as specified in that user's profile, has expired. When the user attempts to run
a transaction against the database after the idle_time has expired, the
database will disconnect the user by terminating the session. After this, the
session will no longer show in v$session. So, even if the session appears to be idle for a duration slightly more then your 10 minutes -- it is already "dead", it just doesn't show as dead yet. PMON will eventually snipe the session, marking it dead in v$session.



and you rated our response

  (44 ratings)

We're not taking reviews currently, so please try again later if you want to add a review.

Reviews

April 04, 2001 - 12:47 pm UTC

Reviewer: Pauline from U.S.A


About Inactive Session

April 04, 2001 - 9:23 pm UTC

Reviewer: Jerrywang from CHINA

I find there is some sessions that status is marked 'INACTIVED' in my Oracle Server,I do not know whether it is normal.Can I let it disapper automatic by setting some paramters?

Connect time not working.

June 03, 2003 - 2:52 am UTC

Reviewer: SUBBARAO

I need to enforce the resource limit of connect time for a session to 30 min. As part of this exercise I am trying to simulate the case with 1 min. I am not getting the result. Here are the steps I have followed:
Plese correct me.

I have set the connect_time = 1 for a profile, and assigned the profile to the scott user.

select * from user_resource_limits a 
where a.resource_name in ('IDLE_TIME','CONNECT_TIME');

RESOURCE_NAME    LIMIT
----------------------
IDLE_TIME    UNLIMITED
CONNECT_TIME    1


Connected to the database with scott user and execute the following query:


select to_char(sysdate,'dd-mm-yyyy hh24 mi') from dual;

TO_CHAR(SYSDATE,
----------------
03-06-2003 13 08

Now I have left the session with out executing any transactions.
After some time(2 min) I have again executed the same query. Here I expected a message saying the session is killed. But I am getting the result of the query again.

SQL> /

TO_CHAR(SYSDATE,
----------------
03-06-2003 13 10 

Tom Kyte

Followup  

June 03, 2003 - 7:31 am UTC

so, did you set RESOURCE_LIMIT=true in your init.ora

and wait longer then what you did.

for all we know that could be

13:08:59
13:10:00

(eg: 1 minute)

Thanks

June 04, 2003 - 1:02 am UTC

Reviewer: A reader

Thanks, problem resolved after setting the resource_limit parameter in init file.

Thanks - one clarification question

December 15, 2003 - 12:18 pm UTC

Reviewer: Doug from Salt Lake City, Utah

Tom,
You stated PMON will eventually snipe an expired session. If the SESSIONS parm in the init.ora is set to 200, and say ther are 190 active sessions and 10 that have 'expired' via the idle_time setting, can 10 new sessions be started or do they wait?

Tom Kyte

Followup  

December 15, 2003 - 3:53 pm UTC

they will be SNIPED -- still there -- until the client acknowledges that it was "killed".

PMON won't "remove it" from v$session -- the session will be dead but still there.

you'll have hit max sessions at that point.

Why sniped sessions are not removed

December 15, 2003 - 5:28 pm UTC

Reviewer: Arun Gupta from Harrisburg, PA USA

Tom,
Is there a reason you can think of why PMON doesn't clean sniped sessions? The user whose session has been sniped can no longer do anything with that session, except get an error message.
Thanks


Tom Kyte

Followup  

December 16, 2003 - 6:42 am UTC

exactly -- "except get an error message"

if we just killed everything, they would get:

ORA-3113 End of file on communication channel

which would significantly increase calls to your helpdesk/support as it surely looks like a bug.

it is waiting to tell the client "sorry, you are dead, goodbye"

December 16, 2003 - 8:47 am UTC

Reviewer: Arun Gupta from Harrisburg, PA USA

If a session is holding objects locks and is sniped before releasing the locks, are the locks still held?
Thanks

Tom Kyte

Followup  

December 16, 2003 - 11:33 am UTC

the locks and other resources are released.

Connect_Time

April 29, 2004 - 12:39 pm UTC

Reviewer: A reader

What does connect_time mean. As per the documentation it means
"Specify the total elapsed time limit for a session, expressed in minutes."
So does it mean that a session is allowed conenctivity for the time specified by this parameter. Irrespective of it is active or inactive.
For example the paremeter has been set to 15 minutes after 15 minutes the session is terminated even though it is active. Because Idle_Time restricts a session based on inactivity, so inactive status can be dealt by this parameter. So how do i use Connect_Time?
Please correct me if i am wrong.

Tom Kyte

Followup  

April 29, 2004 - 1:02 pm UTC

connect time is exactly that -- the duration of the connection.

if you want a connection to last no more than N minutes/hours, you would use this

Disconnect Session before Idle Time

May 24, 2004 - 6:36 am UTC

Reviewer: Mohammed Osman from Riyadh, KSA

Hi Tom,

The Session is Terminated after 15 Minutes, before the Idle Time which is set to 60 Minutes in Profile.

Furnished below are the parmeters in init.ora

job_queue_processes=10
job_queue_interval=3600
resource_limit = TRUE


Is there any other parameters overriding the Idle Time Out ?

Need your advise.

Best regards,


Tom Kyte

Followup  

May 24, 2004 - 8:01 am UTC

what do job_queue* parameters have to do with this?

anyway -- dump the profile from the data dictionary view and make sure you have actually set it to 60 minutes.

Disconnect Session before Idle Time

May 25, 2004 - 3:19 am UTC

Reviewer: Mohammed Osman from Riyadh, KSA

Yes, job_queue_* has nothing to do with the Profile. It is for the DBMS_JOB process.

I was having another question of DBMS_JOB along with this Profile as this thread is dedicated to Profile (Idle Time) I removed my Question but forget to remove the parameters.

I have checked the DBA_PROFILES the Limit is 60 for Idle Time.

select profile, resource_name, limit
from dba_profiles
where profile='MIMS'
and resource_name ='IDLE_TIME'
/

PROFILE RESOURCE_NAME LIMIT
---------- --------------- ---------------
MIMS IDLE_TIME 60

Sqlnet.expire_time is not set in Server / Client.

Am I missing something ??

Environment :
Windows 2000
Oracle Release 8.1.7


Tom Kyte

Followup  

May 25, 2004 - 7:46 am UTC

You'll have to persue this with support, I tried to reproduce with 817 on XP running in vmware, but could not.


SQL> show parameter resource

NAME                                 TYPE    VALUE
------------------------------------ ------- ------
enqueue_resources                    integer 1792
resource_limit                       boolean TRUE
resource_manager_plan                string

SQL> create profile mims limit idle_time 60;

Profile created.

SQL> alter user scott profile mims;

User altered.

SQL> select profile, resource_name, limit
  2  from dba_profiles
  3  where profile = 'MIMS'
  4  and resource_name = 'IDLE_TIME';

PROFILE                        RESOURCE_NAME
------------------------------ --------------------------------
LIMIT
----------------------------------------
MIMS                           IDLE_TIME
60


SQL> exit
Disconnected from Oracle8i Enterprise Edition Release 8.1.7.4.1 - Production
With the Partitioning option
JServer Release 8.1.7.4.1 - Production

C:\Documents and Settings\Administrator>sqlplus scott/tiger

SQL*Plus: Release 8.1.7.0.0 - Production on Tue May 25 07:10:40 2004

(c) Copyright 2000 Oracle Corporation.  All rights reserved.


Connected to:
Oracle8i Enterprise Edition Release 8.1.7.4.1 - Production
With the Partitioning option
JServer Release 8.1.7.4.1 - Production

SQL> @bigdate

Session altered.

SQL> select sysdate from dual;

SYSDATE
--------------------
25-may-2004 07:10:48

SQL> /

SYSDATE
--------------------
25-may-2004 07:31:13
 

SNIPED Sessions

November 04, 2004 - 5:32 pm UTC

Reviewer: M from Columbus, OH USA.

Tom,
Is there is a way to clean up the sniped sessions so that new users can be allowed to login without hitting the max. number of sessions problem?

Tom Kyte

Followup  

November 05, 2004 - 4:04 pm UTC

you can "kill" the dedicated server at the os level

there is an enhancement request in to have this behaviour changed

connect time

March 14, 2005 - 11:42 am UTC

Reviewer: Dave

so, connect_time kicks in regardless of session in active or inactive status? Is this correct? Thanks.

Tom Kyte

Followup  

March 14, 2005 - 1:01 pm UTC

connect time is connect time yes. The amount of time you've been connected.

reviewing for locking issues

May 02, 2005 - 6:35 am UTC

Reviewer: Shahbaz from kuwait

answers are direct on target.
i like it

reader

May 13, 2005 - 7:32 am UTC

Reviewer: A reader

<>
the locks and other resources are released
<>

If the session is waiting for a resource
locks or latches, and if this wait time exceeds
idle_time setting in th profile, does the session
gets sniped, even if the session is in the middle of a
transaction and waiting for lock etc.

If so, will there be any entries in the alert log.


Tom Kyte

Followup  

May 13, 2005 - 10:33 am UTC

if waiting for a lock, you are active -- not idle.

reader

May 13, 2005 - 11:39 am UTC

Reviewer: A reader

Thanks

Does this contradict previous posting on Dec 16,2003 above
"
If a session is holding objects locks and is sniped before releasing the locks,
are the locks still held?
"

Tom Kyte

Followup  

May 13, 2005 - 11:55 am UTC

there is the state of being that is "i have some locks". In that state of being you may be

a) active
b) not active


there is that state of being that is "i want that lock and I'm waiting for it". In that state of being you are ACTIVE.

How does the profile parameter 'idle_time' work?

May 13, 2005 - 11:58 am UTC

Reviewer: Jairo Ojeda from Costa Rica

These page get my attention, six month before I had an Oracle Support for a Data Guard issue, so one of the Oracle guys, notice that I use the Idle_Time and he told me that this parameter don’t work very well because Oracle don’t release the resource of sessions that were marked as snipped, until the next time the user try to use it (waiting to tell “your session was killed”, to clear the session resources)

Tom Kyte

Followup  

May 13, 2005 - 12:47 pm UTC

it does not release the session, but the resources held should be.


I created a resource profile with LIMIT idle_time 1 (one minute)

gave it to scott.

as scott:

scott@ORA9IR2> select * from emp for update;


Now, in another session 

ops$tkyte@ORA9IR2> exec dbms_lock.sleep( 60 );                                                                                 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> select username, status from v$session where username is not null;
 
USERNAME                       STATUS
------------------------------ --------
SCOTT                          SNIPED
OPS$TKYTE                      ACTIVE


ops$tkyte@ORA9IR2> select * from scott.emp for update;
 
     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM
---------- ---------- --------- ---------- --------- ---------- ----------
    DEPTNO
----------
      7369 SMITH      CLERK           7902 17-DEC-80        800
        20
...
      7934 MILLER     CLERK           7782 23-JAN-82       1300
        10
 
 
14 rows selected.
 


the "session" is there, that will not go away until the client acknowledges it, but the "transaction" is gone. 

How does the profile parameter 'idle_time' work?

May 13, 2005 - 1:05 pm UTC

Reviewer: Jairo Ojeda from Costa Rica

Tom, I like the way you demostrait your point (clear, at the point)!

What about OS resources like memory assigned to that snipped session?

Tom Kyte

Followup  

May 13, 2005 - 1:34 pm UTC

well, memory would not really be too much of an issue -- if you are using it (which you aren't, you are idle) it would be physical real ram.

If you are NOT using it, it would be paged, on disk, not real.


That and if you use pga_aggregate_target and automatic workareas, you'll find the memory is allocated and released as needed frequently during your session.

Thanks

May 13, 2005 - 2:11 pm UTC

Reviewer: Jairo Ojeda from Costa Rica

I don't know much about but I thing it can be an issue if oracle process has get 2gb of ram on a WinOS in a rarely day and we need more sessions, but the OS won't give more memory because it is on top (2gb/process) and I know that there are some snipped sessions holding unnecesary ram. --bla, bla, bla

Thanks for clarify me about those guys told me.

Tom Kyte

Followup  

May 13, 2005 - 2:20 pm UTC

if you are using workareas (suggested) the sort/hash/bitmap areas come and go, so they are not a problem.

it would be primarily the remaining UGA memory - and yes, on windows or when using shared server (any platform), that could be an issue.

How does the profile parameter 'idle_time' work?

May 13, 2005 - 4:30 pm UTC

Reviewer: Alex from Chicago, IL

Tom, I've altered a profile to have IDLE_TIME=240(4 hours) and made sure my resource_limit parameter is set to TRUE. When I query v$session I see some "snipped" sessions, but also "inactive" ones that have been idle for more than a day. All those users have this profile assigned to them. If the user session was connected before idle_time was set, would those sessions be affected by this change or not? I've made a change quite some time ago. Is there anything else I should have done?

CAS.MILLIAX> @./scr/parms


Enter parameter name (press ENTER for ALL or use wild card ("%")): %limit%
Enter parameter value (press ENTER for ALL or use wild card ("%")):


NAME VALUE
================================================== ===========================
java_soft_sessionspace_limit 0
resource_limit TRUE
==============================================================================

CAS.MILLIAX> select resource_name,limit from dba_profiles where resource_name='IDLE_TIME'
2 and profile='USER_PROFILE';

RESOURCE_NAME LIMIT
================================ ========================================
IDLE_TIME 240

1 row selected.

CAS.MILLIAX> select username,profile from dba_users where username in ('DELOOCG','ALFINMJ');

USERNAME PROFILE
============================== ==============================
ALFINMJ USER_PROFILE
DELOOCG USER_PROFILE

2 rows selected.

CAS.MILLIAX> @./scr/sessions
ELAPSED_TIME IDLE_TIME
SCHEMANAME STATUS LOGON_TIME DD:HH:MI:SS DD:HH:MI:SS
=========== ======== ================== ============ ============
ALFINMJ INACTIVE 10-MAY-05 07:33:43 03:07:36:41 02:06:49:58
DELOOCG SNIPED 12-MAY-05 08:29:01 01:06:40:23 00:18:59:24

Thanks.

Tom Kyte

Followup  

May 13, 2005 - 5:03 pm UTC

If the user session was connected before idle_time was set, they are "grandfathered" in -- they will not be sniped. it only affects new sessions.



Sniped connections

December 05, 2005 - 4:38 pm UTC

Reviewer: Suren Dharanikota from Milwaukee, WI

Hi Tom,
I have understood, that by enforcing the "Idle_time" parameter the session will be marked as Sniped by pmon and will remain so untill the client acknowledges it.

What about the parameter "Connect_Time" ? Would this parameter do exactly the same ( I mean mark it as "sniped") OR will it clean up the session after the connect_time period ?

Thanks,
Suren


Tom Kyte

Followup  

December 06, 2005 - 5:27 am UTC

we do not remove the session object until the client can be told about it, else the client gets ora-3113 end of file on communication channel which in turn leads to "call support, oracle is broke".

June 02, 2006 - 2:50 pm UTC

Reviewer: A reader


August 30, 2006 - 4:12 pm UTC

Reviewer: A reader

Tom,

Can i use profiles to lock user account which have been inactive(did not log on) for x number of days?

Thanks.

September 07, 2006 - 2:41 am UTC

Reviewer: Jack from Vancouver, BC Canada

>> Tom,
>> Is there is a way to clean up the sniped sessions so >> that new users can be
allowed to login without hitting the max. number of sessions problem?


>> Followup:
>> you can "kill" the dedicated server at the os level

>> there is an enhancement request in to have this behaviour changed

Tom, do you know the status of this enhancement request?



Tom Kyte

Followup  

September 07, 2006 - 7:09 am UTC

no, you would utilize support/metalink for that.

Idle time for os users

September 08, 2006 - 8:57 am UTC

Reviewer: Sebastian

Tom,
I wonder if it's possible to create a profile and set it to the db user, but only if certain session conditions are met, eg. os user connected, ip address etc...
I was thinking about a logon trigger that executes the alter user statement, but that would be effective for all users, regardless the os user session right ?
For example, I want no more than 5 connections from jack and
set a profile only for Pete:

create or replace trigger logon_trigger
after logon on database
declare
pragma autonomous_transaction;
v_count number(2);
v_user varchar2(100);
v_profile varchar2(1000);
begin
v_user := sys_context('userenv', 'os_user');
v_profile := 'alter user scott profile
limited_idle_time';
select count(*)
into v_count
from v$session v
where v.osuser = v_user;

if (v_user = 'jack') and (v_count > 5) then
raise_application_error(-20001, 'Jack sessions
exceeded');
elsif v_user = 'pete' then
execute immediate v_profile;
end if;
end;

Tom Kyte

Followup  

September 09, 2006 - 11:48 am UTC

if you want jack to have no more than 5 sessions - you would create a profile "five_sessions" and assign that to jack.

but profiles are for USERS, they are not ip_address/etc dependent. no.

Would DCD clean up sniped sessions?

July 12, 2007 - 4:37 pm UTC

Reviewer: A reader

Tom,
Is it possible to use the Dead Connection Detection feature (sqlnet.expire_time) to implement cleanup of sniped sessions?

Thanks

Tom Kyte

Followup  

July 12, 2007 - 5:10 pm UTC

if the client is dead, yes.

connection

November 08, 2009 - 9:03 pm UTC

Reviewer: A reader


IDLE_TIME is not effective

May 27, 2011 - 5:00 am UTC

Reviewer: a reader from INDIA

I want to set IDLE_TIME =30 minutes in my production db, so i have created a test environment on my system with Oracle db version 11.1.0.6.0 on windows,and I have set RESOURCE_LIMIT=TRUE in init file and added IDLE_TIME for 1min in user's profile now the concern is

1> Why my connection still INACTIVE after 60 sec,it sniped out after 120 sec
2> Can I use CONNECT_TIME=1 and IDLE_TIME=1 to attain the outcome
3> Is there any way for not have sessions lying INACTIVE in database for more than 30 minutes as we face many sessions lying inactive for more than a day
Tom Kyte

Followup  

May 27, 2011 - 10:42 am UTC

why do you say it doesn't work? You just said it worked - it won't be exactly, precisely, on the nose 30 minutes, but it'll be darn close. The resource manager isn't polling constantly.


And really - you need to ask yourself "why do I have tons of idle sessions - are my programmers grabbing connections from a pool and not returning them? That is, is there a seriously bad bug in the developed applications???" (rhetorical question, I already know what the answer is and it is "yes")

SNIPED status change

November 28, 2011 - 10:55 am UTC

Reviewer: Abhishek Kumar from India

Hi Tom,

The status of a session get changed from INACTIVE to SNIPED based on either CONNECT_TIME or IDLE_TIME.
I am curious to know that the above activity is done by PMON or resource manager?

Thanks,
Abhishek
Tom Kyte

Followup  

November 29, 2011 - 7:48 am UTC

it would be set up either declaritively (via a PROFILE) or the resource manager. The actual killing is likely an SMON activity in most cases.

how to kill a sniped session manually

November 29, 2011 - 4:53 am UTC

Reviewer: Abhishek Kumar from India

Hi Tom,
One more question I would like to ask that how can we kill a SNIPED session? I tried below but got error out:

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod
PL/SQL Release 10.2.0.3.0 - Production
CORE    10.2.0.3.0      Production
TNS for 32-bit Windows: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production

SQL> select username, sid, serial#, status from v$session where program = 'sqlplus.exe';

USERNAME                              SID    SERIAL# STATUS
------------------------------ ---------- ---------- --------
SCOTT                                 139        103 SNIPED
SYS                                   158         66 ACTIVE

SQL> alter system kill session "139,103" immediate;
alter system kill session "139,103" immediate
                          *
ERROR at line 1:
ORA-00026: missing or invalid session ID

Thanks,
Abhishek

Tom Kyte

Followup  

November 29, 2011 - 11:41 am UTC

can you use disconnect instead of kill

the session is already killed.

kill sniped session

November 30, 2011 - 9:12 am UTC

Reviewer: A reader

Hi Tom,
For disconnect also I am getting the error. Please suggest.

===============================================================
sys@ORCL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

sys@ORCL> select username,sid,serial#,status,seconds_in_wait from v$session where program = 'sqlplus.exe';

USERNAME                              SID    SERIAL# STATUS   SECONDS_IN_WAIT
------------------------------ ---------- ---------- -------- ---------------
SYS                                   139       1942 ACTIVE                 0
SCOTT                                 151       3024 SNIPED               828

sys@ORCL> alter system disconnect session "151 3024" immediate;
alter system disconnect session "151 3024" immediate
                                *
ERROR at line 1:
ORA-00026: missing or invalid session ID
===============================================================

What is the difference between kill and disconnect?

I am also curious to know that as per the error it is saying that the SID is invalid, where as the session is still in v$session. So how does oracle validates a session?

Thanks,
Abhishek

Tom Kyte

Followup  

November 30, 2011 - 12:24 pm UTC

'151,3024'

not

"151 3024"

disconnect gets rid of the session, kill snipes it and waits for the client to try to access the database to return the ora-28. a disconnected session would get something like an ora-3113 instead.

ORA error while issuing alter system kill

November 30, 2011 - 2:49 pm UTC

Reviewer: Abhishek Kumar from India

Hi Tom,

thanks.. I was doing the mistake which was not supposed to be done.. :(

I have one more question for you if you would like to advice:

When I execute "alter system kill session '139,258' immediate;" I am getting below error at client:

==============================================================================================
scott@ORCL> desc emp
ERROR:
ORA-03113: end-of-file on communication channel

scott@ORCL> desc emp
ERROR:
ORA-03114: not connected to ORACLE
==============================================================================================

but when I execute "alter system kill session '139,266';" I am getting below error at client:
==============================================================================================
scott@ORCL> desc emp
ERROR:
ORA-00028: your session has been killed

scott@ORCL> desc emp
ERROR:
ORA-01012: not logged on
==============================================================================================

Can you please explain why Oracle is throwing diffrent errors when I am using immediate clause?

With Immediate Clause Without Immdeiate Clause
===================== ========================

ORA-03113 ORA-00028
+ +
ORA-03114 ORA-00028


I am also not able to understand about "ORA-03114" and "ORA-01012".

Does ORA-03114 means physical circuit is closed and ORA-01012 means that session is closed but connection is not?

Thanks,
Abhishek

Tom Kyte

Followup  

December 01, 2011 - 7:35 am UTC

for the same reason I said disconnect would give you a 3113, immediate doesn't snipe the session (sniping involves rolling back, releasing locks - but letting the session live until the client tries something)

diffrence between ORA-03114 and ORA-01012

December 03, 2011 - 12:51 am UTC

Reviewer: abhishek kumar from India

And what is the difference between "ORA-03114: not connected to ORACLE" and "ORA-01012: not logged on"?

Thanks,
Abhishek
Tom Kyte

Followup  

December 06, 2011 - 11:09 am UTC

in order to get a 3114, you typically need to have been connected and logged on and have somehow gotten kicked out.

1012 means you currently are not logged in. You might have been before, but you've logged out.


Getting ora-3113 followed by ora-1012

December 28, 2011 - 3:14 am UTC

Reviewer: Ibrahim from Saudi

Hi Tom,,
In our test environment we are not using any profile on any users, all users are using default profile, also the parameter resource_limit is set to false. Whenever the session is idle with varaition in the time interval(say 10 mins,or 20 mins or sometimes 30 mins etc) and also if a user is working with any of the tools(like sqlplus,toad or connected to application or from developer suite) meanwhile they are connected and active sometimes those sessions also get terminated with the following errors, ora-3113 end-of-communication channel followed bu ora-1012 not logged on. Could you please help me to wherein i have to check to get rid off these errors... and to avoid connection losts to oracle database
Tom Kyte

Followup  

December 29, 2011 - 10:49 am UTC

You have a firewall.

Your network administrators have set up a rule.

that rule is "disconnect any connection that is idle for 20-30 minutes"

Check with your network guys, they are doing it.

If we were doing it - you would get an reasonable error message saying "resource exceeded" ora-28 your session has been killed. The fact you are getting ora-3113 means someone else outside of Oracle has killed your session.

Ora-3113

January 02, 2012 - 3:31 am UTC

Reviewer: A reader

Hi Tom,
When the session get killed got the following error in trace file at client side, getting some nt[0] error what does this means to


[000001 04-NOV-2011 15:16:37:945] ntt2err: entry
[000001 04-NOV-2011 15:16:37:945] ntt2err: Read unexpected EOF ERROR on 272
[000001 04-NOV-2011 15:16:37:945] ntt2err: exit
[000001 04-NOV-2011 15:16:37:945] nsprecv: error exit
[000001 04-NOV-2011 15:16:37:945] nserror: entry
[000001 04-NOV-2011 15:16:37:945] nserror: nsres: id=0, op=68, ns=12537, ns2=12560; nt[0]=507, nt[1]=0, nt[2]=0; ora[0]=0, ora[1]=0, ora[2]=0
[000001 04-NOV-2011 15:16:37:945] nsrdr: error exit
[000001 04-NOV-2011 15:16:37:945] nsdo: nsctxrnk=0
[000001 04-NOV-2011 15:16:37:945] nsdo: error exit
[000001 04-NOV-2011 15:16:37:945] nioqer: entry
[000001 04-NOV-2011 15:16:37:945] nioqer: incoming err = 12151
[000001 04-NOV-2011 15:16:37:945] nioqce: entry
[000001 04-NOV-2011 15:16:37:945] nioqce: exit
[000001 04-NOV-2011 15:16:37:945] nioqer: returning err = 3113
[000001 04-NOV-2011 15:16:37:945] nioqer: exit
[000001 04-NOV-2011 15:16:37:945] nioqrc: exit
[000001 04-NOV-2011 15:16:37:945] nioqrs: entry
[000001 04-NOV-2011 15:16:37:945] nioqrs: state = normal (0)
[000001 04-NOV-2011 15:16:37:945] nioqrs: reset called, but connection in EOF state.
Tom Kyte

Followup  

January 02, 2012 - 8:37 am UTC

it means you got an ora-3113 - eof on communication channel, someone or something closed the network connection.

read the comment directly above.

ora-3113

January 02, 2012 - 10:43 pm UTC

Reviewer: A reader

I mean to ask whether that error point out any hint from where does the problem is(where the actual bottleneck lies), either the network side or oracle bug,issue or its related to operating system(Solaris 10/09 s10x_u8wos_08a x86). Do I need to apply any fixes on oracle binaries to get rid off these error, or to apply any OS patch or this error is genuinely because of network setup. Sometimes getting ora-3113 followed by ora-3114 at only one particular time that is 12:03 pm.
Tom Kyte

Followup  

January 03, 2012 - 11:26 am UTC

If you are getting it at a specific time every day, it is not an Oracle bug - ask your network guys what happens at 12:03pm. They are doing something - someone is doing something.

ora-3113

January 04, 2012 - 1:05 am UTC

Reviewer: A reader

Hi Tom,
Its getting disconnected at around 12:03 pm, as well its getting disconnected when found idle for some time, how could i ensure that this is not an issue of oracle, is there any way to check if its an oracle bug
Tom Kyte

Followup  

January 04, 2012 - 9:26 am UTC

if it happens at 12:03 pm like clockwork, it is not an Oracle bug. Trust me on that one.

This is a network policy - it won't be hard to verify that. Ask your network folks.

Or, just open a telnet/ssh window to the server - let it sit idle. Watch it get timed out.


this is not an oracle bug, this is a firewall/network policy.

Ora-3113

January 08, 2012 - 6:38 am UTC

Reviewer: A reader

Hi Tom,
I have tested both via telnet and via sqlplus from remote server to PC, telnet suppose to be work fine with no connection failure eventhought if its idle for long time, and when i connect throught sqlplus after not a particular time but may be if its idle then it is hitting the following error there by killing the ongoing session, may be around same as 12:03 or something, if itz the network problem then bot the sessions must get killed telnet and sqlplus connection.  
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel


SQL> /
ERROR:
ORA-03114: not connected to ORACLE


After this time if i leave the connnection open idle and i came and check next day but the session with sqlplus too is working fine, say at 3:37 pm i started a session using sqlplus and when i came back next day 7:30 ami checked its working fine could you please help me out to kill this issue its killing thanks....

Tom Kyte

Followup  

January 10, 2012 - 10:14 pm UTC

it is not oracle, that is about all I can say, we don't have any time bombs like that.

have you even talked to your network works? to your DBA's?


Advice for RAC

March 11, 2012 - 6:27 am UTC

Reviewer: A reader

Could you please give some suggestions when to implement Oracle RAC, i need your guidance...

Please tell me how to design my datacenter

April 26, 2012 - 1:52 pm UTC

Reviewer: clueless from Lost in space

I need this information by 5pm. Thanks

idle time does not work on some machines

April 08, 2014 - 12:31 pm UTC

Reviewer: Baniol from Bydgoszcz, Poland

Hi Tom,
I set the parameter idle time in some profile. Then i set this profile to user. Idon't know why in some computers in network this is working (connection is breaking) and why in some others idle time not causes any disconnection. Application is still running... Is it possible that it depends on system version? Is some difference between XP and 7?
Tom Kyte

Followup  

April 16, 2014 - 5:28 pm UTC

if you really have tested this and 100% verified that what you say is correct (that the last_call_et in v$session shows a session that has the profile assigned to it exceeding the idle timeout) please utilize support. that is not the expected behavior.


see this:
https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:914029001168#8160650200346053254


I firmly believe that if you are killing inactive sessions, you have a bug in your developed code. you are leaking connections...

Firewall kills my session

March 15, 2020 - 6:23 am UTC

Reviewer: Nadia from Oman

Dear Tom,
This article was the most useful on the internet to our scenario.

For us, yes there's a firewall between app server and db server. The firewall had a policy of killing the session every 5 mins, then extended to 1 hour, 24 hours and finally 10+ days.


Unfortunately due to this , our server is crashing by the limit, basically now every 10+ days for the idle item out, it seems even if the session is killed it is occupying some space or utilizing the memory and CPU even after being idle for 10+ days (which shouldnt be right!), and CPU reaches over 1000% before the system crashes.

Isnt there any risks associated with keeping the session idle/inactive for 10+ days ? shouldnt we apply a mechanism to clear session and its data from memory, seems like our garbage collector unable to clear it, the java process hits high in CPU at this time.

Not sure which way to fix this, since this policy is just a temporary solution. please advise
Connor McDonald

Followup  

March 16, 2020 - 10:23 am UTC

An idle session counts toward your total session count, so if you have *leaking* sessions, ie, the session count just grows and grows...then this is obviously an issue.

Similarly, if those idle sessions are holding locks etc that may block others then that can be a problem.

Otherwise, idle sessions are not particularly dangerous within sensible limits. (ie, if you have 100,000 idle sessions then you're doing something very very wrong).

Re: Firewall kills my session

March 17, 2020 - 6:07 am UTC

Reviewer: A reader


the sessions are being killed in the front end but are there in the back end (read DB).

You have 2 choices where either identify and kill orphan sessions
- OR - (since these are jave sessions) check if connection pooling can be better utilized.

Since, after being killed Java is creating new connection pool which is in turn impacting your DB.

Cheers!

How do i know my connection pool setup

March 18, 2020 - 6:00 am UTC

Reviewer: Nadia from Oman

Dear Reader,
thanks for your response, truly helpful.

You're asking about connection pool which make more sense in this case, but how do i check if my connection pool is tuned ? is there specific standard values for it in JVM ? is there a specific way to check if sessions are holding connection pool.

Note that we have clustered environment , multiple nodes but same database. please advise.

Connor McDonald

Followup  

March 20, 2020 - 6:05 am UTC

Toon Koppelaars did an entire session on this.

Check out the video here



Cant thank you enough

March 25, 2020 - 7:01 am UTC

Reviewer: reader from Oman

I cant thank you enough for the video shared, which helped in understanding the lifecycle of querying and connection pool concepts. Thanks a ton. Will continue our investigation based on this.


Thanks,
Connor McDonald

Followup  

March 26, 2020 - 2:51 am UTC

glad we could help