Posted on 2001-08-07
Last Modified: 2010-05-18
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.
Question by:tomreddy
  • 2
  • 2
  • 2
  • +2

Accepted Solution

dbalaski earned 50 total points
ID: 6361795

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,


Expert Comment

ID: 6361812
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

Author Comment

ID: 6361827
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.
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Expert Comment

ID: 6362172
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,

Expert Comment

ID: 6362694
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
where = 'PMON' and
  p.addr = b.paddr
set termout on

oradebug wakeup &Pmon

undefine Smon


Expert Comment

ID: 6362699
Sorry Some Typos , it's
undefine Pmon

Expert Comment

ID: 6363299
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.


Author Comment

ID: 6382305

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Can i Import Access Table Into Oracle Using Toad 36 275
Create table from select - oracle 6 67
Row_number in SQL 6 45
join actual table rows based on the column 25 31
This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

756 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question