Link to home
Start Free TrialLog in
Avatar of bessie1215
bessie1215

asked on

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

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
ASKER CERTIFIED SOLUTION
Avatar of schwertner
schwertner
Flag of Antarctica image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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