oracle nclob占用空间,Oracle clob占用临时表空间不释放的解决方案

如果自定义函数返回clob，在使用tomcat,weblogic这样中间件，都有连接池，使用的都是长连接，非常有可能导致Clob字段占用临时表空间不放，要加一句clob.free();

import java.sql.Clob; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; public class TestReadBlob{ Connection con = null; PreparedStatement pre = null; ResultSet result = null; public void runTest() { try{ Class.forName("oracle.jdbc.driver.OracleDriver"); String url = "jdbc:oracle:thin:@11.11.5.54:1521:lcdb"; String user = "test"; String password = "test"; con = DriverManager.getConnection(url, user, password); String sql = "select funcId,en_clob_concat(distinct role_id) otherPostName "+ " from ROLE_RESOURCE where rownum <39490 group by resource_id"; pre = con.prepareStatement(sql); result = pre.executeQuery(); Clob clob ; while (result.next()){ System.out.println( result.getString("funcId")); clob =result.getClob("otherPostName"); clob.free(); } System.out.println("程序暂停开始"); Thread.sleep(20000); } catch (Exception e) { e.printStackTrace(); }//这里没有写finally就是模拟weblogic长连接不关闭连接的情况 } public static void main(String[] args) { TestReadBlob testReadBlob = new TestReadBlob(); testReadBlob.runTest(); } }

select s.BLOCKS * 8 / 1024 / 1024, s.* from v$tempseg_usage s where username = 'LCAM_SYS' --and s.SESSION_ADDR='0000001381EA0408' order by s.BLOCKS desc;