INACTIVE SESSION & ORA-00020

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?
amajiAsked:
Who is Participating?
 
schwertnerConnect With a Mentor Commented:
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
 
baonguyen1Commented:
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
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.

All Courses

From novice to tech pro — start learning today.