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.getConnectio n(
"jdbc:oracle:thin:@localho st:1521:db 1", "dummy",
"foo");
} catch (SQLException e) {
e.printStackTrace();
}
}
private void reconnect() {
// Assume reconnection always succeeds.
try {
conn = DriverManager.getConnectio n(
"jdbc:oracle:thin:@localho st:1521:db 1", "dummy",
"foo");
} catch (SQLException e) {
e.printStackTrace();
}
}
public App() throws Exception {
DriverManager.registerDriv er(new oracle.jdbc.driver.OracleD river());
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
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.getConnectio
"jdbc:oracle:thin:@localho
"foo");
} catch (SQLException e) {
e.printStackTrace();
}
}
private void reconnect() {
// Assume reconnection always succeeds.
try {
conn = DriverManager.getConnectio
"jdbc:oracle:thin:@localho
"foo");
} catch (SQLException e) {
e.printStackTrace();
}
}
public App() throws Exception {
DriverManager.registerDriv
connect();
}
public void doSomeUpdate() {
try {
conn.setAutoCommit(false);
Statement stmt = conn.createStatement();
stmt.executeUpdate("UPDATE
// 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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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