Solved

INACTIVE SESSION & ORA-00020

Posted on 2004-03-25
4
1,277 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

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to recover a database from a user managed backup

867 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now