LOB对象与临时段

刚刚和DCBA聊到他们刚刚遇到的一个问题:LOB对象与临时表空间的关系.

如果程序中使用了临时的LOB类型变量,Oracle会分配临时空间,如果并发很高,初始区很大,那么数据库可能产生严重的TEMP表空间的不足问题.

可以通过测试轻易再现这种状况,在多个Session中执行如下代码:

declare                
  A CLOB;              
BEGIN                  
  A := 'ABC';          
  DBMS_LOCK.SLEEP(120);
END;                   
/                     
            

查询v$sort_usage视图,我们可以获得如下信息:

SQL> select s.username, s.sid, u.tablespace, u.contents, u.segtype,     
  2  round(u.blocks*8192/1024/1024,2) MB                                
  3  from v$session s, v$sort_usage u                                   
  4  where s.saddr = u.session_addr                                     
  5  and u.contents = 'TEMPORARY'                                       
  6  order by MB DESC ;                                                 
  

USERNAME                    SID TABLESPACE      CONTENTS  SEGTYPE           MB
-------------------- ---------- --------------- --------- --------- ----------
SYS                           9 TEMP            TEMPORARY LOB_DATA           1
SYS                          10 TEMP            TEMPORARY LOB_DATA           1
SYS                          13 TEMP            TEMPORARY LOB_DATA           1
SYS                          15 TEMP            TEMPORARY LOB_DATA           1

SQL> 

Metalink Note:228479.1 称这并非一个Bug:

To workaround the problem, disconnect the database user session.

This behaviour does not reproduce in Oracle10, but is not a bug.

9i中临时表空间的定义为:

SQL> select BLOCK_SIZE,INITIAL_EXTENT,NEXT_EXTENT,EXTENT_MANAGEMENT,ALLOCATION_TYPE
  2  from dba_tablespaces where tablespace_name='TEMP';

BLOCK_SIZE INITIAL_EXTENT NEXT_EXTENT EXTENT_MAN ALLOCATIO
---------- -------------- ----------- ---------- ---------
      8192        1048576     1048576 LOCAL      UNIFORM

在Oracle8i中情况有所不同.

Oracle 会分配多个段,在Oracle8i中执行如下代码:

SQL> declare
  2    a clob;
  3  begin
  4  dbms_lob.createtemporary(a,true);
  5  dbms_lob.write(a,3,1,'abc');
  6  dbms_lock.sleep(120);
  7  end;
  8  /

会看到如下结果:

SQL> select user,SEGTYPE,CONTENTS,blocks from v$sort_usage;

USER                           SEGTYPE   CONTENTS      BLOCKS
------------------------------ --------- --------- ----------
SYS                            LOB_DATA  TEMPORARY          8
SYS                            LOB_DATA  TEMPORARY          8
SYS                            LOB_INDEX TEMPORARY          8
            

本测试中Oracle8i中临时表空间定义为:

SQL> select INITIAL_EXTENT,NEXT_EXTENT,EXTENT_MANAGEMENT,ALLOCATION_TYPE
  2  from dba_tablespaces where tablespace_name='TEMP';

INITIAL_EXTENT NEXT_EXTENT EXTENT_MAN ALLOCATIO
-------------- ----------- ---------- ---------
         65536       65536 DICTIONARY USER