Re: Urgent prb - unable to allocate an extent

What does "Turns out ts3 is the temporary table space??" mean? Did v$tablespace say it was called TEMP or what? Why not include sample output in your responses and questions?

Well, if it *is* temporary tablespace, why not drop and recreate it and see how you go? Can't do any harm.

You might just check beforehand that this tablespace has been properly created as temporary (check the 'contents' column in dba_tablespaces).

Given that you have 8.1.6, you can re-create with a 'create temporary tablespace xxx tempfile 'path/name' size xM extent management local uniform extent size Y' (check the syntax -this is close but might not be quite right in all the details).

The extent size should be a multiple of your sort_area_size parameter in the init.ora. The tempfile size should be a multiple of *that* plus 64K for the extent bitmap.

But if all you are doing is a simple insert, I would be very surprised to see the Temporary Tablespace used. Care to be more forthcoming with details which may help diagnosis -such as exactly what operating system you are running on, what *exactly* you are doing that causes the error and so on.

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:8qfklu$121_at_imsp212.netvigator.com...


> 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

> > >

> > >

> >

> >

>

>