Solved

INACTIVE Oracle Sessions

Posted on 2003-12-02
11
4,539 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Technology Partners: 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!

 
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
 

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

Technology Partners: 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!

Question has a verified solution.

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

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows how to recover a database from a user managed backup

729 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