Re: Urgent prb - unable to allocate an extent

Well I sized the block buffers to half available mem to get as much of the DB cached in memory as possible then after that split half of remainder to share pool and the rest for the "os"..... thought it was better to cache as much data as possible

Anyway's I created the temp space, had a few prb with it not "finding" the datafile during the create command and in the end I reused the existing temp datafile but under the new temporary tablespace and using local management. That seems OK (except its "dissapeared" as far as my sql tools SQL Navigator and DBArtisan are concerned but thats not a really big deal).

As far as the "eof" prb, I created a new table with the old contents and the insert into this is now OK, I suspect the problem is due to fragmentation in the datafile , as that datafile is littered with a lot of staging tables created by one developer, so I have created a new tablesapce just fro hime and getting him to move all his data there, after which I will delete the old files and coalesece the freespace.... I would call getting an "eof" a bug but I guess Oracle wont, I mean whatever the problem really is it should give a better message or react in a more sensible way than this, not only that but all the log and trace files are of no, help the server records nothing when I get an "eof" so I am left without a clue as to what the problem might be......

Due to trying several thing to get out of th eptoblem I have ended up with some silly sizes for tablepsaces like RBS which is 300 mb but contains only 24 x 4mb rb segments so I need to tidy them up, and the temporary tablesepace is 400mb which sounds way to big too me.

Rob

"Howard J. Rogers" <howardjr_at_www.com> wrote in message news:39cbdd89$1_at_news.iprimus.com.au...
> "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 - 21:44:46 CDT