Solved

inactive sessions in oracle databae.

Posted on 2009-04-08
10
1,473 Views
Last Modified: 2013-12-18
experts,

i am seeing more number if INACTIVE sessions in oracle. i am not sure why this inactive sessions.
i can see more sessions are belongs to one app server. i could not able to find out root cause of this problem. how do i investigate this problem. can any one help me. please let me know if you need any information/
0
Comment
Question by:pavan27
[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
  • 5
  • 4
10 Comments
 
LVL 35

Expert Comment

by:johnsone
ID: 24097650
This is not a problem.  Sessions with a status of INACTIVE in V$SESSION just means they are not currently running a query.  When the session is running a query it will go to ACTIVE, then back to INACTIVE when the query finishes.
0
 
LVL 8

Expert Comment

by:Haris V
ID: 24097660
0
 
LVL 1

Author Comment

by:pavan27
ID: 24098573
Experts,

Thanks for quick response. my problem is here why the sessions are in inactive even though it is not doing any activity in the DB. why session are not closing automatically after query execution is completed.?

at db side no chance to time out those sessions. we have allocated unlimited resource to that user  account which are making session in the db.

can any one help me to find why the sessions are being inactive and not flushing out from db.

0
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 
LVL 35

Expert Comment

by:johnsone
ID: 24098803
A session wouldn't close automatically when a query completes.  Coding an application to connect, run one query and disconnect is very inefficient.  A lot of time will be spent connecting to the database.
0
 
LVL 1

Author Comment

by:pavan27
ID: 24099068
do you think this these inactive session are inactive due to connection is not made to oracle in proper time?
i did not understand your explanation. could you please clarify me once again.
0
 
LVL 35

Expert Comment

by:johnsone
ID: 24099404
Normally, an application would do this:

- connect
- run a query
- wait for user input  <- Connection would show as INACTIVE here
- run a query
- wait for user input  <- Connection would show as INACTIVE here
- .....
- disconnect

It should not do this:

- connect
- run a query
- disconnect
- connect
- run a query
- disconnect
0
 
LVL 1

Author Comment

by:pavan27
ID: 24198558
John,

thanks for reply. i have one question

how does i know that session is waiting for user input. is there any way to find .
if yes could you please tell me how can i get it.

0
 
LVL 35

Expert Comment

by:johnsone
ID: 24198624
V$SESSION_WAIT will tell you what the session is waiting for.  It will most likely be SQL Net message from client, or something similar.
0
 
LVL 1

Author Comment

by:pavan27
ID: 24558063
Hello Experts,

i have one question.

- connect
- run a query
- wait for user input  <- Connection would show as INACTIVE here

how much time session will wait for user input.
0
 
LVL 35

Accepted Solution

by:
johnsone earned 500 total points
ID: 24559243
Theorectically, it would wait forever.
0

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
PL/SQL More than one element associated with another element 7 57
Oracle dataguard 5 61
oracle differnce between two timestamps 5 40
how to tune the query 17 63
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
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, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

696 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