• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1300
  • Last Modified:

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?
0
amaji
Asked:
amaji
1 Solution
 
schwertnerCommented:
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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now