oracle lob对象属性,LOB对象与临时段

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

By eygle on 2006-03-25 15:34 |

Comments (0) |

Internal | 719 |