Re: ORA-03232

On Thu, 2 Sep 2004 15:22:56 +0500, fahd mirza <fahd-m_at_aero.com.pk> wrote:
> Dear Listers,
> I created a locally managed tablespace with the following command,
>
> CREATE TABLESPACE pms DATAFILE 'G:\ORACLE\ORADATA\VIEW\pms.DBF' SIZE 200M AUTOEXTEND ON EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K;
>
> and then imported some tables, which occupied approximate 99% space in the tablespace. But when a users tried to run queries on the tables, it gave the following error,
>
> 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.
>
> As according to Oracle documentation, When you allocate a locally managed tablespace, you cannot specify default storage parameters or minimum extent size. So I cant increase the value of NEXT, also user settings for HASH_MULTIBLOCK_IO_COUNT are highly un-recommended by Oracle,besides its complex enough.
>
> The rest of the schemas were analyzed but not this fresh one. The queries were also joining the tables from the other schemas. when I also analyzed this fresh schema, the error disappeared and everything was again fine.

> Would please somone clarify what happened actually?

Most likely

the string value in tablespace string referred to your temp tablespace where you were required to sort, and not to the new data ts. When you got appropriate stats, you got appropriate plans that didn't require sorting/excessive temp ts use :) .

As a note since you were joining to tables that did have stats, and assuming a not so recent version (I'd hazard a guess this was some sort of 8i) so you don't get dynamic sampling, you will be using the CBO and the CBO will use default values for the stats (100 row tables in very little space IIRC).

-- 
Niall Litchfield
Oracle DBA
http://www.niall.litchfield.dial.pipex.com
---
To unsubscribe - mailto:oracle-l-request_at_freelists.org&subject=unsubscribe 
To read recent messages - http://freelists.org/archives/oracle-l/09-2004