Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Urgent prb - unable to allocate an extent

Re: Urgent prb - unable to allocate an extent

From: Howard J. Rogers <howardjr_at_www.com>
Date: Fri, 22 Sep 2000 22:44:18 +1000
Message-ID: <39cb459f@news.iprimus.com.au>

I was going to launch into a long spiel about fragmentation and coalescing, but then I read the exceptionally fine manual that is available at technet.oracle.com...

viz:
ORA-03232: unable to allocate an extent of num blocks from tablespace name 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.

However, this is Oracle 8i documentation, and since you don't tell us what version of Oracle you're working on (which would always help), and since it's vaguely possible that error messages have changed between versions, perhaps I will give you the spiel about fragmentation after all....

First identify the tablespace correctly... select * from v$tablespace should show you a tablespace number (note that the documentation above suggests that, if you have reported the error message accurately, "3" is the NAME of your tablespace, not its number).

Second, try alter tablespace xxx coalesce, and then try inserting your new record.

If that works, it suggests you have tablespace fragmentation... which is where you have heaps of free space in a tablespace, but unfortunately none of it is contiguous, but is instead made up of lots of little pieces, each one of which is too small for your segment to extend into. Coalescing will merge adjacent free extents into a single large one -and hopefully, one of them will be large enough to accomodate your segment growth.

And if that *is* the problem, then it's time you read up on fragmentation! Though in truth there is not much to read: all tablespaces should house segments which share the exact same extent size, inital should equal next, and pctincrease should be zero. And if you have segments that need to grow in different extent sizes, house them in different tablespaces.

If that *isn't* the problem, then check the next extent size settings for the table you are inserting into, and make sure that it is sensible. And try an 'alter tablespace xxx add datafile 'path\filename' size xM, where x is at least big enough to accomodate your next extent size. If *that* works, it's time to work out what segments are inside that tablespace, and consider doing a complete reorganisation of the tablespace involving a few exports, drops and imports.

Let me know how you get on
Regards
HJR

--
--------------------------------------------------------------------------
Opinions expressed are my own, and not those of Oracle Corporation
Oracle DBA Resources:               http://www.geocities.com/howardjr2000
--------------------------------------------------------------------------



"Rob Edgar" <robedgar_at_hkstar.com> wrote in message
news:8qfekd$ck33_at_imsp212.netvigator.com...

> I am trying to insert records into a table and get the following error
>
> ORA-03232: unable to allocate an extent of 12 blocks from tablespace 3
>
> I cant figure out which is tablespace 3 but I guess its my Users which is
> where this table is but I have 400mb of space the table itself is in one
> extent and currently is 50mb in size so I dont get it??
>
>
> Anyone know what the problem might be
> TIA
> Rob
>
>
Received on Fri Sep 22 2000 - 07:44:18 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US