Re: Urgent prb - unable to allocate an extent

Well the original error message says "tablespace 3" and using select * from v$tablespace that you suggested the tablespace number 3 is named "temp" which is of type TEMPORARY with init extent64k and next64k.

The sql statement is

insert into webpro.component_characteristic select c.componentseq, 'XJE014', m.des
from desmond.main_onsemi_info m, webpro.component c where c.statusno = 25
and c.part_number = m.upartno
and m.statusno=10

sometimes this produces the "unable to allocate" error and sometimes end-of-file on communications channel ORA-03113, though the unable to allocte error only showed up this afternoon , 03113 has sporadic for the last couple of weeks with large insertion's.

From what you said about the extent size of the temp space I think I know what the issue is, we restarted the server this afternoon and change the sort area size from 64k to 1mb to improve sorts and queries, I didnt realize it would impact the temporary tablespace...

OK details O/S is Win2k, the database is predominatley for decision support over the web, there is almost no input or at least it is negligible ie maybe one or two session a day doing input but a hug volumn of querying the db via the web, ie anonymous pooled connections from the web server. We have 512mb ram double cpu going to quad cpu next month.We use 256mb of ram for block buffers and 100mb for shared pool size

Everything is to be biased owards making queries and sorts very fast at the expense of slower input.

At the moment we are moving data into the final tables from staging areas, this particualr table has 500,000 reords and is 50mb in size and will triple over the weekend as we load in the data

Rob

"Howard J. Rogers" <howardjr_at_www.com> wrote in message news:39cb5df8_at_news.iprimus.com.au...
> 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
> > > >
> > > >
> > >
> > >
> >
> >
>
>
Received on Fri Sep 22 2000 - 09:07:59 CDT