coba ini bro :
=========================
When HASH_MULTIBLOCK_IO_COUNT it set to 0, it means that Oracle
computes the value
for each query. Sometimes ORA-3232 may be encountered when a query uses
HASH JOIN.
What does it mean?
------------------
Error: ORA-3232
Text: 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).
In order to try and avoid this error, please see the options below.
Option 1
--------
Set 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.
Obtain the correct tablespace name by
select name from sys.ts$ where ts# = <tablespace_number>;
Once you have the MAX_IO_SIZE, alter the NEXT value of the using ALTER
TABLESPACE
syntax.
Example: With MAX_IO_SIZE= 128K the default storage for the initial
and next extent
should be >= (128 * 1024) = 131072 bytes.
SQL> SELECT initial_extent, next_extent
2 FROM dba_tablespaces
3 WHERE tablespace_name = 'TEMP';
INITIAL_EXTENT NEXT_EXTENT
-------------- -----------
65536 65536
SQL> ALTER TABLESPACE temp DEFAULT STORAGE (INITIAL 128k NEXT 128k);
Tablespace altered.
SQL> SELECT initial_extent, next_extent
2 FROM dba_tablespaces
3 WHERE tablespace_name = 'TEMP';
INITIAL_EXTENT NEXT_EXTENT
-------------- -----------
131072 131072
Option 2
--------
Set HASH_MULTIBLOCK_IO_COUNT to a value different than 0. This however
should not
be 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.
Note: If you are using Oracle's multi-threaded server architecture,
Oracle ignores
any value you set for this parameter, and instead uses a value of 1.
=========================
source : metalink
.hd.
2009/6/25 Kalam Harun <[email protected]>
>
>
> Dear all,
>
> Beberapa kali saya mendapatkan error
> ORA-03232: unable to allocate an extent of block from tablespace 3
>
> Error tsb muncul ketika saya menjalankan query, baik dari aplikasi maupun
> toad.
> Yang saya lakukan biasanya adalah dengan modify query, dan problem tsb
> solved dan ada juga bahkan saya coba experimen drop/create ulang table
> pen-trigger error tsb dan solved.
>
> Pertanyaannya adalah apa sebenarnya penyebab error tsb ? karena tablespace
> masih memiliki space yg sangat2 cukup.
> Mohon sharing dan pencerahan dari rekan2 sekalian...
>
> Terima kasih
> /
>
> [Non-text portions of this message have been removed]
>
>
>
[Non-text portions of this message have been removed]
