[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Oracle JDBC -- How to clean up an orphan session after reconnection?

Posted on 2005-04-27
5
Medium Priority
?
2,534 Views
Last Modified: 2008-01-09
I am using Oracle 8.1.7 JDBC thin driver and Java 1.4.2.

My application tries reconnection after a connection is dropped. Once reconnection succeeds, the application repeats an operation.

The scenario is:

App updates table Employee.
The network between App and Oracle is down.
Commit fails.
The network resumes.
Rollback fails (due to socket write error).

App reconnects.
App updates table Employee again.
App waits for a table lock (since the previous session is still alive in Oracle).

The code is:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

public class App {

    private Connection conn;

    private void connect() {
        //  Assume connection always succeeds.
        try {
            conn = DriverManager.getConnection(
                    "jdbc:oracle:thin:@localhost:1521:db1", "dummy",
                    "foo");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    private void reconnect() {
        //  Assume reconnection always succeeds.
        try {
            conn = DriverManager.getConnection(
                    "jdbc:oracle:thin:@localhost:1521:db1", "dummy",
                    "foo");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public App() throws Exception {
        DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
        connect();
    }

    public void doSomeUpdate() {
        try {
            conn.setAutoCommit(false);
            Statement stmt = conn.createStatement();
            stmt.executeUpdate("UPDATE Employee SET salary = 100 WHERE name = 'Mary'");
            // The network between App and Oracle is down at this point of time.
            conn.commit();
        } catch (SQLException e) {
            try {
                // The network between App and Oracle resumes at his point of time.                
                conn.rollback();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
            reconnect();
            doSomeUpdate();
        }
    }

    public static void main(String[] args) {
        try {
            new App().doSomeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

The problem is:

(1) How to kill an orphan session in Java after reconnection?

(2) How to get the SID and serial# of a session associated with a JDBC connection in Java?

(3) Any better way to gracefully do reconnection?

Thanks and Regards,
Matt
0
Comment
Question by:bessie1215
2 Comments
 
LVL 48

Accepted Solution

by:
schwertner earned 2000 total points
ID: 13874937
To clean 'orphan' (dead) session Oracle uses some features of the Listener.
In the file SQLNET.ORA the DBA can enter the line:
SQLNET.EXPIRE_TIME = 20
This will force the listener to clean all dead sessions every 20 minutes.
The listener sends probes to every connection asking confirmation that the
user process is alive. If it doesn't receive confirmation the listener closes the
session.
0
 
LVL 11

Expert Comment

by:sujit_kumar
ID: 13875134
Terminating an Inactive Session
If the session is not making a SQL call to Oracle (is INACTIVE) when it is terminated, the ORA-00028 message is not returned immediately. The message is not returned until the user subsequently attempts to use the terminated session.

When an inactive session has been terminated, STATUS in the V$SESSION view is KILLED. The row for the terminated session is removed from V$SESSION after the user attempts to use the session again and receives the ORA-00028 message.

In the following example, an inactive session is terminated. First, V$SESSION is queried to identify the SID and SERIAL# of the session, then the session is terminated.

SELECT SID,SERIAL#,STATUS,SERVER
   FROM V$SESSION
   WHERE USERNAME = 'JWARD';

SID    SERIAL#   STATUS     SERVER
-----  --------  ---------  ---------
    7        15  INACTIVE   DEDICATED
   12        63  INACTIVE   DEDICATED
2 rows selected.

ALTER SYSTEM KILL SESSION '7,15';
Statement processed.

SELECT SID, SERIAL#, STATUS, SERVER
   FROM V$SESSION
   WHERE USERNAME = 'JWARD';

SID    SERIAL#   STATUS     SERVER
-----  --------  ---------  ---------
    7        15  KILLED     PSEUDO
   12        63  INACTIVE   DEDICATED

0

Featured Post

Independent Software Vendors: 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

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
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 explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
Suggested Courses

830 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