Re: Urgent prb - unable to allocate an extent

"Rob Edgar" <robedgar_at_hkstar.com> wrote in message news:8qfpkn$146_at_imsp212.netvigator.com...
> 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...
>

Oh, yes... The only thing that ever gets swapped down to the temporary tablespace is PGA-sized chunks, so the extent sizes should match (though they can be multiples of the sort_area_size instead of just one-for-one).

You can alter the next extent sizes any time, so maybe you don't need to re-create. But it sounds like you've used dictionary managed tablespace.... and since you say performance is king on your database, I'd strongly recommend re-creating anyway with locally managed tablespace.

This is difficult to advise without lots more detail, but it's a bit odd to have a shared pool size that is half the size of your buffer cache. The proportions are usually the other way around, and in a decision support system that is presumably subject to lots of querying, you'd benefit from a bigger shared pool in which to store all that parsed SQL.

Anyway: let me know how the TEMP thing resolves itself.

Suspect we'll probably talk again soon anyway about your end of file on communication channel issue!!

Regards
HJR
> 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

[snip] Received on Fri Sep 22 2000 - 18:32:29 CDT