Re: Urgent prb - unable to allocate an extent

Well...
First the tsname is absolutley not 3 but I did report the error message accuratley, second I am not so dumb(well only half dumb) I did try the obvious things, I actually did add another datafile with 200mb of space and I did check I had at least 30mb contigous free space before doing the insert which was actually only 6000 records of about 1k,.

Turns out ts3 is the temporary table space??

temporary ts is 300mb in size 99% free with a frag index of 4....

Never really touched the temp ts before..... just thought you left these sort of as is after the defualt creation...

Should I drop and recreate the temp ts and if so what settings would be "normal" if there is such a thing

PS we are running 8ir2
Rob

"Howard J. Rogers" <howardjr_at_www.com> wrote in message news:39cb459f_at_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:43:19 CDT