遭遇ORA-03232故障 (Give you some color to see see!)

起因是这样的，昨天重建TEMP表空间的时候，由于偷懒，仅用以下语句CREATE TABLESPACE TEMPDATA datafile '/dev/vgqddata04/rlvqddata04_2000_01'size 2000M TEMPORARY;所有的参数均使用默认值，然后自己执行了几个小查询，没有出现问题就回家了。

今天上午，有应用程序在执行时报错，错误为： ORA-03232 unable to allocate an extent of 10 blocks from tablespace 8根据错误号查询Metalink，Doc ID: Note:125271.1Error: ORA-3232Text: unable to allocate an extent of %s blocks from tablespace %s ---------------------------------------------------------------------------Cause: An attempt was made to specify a HASH_MULTIBLOCK_IO_COUNT value that is greater than the tablespace's NEXT value Action: Increase the value of NEXT for the tablespace using ALTER TABLESPACE DEFAULT STORAGE or decrease the value of HASH_MULTIBLOCK_IO_COUNT.

This parameter determines how many sequential blocks a hash join reads and writes in one IO operation. The maximum value is operating system dependent.

It is always less than the maximum I/O size of the operating system expressed as Oracle blocks (MAX_IO_SIZE / DB_BLOCK_SIZE).

文档说明这个错误是由于哈希连接时顺序读取或写入的连续数据块大小大于相应表空间的next_extent值而引发的。SQL> select * from v$tablespace where TS#=8;

TS# NAME---------- ------------------------------ 8 TEMPDATA

这个表空间的情况：SQL> select initial_extent,next_extent,extent_management from dba_tablespaces where tablespace_name='TEMPDATA';

INITIAL_EXTENT NEXT_EXTENT EXTENT_MANAGEMENT-------------- ----------- ----------------- 4194304 4194304 DICTIONARY

这个表空间INITIAL_EXTENT和NEXT_EXTENT 大小均为40K，即默认值5*db_block_size的大小，采用字典管理方式。再看这个参数hash_multiblock_io_countSQL> show parameter hash_multiblock_io_count

NAME TYPE VALUE------------------------------------ ----------- ------------------------------hash_multiblock_io_count integer 0应用程序要求连续写入10*blocks的内容，即80K，而NEXT_EXTENT 为40K，引起错误。metalink给出的建议是：Option 1Set the initial and next extent size of the temporary tablespace equal to or greater than MAX_IO_SIZE.MAX_IO_SIZE is an operating system dependant parameter. Please refer to the operating system documentation for information about this parameter.增大表空间的initial 和next值，使之等于或大于操作系统的IO大小（MAX_IO_SIZE）。HP-UX11.0的MAX_IO_SIZE为512KOption 2Set HASH_MULTIBLOCK_IO_COUNT to a value different than 0. This however should notbe required as Oracle computes the value individually for every query. If you let Oracle do the automatic computation, the value of the parameter appears as 0 in the V$PARAMETER dynamic performance view.设置HASH_MULTIBLOCK_IO_COUNT为非0的值。

考虑到数据读写效率，并参照之前建库的脚本，最后把initial 和next extent 的大小改为了4096K，问题解决。

测试中发现，该sql只需要将initial 和next extent 改为48K即可正常运行。

另外还有一个问题不能解释：当我把initial 和next extent 设为40K时SQL> select blocks,iniexts from sys.seg$ where file#=145;

BLOCKS INIEXTS---------- ---------- 10 5执行sql时报错，发现是第10个block申请时。并可以理解INIEXTS=40K=5*db_block_size

但是，我将initial 和next extent 设为48K时SQL> select blocks,iniexts from sys.seg$ where file#=145;

BLOCKS INIEXTS---------- ---------- 3650 10此时，INIEXTS为什么是10呢？

希望能找到答案.

网址: http://www.color-cc.com/2006/04/ora03232.html

Posted on April 21, 2006 8:04 PM Under Database. Tags :. RSS-Feed. TrackBack URL for this entry:
http://www.color-cc.com/mt/mt-tb.cgi/255.