可以设置这个来让数据库清理lob占用的临时表空间，否则只能等着JDBC关闭连接来释放。
alter session set events '60025 trace name context forever'
logon触发器来实现该功能。
create or replace trigger sys.login_db after logon on database
begin
execute immediate 'alter session set events ''60025 trace name context forever''';
end;
/
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 freedStatement 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 releasedSystem.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;}}
代码上有三种方法释放lob temp
1.java.sql.NClob.free()
2.oracle.sql.NCLOB.freeTemporary()
3.(OracleCallableStatement) conn.prepareCall("begin DBMS_LOB.FREETEMPORARY ( ? ); end;");
