- 论坛徽章:
- 0
|
Anybody can tell me how to find the "MAX_IO_SIZE"
最近遇到如下一個錯誤.找到解決辦法如下.但無法找到MAX_IO_SIZE,
的大小
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 |
|