Solved

INACTIVE Oracle Sessions

Posted on 2003-12-02
11
4,491 Views
Last Modified: 2007-12-19
I'm having a problem with an app written in ASP.NET.  I am explicitly opening and closing all of my connections to an Oracle DB, but when I select from V$Session, the sessions are shown as INACTIVE.  If the app is being used by many users, I get an error stating that the maximum number of sessions has been exceeded (the Oracle Error is ORA-00020).  Even if I kill the sessions using alter system, the killed sessions still count against me.  Is there a way to completely kill the session, so I can keep the number of sessions I have down?  
Thanks.
I appreciate any help.
0
Comment
Question by:Cavada12
  • 4
  • 3
  • 3
  • +1
11 Comments
 
LVL 5

Assisted Solution

by:DrJekyll
DrJekyll earned 125 total points
ID: 9861585
If you are disconnecting then that session will go away. An inactive transaction is nothing more than a session with no active sql.  

Increasing PROCESSES parameter in init.ora should resolve the problem.
This is what the Oracle Administration guide says -

This (PROCESSES) parameter determines the maximum number of operating system processes that can be connected to Oracle concurrently. The value of this parameter must include 5 for the background processes and 1 for each user process. For example, if you plan to have 50 concurrent users, set this parameter to at least 55.


1. Edit the database INITsid.ORA file
2. Locate and increase the processes parameter to a higher value.
3. Save the INITsid.ORA file
4. Stop and restart the database
5. Re-connect to the database instance
0
 
LVL 5

Expert Comment

by:DrJekyll
ID: 9861669
What is your OS?

You can use the OraKill utility that is shipped with Oracle databases on Windows platforms. See the following document on MetaLink for more information: 69882.1 Windows NT Killing an Oracle Thread. You should see why your asp app is not closing sessions but in the interim increase the processes parameter.
0
 

Author Comment

by:Cavada12
ID: 9861716
Do you know of any way to run OraKill from within a PL\SQL procedure?
0
 
LVL 23

Accepted Solution

by:
seazodiac earned 125 total points
ID: 9861958
the neat way to do this is:
Create a profile with session_idle_time (equal to the time period you prefer)
assign this application user to this profile.
then all the sessions opened by this application will be closed when exceeding the idle time.



I think sessions initiatied from applications are , for some reasons, behaving differently.
From my experience, the opening and closing of my sessions even have something to do with the DRIVERs I used.
for example, when i do the java programming using oracle thin jdbc drivers, sessions from applications are immediately closed when my apps exit.
whereas VB apps using ODBC Drivers are not.

I also came across the documents saying that this is normal behavior...


0
 
LVL 13

Expert Comment

by:anand_2000v
ID: 9863587
Is the DB on a UNIX machine?
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:Cavada12
ID: 9866773
No, NT.
0
 
LVL 5

Expert Comment

by:DrJekyll
ID: 9867136
I think you would have to run some sort of external procedure to run OraKill in pl/sql. I would NOT recommend that approach at all nor would I disconnect after some determined idle time, at least not yet.  I think the focus should be on getting the sessions to disconnect gracefully.  I do not know issues with the drivers as suggested.  Perhaps others are experienced in this area. Using orakill and setting the idle should only be considered as a short term bandaid.

DJ
0
 

Author Comment

by:Cavada12
ID: 9867191
I'm using Microsoft's Oracle Managed Provider for .NET.  I knnow Oracle has one (Oracle Data Provider), but I have never used it, nor do I know if it would help in this case.  Has anyone used ODP?
0
 
LVL 23

Expert Comment

by:seazodiac
ID: 9867232
have you tried my solution?
0
 

Author Comment

by:Cavada12
ID: 9867261
Yes, and it seems to be helping, but I just want to know if there is any way to get the sessions to expire immediately.
0
 
LVL 23

Expert Comment

by:seazodiac
ID: 9867326
actually NO.

you can try this command at the sqlplus command:

Alter system kill session ‘sid, serial#‘;

you can get sid, serial# from v$session.

but my experience tells me that that command does not work reliably for sessions initiated from ODBC or JDBC application. it works pretty good for sqlplus sessions.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

760 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now