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

可以设置这个来让数据库清理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 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;

}

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