Re: temporary tablespaces

"Sybrand Bakker" <postbus_at_sybrandb.nospam.demon.nl> wrote in message news:<vm3vv8duaeofeb_at_corp.supernews.com>...
> "David COSTE" <dco_at_compaq.com> wrote in message
> news:Onl8b.4630$je2.2848_at_news.cpqcorp.net...
> > It's because my batch processing consumes temporary segments and I don't
> > want to see it run out of space because some one would have launched a big
> > query with an order by.
> > The idea is to avoid sharing the same temp tablespace for the 2
> activities.
> >
> >
> This is not going to help you at all. If you set up 2 different tablespaces
> for 2 different users/processes, those processes will still be capable to
> have your temp tablespaces out of space.
> Make sure you have adequate space, as disk is cheap, and stop symptom
> fighting.

Just a thought: How about if the purpose is to isolate a particularly heavy user (say a batch process) or a group of ad hoc users so that even if they fill their temp, it has no effect on other production user's temp space? Maybe even distribute the datafiles on different disks for performance? For example, if you had 1000 users running normal queries all the time with a 1GB temp of which 500MB is typically in use at any one time, and one ad hoc quaery comes in and suddenly consumes the extra 500MB, who is to tell which user will error out first because they can't grap their next needed extent in temp?

Also, if memory serves me, I believe that a temp tablespace once it gets filled, still shows as full until you bounce the db which I find anoying at times (like 2AM). Received on Tue Sep 16 2003 - 16:02:46 CDT