Solved

INACTIVE SESSION & ORA-00020

Posted on 2004-03-25
4
1,280 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
4 Comments
 
LVL 47

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Via a live example, show how to take different types of Oracle backups using RMAN.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

810 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