Our db has been having issues with inactive sessions and upto 100 sessions from a single web server which is using taking up all the sessions/processes that are allocated.

The sqlnet parameter sqlnet.expire_time is set to 3min which is supposed to clean out dead connections.

How can do this for inactive sessions? In essence, I am looking to automate the killing of all dead/inactive sessions from web servers that are in that state for more than 30minutes.
Who is Participating?
dbalaskiConnect With a Mentor Commented:

the parameter  sqlnet.expire_time  does not do exactly what you think it does.

It only kills connections that do not respond via a probe packet sent out at regular intervals.  
Dead Connection Detection is intended primarily for environments in which clients power down their systems without disconnecting from their Oracle sessions, a problem characteristic of networks with PC clients.

One noted bug in "some" NT versions:
- Orphaned resources are not released if only the client application is terminated. Only after the client PC has been rebooted does DCD release these resources. For example, if a Windows application is killed yet Windows remains running, the probe packet may be received and discarded as if the connection is still active.  As it currently stands, it appears that DCD detects dead client machines, but not dead client processes.
This is logged as generic Bug#280848.

To quote oracle:
"Under no circumstances should you rely 100% on Dead Connection Detection. It was developed to handle clients that have abnormally exited.  Clients should always exit their applications gracefully."


What it sounds like is the application itself is not completely closing off the connection.  So as far as DCD is concerned,  it is not "Dead"

This really would require a re-examination the code.

All is not lost!

Let me Present Some Options:

Use a user Profile to limit Idle time.

Use the CREATE PROFILE statement to create a profile, which is a set of limits on database resources, specifically in your case:  IDLE_TIME  
which specifies the permitted periods of continuous inactive time during a session, expressed in minutes. Long-running queries and other operations are not subject to this limit.  
For this to work,  you should set the INIT.ORA parameter

create profile SHORT_LIMIT
    IDLE_TIME   5;
(5 minute idle time)
Now, make this the user's default profile.
Example:  ALTER USER scott PROFILE short_limit;

This way -- if the session is idle for more than 5 minutes,  the connection is terminated.

This is still a band-aid to the real problem.  

Why launch new server process every time there is a connection?   Launching a new server process is pretty resource intensive on the machine..  

You might be better off looking into using Connection Pooling  (which utilizes Oracle MTS)...
There are some advantages to this,  especially when running OLTP system, such as with a website

To quote Oracle
Connection pooling is a resource utilization feature that enables you to maximize the number of physical network connections to a multi-threaded server. This is achieved by sharing or pooling a dispatcher's set of connections among multiple client processes.
By using a time-out mechanism to temporarily release transport connections that have been idle for a specified period of time, connection pooling makes these physical connections available for incoming clients, while still maintaining a logical session with the previous idle connection. When the idle client has more work to do, the physical connection is reestablished with the dispatcher.

This feature only works when multi-threaded server is configured.

Connection Pooling Advantages:
 (This feature works when MTS is configured.)

 1.  Limits the number of network resources used per process.
 2.  Maximizes the number of client/server sessions over a limited number of physical connections.
 3.  Optimizes resource utilization.

 Connection Pooling Recommended for:

 Networks where many clients run interactive "high think/search time"  applications such as messaging and OLAP.  

Sounds like this option is right up your alley (so to speak)

Hope this helps,

Here is the procedure which you can execute to kill a session.
Your session must have alter system privilege.

The input paramter is idleTime which is the time when session is inactive and doing nothing.

Create or replace procedure KillSession(idleTime in Number)
as cursor c1 is
select sid,serial#,trunc((last_call_et - trunc(last_call_et/3600,0)*3600) / 60,0) TMIN from

  where status='INACTIVE' and username  not in ('SYS');
VString varchar2(50);
for c1rec in c1 loop
 if c1rec.TMIN > idleTime then
     Vstring := 'alter system kill session

    execute immediate(Vstring);
 end if;
end loop;

so if you want to delete a session who's status is inactive and doing nothing with 30 min, you can execute procedure as
executue killsession(30);

Hope this helps
tomreddyAuthor Commented:
I have poured over some documentation before posting this question. This seems like something that Oracle needs to address.
I think another way might be to run dbms_jobs to check which sessions are inactive for more than 30 min and then to kill them but I am sure this will lead to more headaches.
Dbalaski, thanks for your proposed solution. I will leave this here for a day or two to see if anyone else can come up with a better way to do this.
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

Hi tomreddy,

Dbalaski suggestion is good to set the profile with the idle time .
However, idle_time only begins to count after the user does something. For example, assume a user logs into sql*plus and issues no command. The clock for this user's idle time doesn't start. He can stay that way forever. Once is executes anything, even a 'select * from dual', the clock starts ticking.

After reaching the idle time ,if connected user will try to execute the sql statement he will get a
ORA-02396: exceeded maximum idle time, please connect again

Also when user has exceeded idle_time, their process will not be killed but it's status will be changed in V$session to 'SNIPED'. Only after the user tries something and gets the message 'you have exceeded idle_time...' (not exact error)  their session disappear from V$session

You can execute above procedure which i mentioned before with dbms_job or cron and set the idle time dynamically.

Hope this helps,
Hi You can Invokde PMON after say like 30 mins to clean up the dead connections;

-- Script:     post_smon.sql
-- Purpose:     to post PMON to cleanup Dead connections
-- For:          8.1

column pid new_value Smon

set termout off
  sys.v_$bgprocess b,
  sys.v_$process p
  b.name = 'PMON' and
  p.addr = b.paddr
set termout on

oradebug wakeup &Pmon

undefine Smon

Sorry Some Typos , it's
undefine Pmon
From my experience, MTS is somehow broken in NT versions, at least in 8.0.x. It seems to work but sometimes eats network resources (sockets) and the only way to free them is to restart the whole server.

tomreddyAuthor Commented:
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.