| LOB变量与TEMP空间 | DBA之路：OoNiceDream |

LOB变量与TEMP空间

作者：OoNiceDream【转载时请务必以超链接形式标明文章原始出处和作者信息】
链接：http://www.dbaroad.me/archives/2009/09/lob_temp.html

最近一测试库上频繁遇到ORA-1652: unable to extend temp segment by 128 in tablespace TEMP，只要一进行性能测试，就报错。检查TEMP的使用情况，耗用最多的类型全是LOB_DATA，而且随着测试进行，一直往上涨。一开始以为是在存储过程中定义的LOB、CLOB变量，所占用的TEMP空间是无法自动释放导致的，虽然最终原因是在java代码中没有释放，但存储过程中的这个现象，还是值得记录一下的。

举个例子：

SQL> select max(sid) from v$mystat;
 
  MAX(SID)
----------
        45
 
SQL> DECLARE 
  2  a clob; 
  3  BEGIN 
  4  dbms_lob.createtemporary(a, TRUE,dbms_lob.call); 
  5  dbms_lob.freetemporary(a); 
  6  END; 
  7  /
 
PL/SQL procedure successfully completed.
 
SQL> select count(*) from dual;
 
  COUNT(*)
----------
         1
 
SQL> SELECT se.inst_id,
  2         se.username username,
  3         se.SID sid,
  4         se.status status,
  5         se.sql_hash_value,
  6         se.prev_hash_value,
  7         su.TABLESPACE tablespace,
  8         su.segtype,
  9         su.CONTENTS CONTENTS,
 10         round(su.blocks * 8192 / 1024 / 1024, 2) MB 
 11  FROM gv$session se,
 12       gv$sort_usage su
 13  WHERE se.saddr=su.session_addr
 14  AND   se.inst_id=su.inst_id
 15  ORDER BY MB;
 
INST_ID USERNAME SID STATUS SQL_HASH_VALUE PREV_HASH_VALUE TABLESPACE SEGTYPE  CONTENTS  MB
------- -------- --- ------ -------------- --------------- ---------- -------- --------- --
      1 SYS       45 INACTI      317853294       317853294 TEMP       LOB_DATA TEMPORARY  1

此时CLOB占用的TEMP空间不会自动释放，需要等待会话断开，才能释放。但这个空间，在本会话中，还是可以重用的，只是不供其它会话使用。

在10.2.0.3以前，只能让会话退出，以释放这部份空间，在10.2.0.4中当作一个BUG（Bug:5723140）来修复，但默认不激活，需要通过设置60025事件才可以释放这些lob的TEMP空间。以下是10.2.0.3中通过打5723140补丁来设置这个事件：

$ opatch lsinventory
Invoking OPatch 10.2.0.3.0
 
Oracle interim Patch Installer version 10.2.0.3.0
Copyright (c) 2005, Oracle Corporation.  All rights reserved..
 
....................
....................
 
Interim patches (3) :
 
Patch  5723140      : applied on Mon Sep 07 17:04:17 GMT+08:00 2009
   Created on 26 Dec 2007, 03:25:35 hrs US/Pacific
   Bugs fixed:
     5723140
....................
....................
 
SQL> select * from v$version where rownum&lt;2;
 
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi
 
SQL> DECLARE 
  2  a clob; 
  3  BEGIN 
  4  execute immediate 'alter session set events '||'''60025 trace name context forever''';
  5  dbms_lob.createtemporary(a, TRUE,dbms_lob.call); 
  6  dbms_lob.freetemporary(a); 
  7  END; 
  8  /
 
PL/SQL procedure successfully completed.
 
SQL> SELECT se.inst_id,
  2         se.username username,
  3         se.SID sid,
  4         se.status status,
  5         se.sql_hash_value,
  6         se.prev_hash_value,
  7         su.TABLESPACE tablespace,
  8         su.segtype,
  9         su.CONTENTS CONTENTS,
 10         round(su.blocks * 8192 / 1024 / 1024, 2) MB 
 11  FROM gv$session se,
 12       gv$sort_usage su
 13  WHERE se.saddr=su.session_addr
 14  AND   se.inst_id=su.inst_id
 15  ORDER BY MB;
 
no rows selected