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

超、凢脫俗 2022-05-29 11:19 603阅读 0赞

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