terima kasih bro, sangat membantu dan mencerahkan thx/kalam On Thu, Jun 25, 2009 at 8:35 PM, HeLmi <[email protected]> wrote:
> > > 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] <kalamharun%40gmail.com>> > > > > > > > > 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] > > > [Non-text portions of this message have been removed]
