Oracle JDBC使用lob不释放临时表空间的解决方案

可以设置这个来让数据库清理lob占用的临时表空间，否则只能等着JDBC关闭连接来释放。
alter session set events '60025 trace name context forever';
logon触发器来实现该功能。
create or replace trigger sys.login_events_60025 after logon on database
 begin
 execute immediate 'alter session set events ''60025 trace name context forever''';
end;
/

代码上有三种方法释放lob temp
1.java.sql.NClob.free()  
2.oracle.sql.NCLOB.freeTemporary()
3.(OracleCallableStatement) conn.prepareCall("begin DBMS_LOB.FREETEMPORARY ( ? ); end;");

"***************************************************************************"
"session - Dump session group's event settings                              "
"process - Dump process group's event settings                              "
"system  - Dump system group's event settings(Ie the instance wide events)  "
"***************************************************************************"

在实例级别设置：
###alter system set events '60025 trace name context forever';

设置到SPFILE中：
###alter system set event='60025 trace name context forever' scope=spfile sid='*';

在session级别设置：
alter session set events '60025 trace name context forever';

import java.sql.*;
import oracle.jdbc.OracleCallableStatement;
import oracle.jdbc.OracleConnection;
import oracle.jdbc.OracleResultSet;
import oracle.jdbc.OracleTypes;
import oracle.jdbc.pool.OracleDataSource;
 
import oracle.sql.NCLOB;
 
public class Class2 {
public static void main(String[] args) throws Exception {
String SQL1_syntax = "select to_nclob('a') from dual";
String SQL2_syntax = "select * from v$tempseg_usage";
String SQL3_syntax = "select count(*) from v$tempseg_usage";
String SQL4_syntax = "alter session set events '60025 trace name context forever'";
 
Connection conn = getConnection();
 
// event 60025 - if there are no active temp lobs in the session (ie: both cache temp lob and
// no-cache temp lobs used are zero) then the temp segment itself will also be freed
Statement stmt = conn.createStatement();
System.out.println(SQL4_syntax);
stmt.executeUpdate(SQL4_syntax);
 
System.out.println(SQL1_syntax);
ResultSet rs = stmt.executeQuery(SQL1_syntax);
NCLOB nclob;
rs.next();
System.out.println(rs.getString(1));
nclob = (NCLOB) ((OracleResultSet) rs).getNClob(1); /* we now have handle to the LOB memory */
 
System.out.println(SQL2_syntax);
rs = stmt.executeQuery(SQL2_syntax);
while(rs.next()) {
System.out.println(rs.getString(1) + " " + rs.getString(2) + " " + rs.getString(3) + " " + rs.getString(4) + " " + rs.getString(5) + " " + rs.getString(6) + " " + rs.getString(7) + " " + rs.getString(8));
}
 
 
// System.out.println("disconnect"); // We are NOT disconnecting
// conn.close();
// conn = getConnection();
 
 nclob.free();
 
//nclob.freeTemporary();
 
// this block does the same as nclob.freeTemporary();  or nclob.free()
//OracleCallableStatement cs = (OracleCallableStatement) conn.prepareCall("begin DBMS_LOB.FREETEMPORARY ( ? ); end;");
//cs.registerOutParameter(1, OracleTypes.NCLOB);
//cs.setNClob(1, nclob);
//cs.execute();
 
 
stmt = conn.createStatement();
System.out.println(SQL2_syntax);
rs = stmt.executeQuery(SQL2_syntax);
while(rs.next()) {
System.out.println(rs.getString(1) + " " + rs.getString(2) + " " + rs.getString(3) + " " + rs.getString(4) + " " + rs.getString(5) + " " + rs.getString(6) + " " + rs.getString(7) + " " + rs.getString(8));
}
ResultSetMetaData rsmd = rs.getMetaData();
rs = stmt.executeQuery(SQL3_syntax);
rs.next();
// if # of rows is zero, this means temp segments have been released
System.out.println("Nb of rows : " + rs.getString(1));
 
}
 
public static Connection getConnection() throws SQLException {
String username = "scott";
String password = "tiger";
String thinConn = "jdbc:oracle:thin:@//host/ORCL";
OracleDataSource ods = new OracleDataSource();
ods.setUser(username);
ods.setPassword(password);
ods.setURL(thinConn);
Connection conn = ods.getConnection();
DatabaseMetaData dbmd = conn.getMetaData();
System.out.println(dbmd.getDatabaseProductVersion());
System.out.println(dbmd.getDriverVersion());
conn.setAutoCommit(false);
return conn;
}
}