INACTIVE Oracle Sessions

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.
Cavada12Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

DrJekyllCommented:
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
DrJekyllCommented:
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
Cavada12Author Commented:
Do you know of any way to run OraKill from within a PL\SQL procedure?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

seazodiacCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
anand_2000vCommented:
Is the DB on a UNIX machine?
0
Cavada12Author Commented:
No, NT.
0
DrJekyllCommented:
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
Cavada12Author Commented:
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
seazodiacCommented:
have you tried my solution?
0
Cavada12Author Commented:
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
seazodiacCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

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.