博客

/

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;”);