Oracle JDBC使用lob不释放临时表空间的解决方案
发布时间:2020-12-12 14:01:48 所属栏目:百科 来源:网络整理
导读:可以设置这个来让数据库清理lob占用的临时表空间,否则只能等着JDBC关闭连接来释放。 alter session set events '60025 trace name context forever' logon触发器来实现该功能。 create or replace trigger sys.login_db after logon on database begin exec
可以设置这个来让数据库清理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;"); (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |
推荐文章
站长推荐
热点阅读