Solved

INACTIVE SESSION & ORA-00020

Posted on 2004-03-25
4
1,285 Views
Last Modified: 2012-06-27
I have developed a web site using JSP and Oracle9i as my backend database. The site contains a few java .class files meant for database transaction. From the .jsp pages appropriate methods of java .class files are invoked - in turn the method establishes a communication with Oracle and does the transaction. While coming out of the pages I explicitly set the instances of the java .class files to null. When I log on to Enterprise Manager console I see a lot of INACTIVE SESSIONS originated from the web site. Sometimes I get the error ORA-00020 : Maximum Processes exceed. As a result of this web site fails to perform. Can anybody help me to get around this problem? In my init.ora file PROCESSES attribute is set to 150. I use jdbc driver in order to connect to ORACLE.
The web server I use is Orion 2.0.2. Does setting the instance of a class to null mean all the contained objects in the class are also deallocated from memory?
0
Comment
Question by:amaji
[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 Comments
 
LVL 48

Accepted Solution

by:
schwertner earned 100 total points
ID: 10675985
After establishing the connection:
Connection con = null
.......
con = ods.getConnection("scott","tiger")

you have to close it:

ods.close();

Example:


import java.io.*;
import java.sql.*;
import javax.servlet.*;
import javax.servlet.http.*;
import javax.sql.*;
import oracle.jdbc.driver.*;
import oracle.jdbc.pool.*;
 
public class Pooljdbc2 extends HttpServlet{
 
   private OracleConnectionPoolDataSource ocpds;
   private OracleConnectionCacheImpl ods;
 
   public void init(ServletConfig config) throws ServletException {
      super.init(config);
      try {
        ocpds =new OracleConnectionPoolDataSource();
        ocpds.setURL("jdbc:oracle:thin:@bldel52:1522:v816");
 
        // ocpds.setUser("scott");
        // ocpds.setPassword("tiger");
 
        // Associate it with the Cache
        ods = new OracleConnectionCacheImpl(ocpds);
 
        // Set the Max Limit
        ods.setMaxLimit (3);
 
        // Set the Scheme
        ods.setCacheScheme (OracleConnectionCacheImpl.FIXED_RETURN_NULL_SCHEME);
      }
      catch (Exception e) {
        throw new UnavailableException(this, "Couldn't create connection pool");
      }    
   }
 
   public void doGet(HttpServletRequest req, HttpServletResponse res)
                                throws ServletException, IOException {
      Connection con = null;
      res.setContentType("text/plain");
      PrintWriter out = res.getWriter();  
      out.println("Updating salary");
 
      try {
         con = ods.getConnection("scott","tiger");
 
         // Turn on transactions
         con.setAutoCommit(false);
 
         Statement stmt = con.createStatement();
         stmt.executeUpdate("UPDATE EMPBIG SET SAL = (SAL - 10) ");
         stmt.executeUpdate("UPDATE EMPBIG SET SAL = (SAL + 10) ");
 
         con.commit();
 
         out.println("Salary updated");
 
         con.close();
      }
      catch (Exception e) {
 
         // Any error is grounds for rollback
         try {  
            con.rollback();
         }
         catch (Exception ignored) { }  
         out.println("No more connections available, try later");
      }    
   }
 
   public void destroy() {
 
      try {  
         ods.close();
      }
      catch (Exception ignored) { }  
   


 
0
 
LVL 8

Expert Comment

by:baonguyen1
ID: 10675997
INACTIVE SESSIONS means there are sessions that remain connected to the database with a   status in v$session of INACTIVE. It may happen because users start a program/session, then leaves it running and idle  for an extended period of time.    

 To automate cleanup of INACTIVE sessions you can create a profile  with an appropriate IDLE_TIME setting and assign that profile to  the users.

To do that:

1.  set resource_limit = true in the init file then restart the db. YOu can also . Enable the feature issue:
SQL>alter system set resource_limit = true;

2. Create a profile for monitoring idle time:
- create profile <profile_name> limit idle_time no_of_minutes;
Example:
- create profile idletime limit idle_time 10;

3. Attach the profile to desired user:
- alter user user_name profile profile_name;
Example:
- alter user scott profile idletime;

This forces any Oracle user sessions, which have been inactive for greater than
no_of_minutes, to be disconnected from the database. Any uncommitted
transaction will be rolled back. When the idle time has passed, the session
will be suspended. Next time the user enters a command he will receive an ORA-
02396: exceeded maximum idle time, please connect again.

Hope this helps
0

Featured Post

Industry Leaders: 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

Suggested Solutions

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
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…
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.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

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