Re: ORA-03232 and HASH_JOIN_ENABLED

This sounds like a bug which I thought had been fixed in 7.3.something.

Try setting the HASH_MULTIBLOCK_IO_COUNT to 1 and the problem may disappear.

Tablespace 3 is usually the TEMP tablespace, which you can check by viewing dba_tablespaces, and when Oracle determines that it will need to use a disk based hash it may find that it needs disk space almost immediately, which is why you can get the error message very quickly.

--

Jonathan Lewis
Yet another Oracle-related web site:  http://www.jlcomp.demon.co.uk

Yuri McPhedran wrote in message <8oo2ag$6lc$1_at_nnrp1.deja.com>...


>Hi there Oracle users,

>

>This posting is for information only - I have already found a fix to

>the problem but it wasted some time and I hope that by sharing the

>details it will make life a bit easier for some of you.

>

>I was given a fairly complicated select statement across six tables

>which was causing an "ORA-03232: unable to allocate an extent of 11

>blocks from tablespace 3" error every time it was run.

>

>We are using Oracle 8iR2 (8.1.6) on NT, though I am not convinced that

>this makes much difference.

>

>The Oracle documentation has this to say...

>

>------------------------

>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.

>------------------------

>

>Well, the tablespace "3" message isn't very helpful, and checking

>V$PARAMETERS I see that HASH_MULTIBLOCK_IO_COUNT=0, so I can't exactly

>decrease it.  There is plenty of free space in every tablespace, so I

>don't understand why I get this sort of error.  Also, the error comes

>back instantly which suggests that it is not a case of the TEMP

>tablespace being exceeded. In any case, there is no "ORDER BY" or other

>clause that would result in a sort.

>

>A quick EXPLAIN PLAN on the statement reveals that there are several

>HASH JOINS taking place.  I therefore decide to rewrite the statement

>and/or use hints so that a different join method is used.  The

>optimizer stubbornly refuses to do anything other than hash joins, so I

>have another search in the manuals and come up with the

>HASH_JOIN_ENABLED init.ora parameter.  This can be set at a session

>level, so I issue a "alter session set hash_join_enabled=false;"

>statement, then re-run the query & it works fine.

>

>So, that's the fix, but if anybody can explain *why* I got the problem

>in the first place I would be very interested!

>

>--

>Regards

>Yuri McPhedran

>

>

>Sent via Deja.com http://www.deja.com/

>Before you buy.