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?

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 

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

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

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

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.

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,

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

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
 

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?

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

answers are direct on target.
i like it

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

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?
"

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)

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. 

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

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

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

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,

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

Thanks.

>> 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,
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,
Is it possible to use the Dead Connection Detection feature (sqlnet.expire_time) to implement cleanup of sniped sessions?

Thanks

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

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

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

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

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

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

Thanks,
Abhishek

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

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.

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.

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

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

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

I need this information by 5pm. Thanks

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?

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

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!

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.

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,