Re: ORA-03232

From: Brian Tkatch <SPAMBLOCK.Maxwell_Smart_at_ThePentagon.com.SPAMBLOCK>
Date: Mon, 14 Jan 2002 17:48:01 GMT
Message-ID: <3c431441.554131422@news.alt.net>

On Mon, 14 Jan 2002 15:38:35 GMT,
SPAMBLOCK.Maxwell_Smart_at_ThePentagon.com.SPAMBLOCK (Brian Tkatch) wrote:

>>From the manual (Oracle 8i Error Messages):
>
>>"ORA-03232 unable to allocate an extent of string blocks from
>>tablespace string
>>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."
>
>>So look up your HASH_MULTIBLOCK_IO_COUNT in V$parameter and adjust it
>>(or NEXT) accordingly.
>
>>Jaap.
>
>>On Fri, 11 Jan 2002 19:57:27 GMT,
>>SPAMBLOCK.Maxwell_Smart_at_ThePentagon.com.SPAMBLOCK (Brian Tkatch)
>>wrote:
>
>>snip
>>
>>ERROR at line 1:
>>ORA-03232: unable to allocate an extent of 127 blocks from tablespace
>>3
>> snip
>>
>>Brian
>
>Well, I tried this:
>
>SELECT Value FROM V$Parameter WHERE Name = 'hash_multiblock_io_count';
>
>VALUE
>--------------------------------------------------------------------------------
>0
>
>1 row selected.
>
>So, I'm not sure that I can decrease it.
>
>SELECT NEXT_EXTENT FROM user_tablespaces where tablespace_name =
>'TEMP';
>
>NEXT_EXTENT
>-----------
> 65536
>
>1 row selected.
>
>It seems to be nice.
>
>host df -h /u01
>Filesystem Size Used Avail Use% Mounted on
>/dev/sda6 22G 16G 4.7G 77% /u01
>
>Seems to be room enough to expand.
>
> host ls -lh temp*
>-rw-r--r-- 1 oracle dba 1.0G Jan 11 14:47 temp01.dbf
>-rw-r--r-- 1 oracle dba 200M Jan 11 14:47 temp02.dbf
>-rw-r--r-- 1 oracle dba 200M Jan 11 14:47 temp03.dbf
>
>Seems to have another some time to go before being full (files are
>limited to 1 gig). The first file should be empty anyway.
>
>Brian

OK, I found out a bit more, but I am all foncused and muxed ip.

First. I did a search and came up with the following: <URL:http://technet.oracle.com/docs/products/designer/doc_library/6i_release2/cmnhlp72/rep_relnotes/rn_rep_6i.htm>

<<<
ORA-03232 during migration of Oracle Designer Problem:
During migration from version 2.x or 6.0 Oracle Designer to version 6.5 the following error may be reported:

ORA-3232 unable to allocate an extent of %s blocks from tablespace %s This is because a join of two tables required a sort operation that requested a temporary table segment. The size of the segment requested was larger than the "NEXT" extent of the temporary tablespace from which it was requested.

Workaround:
From the problem description it can be seen that there are two possible solutions. Firstly, the NEXT extent of the temporary tablespace cited in the error could be increased, or the request size can be reduced to fit within the NEXT extent.

The preferred solution is to reduce the request size. To do this examine the databases init.ora file (or query SYS.V$PARAMETER) to determine your database block size. Usually this will be a multiple of 2K bytes, such as 4096 or 8192. Then issue the sql command:

     select next_extent/<DB_BLOCK_SIZE> 
     from user_tablespaces 
     where tablespace_name = '<THE_TABLESPACE_NAME>'

replacing <DB_BLOCK_SIZE> with the appropriate value and <THE_TABLESPACE_NAME> with the name given in the original error message.

Take the value calculated and set the init.ora parameter hash_multiblock_io_count to be equal to or less than it.

Then restart the database and restart the migration. <<<

Next extent is 65536, and the DB_BLOCK_SIZE is 8192, thus the request size should be no greater than 8. Lo and behold, when I set HASH_MULTIBLOCK_IO_SIZE to 9, it fails, but at 8 it runs smoothly. (Interesting, it that it finishes under 9 minutes, when the initial error appears after 13 minutes.)

So, the question is, what did Oracle want. I had HASH_MULTIBLOCK_IO_SIZE set to 0, which means that Oracle decided its fate.

The error I got was "ORA-03232: unable to allocate an extent of 127 blocks from tablespace 3". Which means that Oracle set HASH_MULTIBLOCK_IO_SIZE to 127 for this query. That is rather large. 127 * 8196 = 1040892. That ranks in at ~99.26% of a meg (1024 * 1024 = 1048576. 1040892 / 1048576 = 0.992671966552734375).

Well, that works, though I am afraid that I may not be aware of al the consequences. Besides, Oracle Reference does say "Oracle Corporation does not recommend that you set or change the value of this parameter". So I'd rather not change it unless I must.

So, with changing HASH_MULTI_BLOCK_IO_SIZE, I did some testing.

I guessed that this had to do with HASH_AREA_SIZE. The Oracle Reference mention that it defaults to 2 * SORT_AREA_SIZE. I just changes SORT_AREA_SIZE by a factor of 10 (was 10 meg, now its 100), so this is obviously a significant difference to HASH_AREA_SIZE. I verified it by querying V$Parameter. It jumped from 20 meg to 200 meg.

So, I dutifully tried an ALTER SESSION to SET HASH_AREA_SIZE back to 20 meg. The error still appeared.

Further, I grabbed the init.ora from the live database, and restarted. It still wanted 127 blocks. Now I am completely baffled.

Brian Received on Mon Jan 14 2002 - 11:48:01 CST