Know more about Temporary Tablespaces ORA-1652 and event 60025 temp space never release
众所周知, oracle临时表空间是用于存储数据库的排序数据和全局临时表数据，tempfile不同于datafile因为不存在控制文件中，在恢复数据库后需要重建tempfile,也可以在standby环境中配置不同的temp表空间。用监控数据表空间间的脚本也许看到临时表空间使用率100%，但并且有时并非真实，DBA_FREE_SPACE 不会记录临时表空间的可用空间，使用v$sort_segments, V$TEMP_SPACE_HEADER 和v$tempseg_usage监控临时表空间的使用，如果临时表空间不足会提示ORA-1652错误。对于临时表空间组特性是没有用的，为不同的用户分配不同的temp tablespace并且以用户分配到不同的实例可以减少temp空间争用。
临时表空间是被分成不同的extent,在11g r2中每个extent为1MB，extent maps是cache在本地的SGA中，并在再重启时不会保存extent map，所以在实例重启后可以完全释放临时表空间的扩展， 临时表空间(cache extent map)是按需扩展的，用完后会标记为free但不会回收(如果不重启)，可以使用v$tempseg_usege查看当前实际使用。本实例cache的extent即使为free,对于其他实例也不可以立即使用，另一个实例需要申请新的uncache的extent, free extent只重用于本实例请求。当一个实例需要更多的extent时可以从另一个实例偷窃，另一个实例会uncache extent map，可以从gv$temp_extent_pool中观察,在11g中为一次请求100 extents,当本实例窃取够了足够的extent就可以继续使用temp extent，临时段的清理或合并是有每5分钟smon进程完成的。
SQL> select * from gv$temp_extent_pool;
INST_ID TABLESPACE_NAME FILE_ID EXTENTS_CACHED EXTENTS_USED BLOCKS_CACHED BLOCKS_USED BYTES_CACHED BYTES_USED RELATIVE_FNO
---------- ----------------- ---------- -------------- ------------ ------------- ----------- ------------ ---------- ------------
1 TEMP 1 11916 8 1525248 1024 1.2495E+10 8388608 1
1 TEMP 3 2884 1 369152 128 3024093184 1048576 3
1 TEMP 4 5990 3 766720 384 6280970240 3145728 4
1 TEMP 5 9422 1 1206016 128 9879683072 1048576 5
1 TEMP 6 10659 3 1364352 384 1.1177E+10 3145728 6
2 TEMP 1 18347 0 2348416 0 1.9238E+10 0 1
2 TEMP 3 12474 2 1596672 256 1.3080E+10 2097152 3
2 TEMP 4 9369 0 1199232 0 9824108544 0 4
2 TEMP 5 5937 3 759936 384 6225395712 3145728 5
近期有一套数据库出现了ORA-1652,extent map都已cache，显示使用率为100%，但当前真实使用的并不多，重启数据库不现实，我想通过drop tempfile再填加的方式释放已扩展的temp空间，结果发现了一个有意思的事情。
1, Drop Tempfile
SQL> @df TABLESPACE_NAME TotalMB UsedMB FreeMB % Used Ext Used ------------------------------ ---------- ---------- ---------- ------ --- ---------------------- ... SYSAUX 24318 17012 7306 70% NO |############## | SYSTEM 20478 2674 17804 14% NO |### | TEMP 107517 107474 43 100% YES |####################| UNDOTBS1 61438 35678 25760 59% NO |############ | UNDOTBS2 61438 13743 47695 23% NO |##### | USERS 330226 283772 46454 86% NO |################## | SQL> @ls temp TABLESPACE_NAME FILE_ID FILE_NAME EXT MB MAXSZ ------------------------------ ---------- ---------------------------------------- --- ---------- ---------- TEMP 3 /dev/yyc_oravg02/ryyc_lv15_128 NO 15359 TEMP 1 /dev/yyc_oravg02/ryyc_temp YES 30719 32767.98 TEMP 2 /dev/yyc_oravg03/ryyc_lv15_240 TEMP 4 /dev/yyc_oravg04/ryyc_lv15_281 NO 15359.99 TEMP 5 /dev/yyc_oravg04/ryyc_lv15_282 NO 15359.99 TEMP 6 /dev/yyc_oravg04/ryyc_lv15_283 NO 15359.99 TEMP 7 /dev/yyc_oravg04/ryyc_lv15_284 NO 15359.99 SQL> alter tablespace temp drop tempfile '/dev/yyc_oravg04/ryyc_lv15_284'; Tablespace altered. SQL> alter tablespace temp drop tempfile '/dev/yyc_oravg04/ryyc_lv15_283'; Tablespace altered. SQL> alter tablespace temp add tempfile '/dev/yyc_oravg04/ryyc_lv15_284'; alter tablespace temp add tempfile '/dev/yyc_oravg04/ryyc_lv15_284' * ERROR at line 1: ORA-01537: cannot add file '/dev/yyc_oravg04/ryyc_lv15_284' - file already part of database SQL> @ls temp TABLESPACE_NAME FILE_ID FILE_NAME EXT MB MAXSZ ------------------------------ ---------- -------------------------------------------------------------------------------- --- ---------- ---------- TEMP 3 /dev/yyc_oravg02/ryyc_lv15_128 NO 15359 TEMP 1 /dev/yyc_oravg02/ryyc_temp YES 30719 32767.98 TEMP 2 /dev/yyc_oravg03/ryyc_lv15_240 TEMP 4 /dev/yyc_oravg04/ryyc_lv15_281 NO 15359.99 TEMP 5 /dev/yyc_oravg04/ryyc_lv15_282 NO 15359.99 TEMP 6 /dev/yyc_oravg04/ryyc_lv15_283 TEMP 7 /dev/yyc_oravg04/ryyc_lv15_284 SQL> @df TABLESPACE_NAME TotalMB UsedMB FreeMB % Used Ext Used ------------------------------ ---------- ---------- ---------- ------ --- ---------------------- ... SYSAUX 24318 17014 7304 70% NO |############## | SYSTEM 20478 2674 17804 14% NO |### | TEMP 76797 76754 43 100% YES |####################| UNDOTBS1 61438 35678 25760 59% NO |############ | UNDOTBS2 61438 13726 47712 23% NO |##### | USERS 330226 283772 46454 86% NO |################## |
NOTE:
使用alter tablespace无法drop tempfile(从数据库释放tempfile),但是可用空间被收回。 正确的方法是使用alter database方式如下：
SQL> alter database tempfile '/dev/yyc_oravg03/ryyc_lv15_240' drop including datafiles;
Database altered.
SQL> @ls temp
TABLESPACE_NAME FILE_ID FILE_NAME EXT MB MAXSZ
------------------------------ ---------- -------------------------------------------------------------------------------- --- ---------- ----------
TEMP 3 /dev/yyc_oravg02/ryyc_lv15_128 NO 15359
TEMP 1 /dev/yyc_oravg02/ryyc_temp YES 30719 32767.98
TEMP 4 /dev/yyc_oravg04/ryyc_lv15_281 NO 15359.99
TEMP 5 /dev/yyc_oravg04/ryyc_lv15_282 NO 15359.99
TEMP 6 /dev/yyc_oravg04/ryyc_lv15_283
TEMP 7 /dev/yyc_oravg04/ryyc_lv15_284
Note:
drop tempfile或drop temporary tablespace前提是当前无会话使用该文件和表空间，如果删除了所有的tempfile,在使用时会提示ora-25153错误，如果有会话使用该tempfile将报ORA-25152错误。
SQL> alter database tempfile '/dev/yyc_oravg04/ryyc_lv15_284' drop including datafiles; alter database tempfile '/dev/yyc_oravg04/ryyc_lv15_284' drop including datafiles * ERROR at line 1: ORA-25152: TEMPFILE cannot be dropped at this time SQL> select u.inst_id,s.sid,s.status,tablespace,u.sql_id,segfile#,segfile#-p.value rfile from gv$tempseg_usage u,v$parameter p,gv$session s where u.session_addr = s.saddr AND u.inst_id = s.inst_id and p.NAME='db_files'; INST_ID SID STATUS TABLESPACE SQL_ID SEGFILE# RFILE ---------- ---------- -------- ------------------------------- --------------- ---------- ---------- 1 17813 INACTIVE TEMP cbpmfx12jzj4r 2025 1 1 17530 INACTIVE TEMP 64tcqftuuwaan 2028 4 1 17285 INACTIVE TEMP cbpmfx12jzj4r 2025 1 ... 2 4814 INACTIVE TEMP cbpmfx12jzj4r 2025 1 2 4806 INACTIVE TEMP 6h214g82rxb7x 2027 3 1 4319 INACTIVE TEMP 64tcqftuuwaan 2030 6 1 1776 INACTIVE TEMP a6kb0pf8x0hqc 2029 5 2 51 INACTIVE TEMP cbpmfx12jzj4r 2025 1 1 285 INACTIVE TEMP 6h214g82rxb7x 2031 7 1 4 INACTIVE TEMP a6kb0pf8x0hqc 2028 4 SQL> select inst_id,tablespace,segtype,sum(blocks),count(*),sum(blocks)/count(*) from gv$tempseg_usage group by inst_id,tablespace,segtype; INST_ID TABLESPACE SEGTYPE SUM(BLOCKS) COUNT(*) SUM(BLOCKS)/COUNT(*) ---------- ------------------------------- --------- ----------- ---------- -------------------- 1 TEMP LOB_DATA 2176 17 128 2 TEMP LOB_DATA 1536 12 128
Note:
上面持用tempfile rfile #7的会话是connect pool的长连接，在当前的数据库版本11.2.0.3(其实在9i-12c的版本都存在)，持有的temp直到该session退出才会释放。如删除本案例中的tempfile rfile 7#,Rfile# 7的tempfile正在被实例1的sid=285的会话使用，kill 掉实例1的285会话即可删除7# tempfile。
并且当前2个实例上一共29个会话，占用的都是temp类型都是temp lob data.每个session占用1M(128*8k). 注意该视图中的SQL_ID并不一定是产生temp seg的SQL，而是该会话最后一次执行的SQL。
如何识别占用TEMP的SQL?
在11g中可以使用 V$ACTIVE_SESSION_HISTORY or DBA_HIST_ACTIVE_SESS_HISTORY的 TEMP_SPACE_ALLOCATED 列识别使用temp较大的查询SQL .在12C版本中v$tempseg_usage新增加列”SQL_ID_TEMPSEG”可以确认产生temp segment的SQLID.如下：
— 12.2
SQL> select username,session_num,sql_id,tablespace,CONTENTS,SEGTYPE,SEGRFNO#,BLOCKS,SQL_ID_TEMPSEG from v$tempseg_usage; USERNAME SESSION_NUM SQL_ID TABLESPACE CONTENTS SEGTYPE SEGRFNO# BLOCKS SQL_ID_TEMPSE -------------------- ----------- ------------- ------------------------------ --------- --------- ---------- --------------------- 18783 g44b5rqw7xwq7 TEMP TEMPORARY LOB_DATA 1 128 0w26sk6t6gq98 SQL> select sql_text from v$sqlarea where sql_id='0w26sk6t6gq98'; SQL_TEXT -------------------------- SELECT XMLTYPE(DBMS_REPORT.GET_REPORT_WITH_SUMMARY(:B1 )) FROM DUAL
Note:
SQL_ID_TEMPSEG 是产生temp的SQL，XML 也是LOB一种形式。
— 11g
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
sys@DB11G 17:26:07> select distinct sid from v$mystat;
SID
----------
785
Elapsed: 00:00:00.01
sys@DB11G 17:26:26> SELECT TO_CLOB(LPAD('X',2000,'X')) FROM DUAL;
TO_CLOB(LPAD('X',2000,'X'))
--------------------------------------------------------------------------------
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
XXXXXXXXXXXXXXXXXXXX
sys@DB11G 2024-09-30 17:30:31> @temp
INST_ID USERNAME SID SERIAL# SQL_ID TABLESPACE CONTENTS SEGTYPE MB EXTENTS BLOCKS
---------- -------------------- ---------- ---------- ------------- -------------------- --------- --------- ---------- ---------- ----------
1 ANBOB 785 2631 9babjv8yq8ru3 TEMP TEMPORARY LOB_DATA 1 1 128
sys@DB11G 2024-09-30 17:30:33> @sqlid 9babjv8yq8ru3
Show SQL text, child cursors and execution stats for SQLID 9babjv8yq8ru3 child %
HASH_VALUE PLAN_HASH_VALUE CH# SQL_TEXT
---------- --------------- ----- ------------------------------------------------------------------------------------------------------------------------------------------------------
1029988163 0 0 BEGIN DBMS_OUTPUT.GET_LINES(:LINES, :NUMLINES); END;
-- 这是一个错误的SQL ID, 可以使用x$ktsso 表
-- monitory temp REAL SQL ID
SELECT k.inst_id "INST_ID",
ktssoses "SADDR",
sid "SID",
ktssosno "SERIAL#",
username "USERNAME",
osuser "OSUSER",
ktssosqlid "SQL_ID",
ktssotsn "TABLESPACE", decode(ktssocnt, 0, 'PERMANENT', 1, 'TEMPORARY') "CONTENTS",
decode(ktssosegt, 1, 'SORT', 2, 'HASH', 3, 'DATA', 4, 'INDEX', 5, 'LOB_DATA', 6, 'LOB_INDEX' , 'UNDEFINED') "SEGTYPE",
ktssofno "SEGFILE#", ktssobno "SEGBLK#", ktssoexts "EXTENTS", ktssoblks "BLOCKS",
round(ktssoblks*p.value/1024/1024, 2) "SIZE_MB",
ktssorfno "SEGRFNO#"
FROM x$ktsso k, v$session s, v$parameter p
WHERE ktssoses = s.saddr
AND ktssosno = s.serial#
AND p.name = 'db_block_size'order by sid;
INST_ID SADDR SID SERIAL# USERNAME OSUSER SQL_ID TABLESPACE CONTENTS SEGTYPE SEGFILE# SEGBLK# EXTENTS BLOCKS SIZE_MB SEGRFNO#
---------- ---------------- ---------- ---------- ------------------------------ ------------------------------ ------------- ------------------------------- --------- --------- ---------- ---------- ---------- ---------- ---------- ----------
1 0000000088D37A80 785 2631 ANBOB oracle 81q0rkt5nud7y TEMP TEMPORARY LOB_DATA 201 512 1 128 1 1
sys@DB11G 2024-09-30 17:27:51> @sqlid 81q0rkt5nud7y
Show SQL text, child cursors and execution stats for SQLID 81q0rkt5nud7y child %
HASH_VALUE PLAN_HASH_VALUE CH# SQL_TEXT
---------- --------------- ----- ------------------------------------------------------------------------------------------------------------------------------------------------------
1263351038 1388734953 0 SELECT TO_CLOB(LPAD('X',2000,'X')) FROM DUAL
Note:
正在使用的temp可以使用x$ktsso 查询真实的SQL ID. 是to_clob产生的临时LOB TEMP SEGMENT.
60025 Event
从10.2.0.4提供了一个event 60025,当系统级或会话级启用了该event, 会在session中的temp lob不活动时(cache temp和nocache temp lobs都为0时)，即使不退出会话也会自己释放空间供其它会话使用。如果启用了60025 event像下面这引起temp lob 会被释放掉:
SQL> select * from gv$temporary_lobs;
INST_ID SID CACHE_LOBS NOCACHE_LOBS ABSTRACT_LOBS
---------- ---------- ---------- ------------ -------------
2 4806 0 0 0
2 5159 0 0 0
2 6843 0 0 0
2 7975 0 0 0
2 8192 0 0 0
2 10728 0 0 0
1 4 0 0 0
1 1776 0 0 0
1 3460 0 0 0
1 4319 0 0 0
1 4799 0 0 0
1 4887 0 0 0
1 8220 0 0 0
1 8544 0 0 0
1 10478 0 0 0
1 11031 0 0 0
1 11110 0 0 0
1 11369 0 0 0
1 12983 0 0 0
1 14120 0 0 0
1 17285 0 0 0
1 17530 0 0 0
1 17813 0 0 0
1 17872 0 0 0
60025 event不可以动态在系统级修改生效，在spfile级修改重启生效；session级启用60025后，也不会立即释放之前的temp lobs.下面做个测试:
[oracle@weejar1:/home/oracle] ora
SQL*Plus: Release 11.2.0.3.0 Production on Wed Jan 17 14:27:24 2018
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
USERNAME INST_NAME HOST_NAME SID SERIAL# VERSION STARTED SPID OPID CPID SADDR PADDR
-------------------- -------------------- ------------------------- ----- -------- ---------- -------- --------------- ----- --------------- ---------------- ----------------
SYS anbob1 weejar1 17872 50597 11.2.0.3.0 20171114 19239 3327 19235 C0000016152D86D8 C000001652FD99F0
-ne
SQL> declare
2 b blob;
3 begin
4 dbms_lob.CREATETEMPORARY(b,true);
5 dbms_lob.freeTEMPORARY(b);
6 end;
7 /
PL/SQL procedure successfully completed.
SQL> select * from gv$temporary_lobs;
INST_ID SID CACHE_LOBS NOCACHE_LOBS ABSTRACT_LOBS
---------- ---------- ---------- ------------ -------------
...
1 14120 0 0 0
1 17285 0 0 0
1 17530 0 0 0
1 17813 0 0 0
1 17872 0 0 0
24 rows selected.
SQL> select u.inst_id,s.sid,s.status,tablespace,u.sql_id,segfile#,u.blocks,segfile#-p.value rfile from gv$tempseg_usage u,v$parameter p,gv$session s where u.session_addr = s.saddr AND u.inst_id = s.inst_id and p.NAME='db_files';
INST_ID SID STATUS TABLESPACE SQL_ID SEGFILE# BLOCKS RFILE
---------- ---------- -------- ------------------------------- --------------- ---------- ---------- ----------
1 17872 INACTIVE TEMP1 g0wf0fkkqwaya 2033 128 9
1 17813 INACTIVE TEMP cbpmfx12jzj4r 2025 128 1
...
SQL> alter session set events '60025 trace name context forever';
Session altered.
SQL> select u.inst_id,s.sid,s.status,tablespace,u.sql_id,segfile#,u.blocks,segfile#-p.value rfile from gv$tempseg_usage u,v$parameter p,gv$session s where u.session_addr = s.saddr AND u.inst_id = s.inst_id and p.NAME='db_files';
INST_ID SID STATUS TABLESPACE SQL_ID SEGFILE# BLOCKS RFILE
---------- ---------- -------- ------------------------------- --------------- ---------- ---------- ----------
1 17872 INACTIVE TEMP1 g0wf0fkkqwaya 2033 128 9
1 17813 INACTIVE TEMP cbpmfx12jzj4r 2025 128 1
...
SQL> declare
2 b blob;
3 begin
4 dbms_lob.CREATETEMPORARY(b,true);
5 end;
6 /
PL/SQL procedure successfully completed.
SQL> select * from gv$temporary_lobs;
-- sid 17872 released
Note:
因为temp seg同会话可以reuse, 再配置了60025 event后，再次执行lob操作后， temp lob自动清理。
系统级修改
alter system set event='60025 trace name context forever' scope=spfile;
对于已存在的会话可以使用oradebug 启用指定sid
oradebug setorapid <oracle pid> oradebug event 60025 trace name context forever -- wait the session next call lob to clean temp lob by itself
为什么当使用连接池时，TEMP SPACE从不释放,即使配置了60025 event？
Tempory LOB segment in TEMP tablespace keep increaseing when customer uses CONNECTION POOL (Doc ID 2297060.1)
A Temp LOB was created when any SQL statement (or PLSQL statement) was executed on a Session.After that, the corresponding session is returned to the connection pool after completion of the execution of the corresponding SQL statement.However, because the session exit has not occurred, the Temp LOB used by the session still remains and is not released.
If the session is reassigned and new SQL statements that generate Temp LOBs are executed, the existing Temp LOB is not reused and new Temp LOB segments are created and used.
The temp LOB grows as the same cycle repeats over several sessions.In this situation, Temp LOBs used in a specific session must be freed explicitly at the application level in order for the same session to reuse it.According to Bug 5723140,since 9i onwards we have been using only one temp segment for temp lobs in a session and all these temp lobs share the same temp segment. When there is a request to cleanup those temp lobs we do free space from temp tablespace and the same session can reuse it, but the temp segment is not released which means it is not available for use to other sessions. The actual cleanup happens only on session exit thru the callback that was registered for this purpose. Hence multiple sessions using temp lobs will have these temp segments active though unused, thus leading to temp segment growth.
正如note中描述的从9i以后，session使用一个临时段存在所有temp lobs,但是用完后free空间，同一会话可以reuse, 临时段只有到exit时才会释放，否则一直占用。如果session 使用了connect pool连接池的长连接，在完成相应 SQL 语句的执行后，相应会话将返回到连接池。但是，由于未发生会话退出，因此会话使用的临时 LOB 仍会保留，不会被释放。
如果重新分配（reassigned ）会话并执行生成临时 LOB 的新 SQL 语句，则不会重用现有的临时 LOB，而是会创建和使用新的临时 LOB 段。随着同一循环在多个会话中重复，临时 LOB 会不断增长。在这种情况下，必须在应用程序级别明确释放特定会话中使用的临时 LOB，以便同一会话可以重用它。
所以即使配置了 Event 60025, 只要使用连接池，Temp LOB 也不会释放，因为 CACHE_LOB 存在（ both CACHE_LOB and NOCACHE_LOB values must be zero）如下:
SQL> select * from GV$TEMPORARY_LOBS; INST_ID SID CACHE_LOBS NOCACHE_LOBS ABSTRACT_LOBS ------- --- ---------- ------------ ------------- 1 9 38179 0 0 1 16 9230 0 0
我们发现的一些问题示例：
- Oracle TEMP 表空间在 Oracle 11g 和 12c 上持续增加
- Web Logic 连接池中的会话未清除 TEMP 表空间
- 使用 HikariCP，TEMP 表空间中的临时 LOB 段不断增加
解决方案
–10.2.0.4 and above
alter session set events '60025 trace name context forever'
DBMS_LOB.FREETEMPORARY
如果使用连接池时未发生会话退出，则必须通过在创建段的会话的应用程序上调用 DBMS_LOB 上的 FREETEMPORARY 或 OCI 上的 OCIDurationEnd 来明确清理生成的临时 lob。
declare
clb clob;
ch varchar2(32767);
k number;
begin
dbms_lob.createtemporary(clb,true,dbms_lob.call);
for i in 1..1500 loop
ch:=lpad('o',32767,'Y');
dbms_lob.writeappend(clb,length(ch),ch);
end loop;
k:=dbms_lob.getlength(clb);
dbms_lob.freetemporary(clb);
dbms_output.put_line('the clob length: '||k);
end;
/
- connect pool or jdbc 配置
或建议设置connect pool一个更低的maxLifetime值Exiting the session，取决于应用程序中临时 LOB 的创建率，临时存储直到发生“会话退出”才会被释放。只有当连接从池中退出时才会发生“会话退出”。
修正 JDBC 错误configuration，数据库连接数远低于 Tomcat 最大线程数。这将使数据库会话保持打开状态，以便处理更多请求。这些会话将保存 TEMP 表空间中的对象，如果您已经在使用 Hibernate JDBC 配置，请将其更改为 Tomcat 级别的 JDBC 数据源
- Java code
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 java.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();
// this block does the same as 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 = "<USERNAME>";
String password = "<PASSWORD>";
String thinConn = "jdbc:oracle:thin:@//<HOST>:<PORT>/<SERVICE>";
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;
}
}
there are 2 equivalent ways of coding the release of the LOB resources.
- java.sql.NClob.free()
- (OracleCallableStatement) conn.prepareCall(“begin DBMS_LOB.FREETEMPORARY ( ? ); end;”);
总结
temp表空间的使用率并不同于datafile，当使用lob或一些SQL时可能会产生临时的lob_data, 该空间不会释放，同一个session中可以复用，直到会话退出，配置event 60025可以在会话退出前，主动释放lob temp， 但是要求当前session使用temp lob不活动时(cache temp和nocache temp lobs都为0时), 当应用程序使用了connect pool，session可能会因为cached lob无法释放，即使配置了event 60025, 此时需要应用显示的释放清理临时lob。
References
Tempory LOB segment in TEMP tablespace keep increaseing when customer uses CONNECTION POOL (Doc ID 2297060.1)
How to Release the Temp LOB Space and Avoid Hitting ORA-1652 (Doc ID 802897.1)
How to Release Temporary LOB Segments without Closing the JDBC Connection (Doc ID 1384829.1)
http://175.178.24.119/21c/content/adlob/temporary-LOB-APIs-in-different-programmatic-interfaces.html#GUID-67D95488-6B13-4941-9D13-5D1CB4F3D87C
微信扫一扫，打赏作者吧～
对不起，这篇文章暂时关闭评论。