Re: Temp tablespace

"tingl" <all4one_at_one4all.not> wrote in message news:tmoha.1825$rN3.174273_at_newsread2.prod.itd.earthlink.net...
> To free up temp space after sorts, you need to change the temporary
> tablespace to the type permanent. This may sound confusing but it works.

Er, and don't forget to immediately change it *back* to being type 'temporary'. Changing the contents of the tablespace in this way causes SMON to do its coalesce thing, and as part of that it drops temporary segments found within a proper temporary tablespace.

This is of course something that you shouldn't really ever do, because the entire point of a temporary tablespace is that those temporary segments *shouldn't* be dropped, but remain there for re-use by anyone performing later sorts. Dropping them means that they need to be re-created by the first sorts that take place after the drop, and that means those sorts run slower than they otherwise would.

The concern that temporary tablespace fills up should not be a concern: it's designed that way. The continued existence of temporary segments (which, however, get re-used by anybody who needs to) means the thing is supposed to be always 100% full. The concern about the continued growth of the temporary tablespace is dealt with in the first instance by switching autoextend off. The sorts that then need additional space will of course start to fail if the tablespace cannot grow to accomodate them... but at that point, you'll know what it is that is making such heavy demands on your temporary tablespace, and (should) be able to tune the problem away, properly.

With all that said, it is true that earlier versions of Oracle sometimes had, er, 'quirky' handling of the re-sharing of temporary segments... which is why running on 8.1.6 is not something I'd be particularly happy about. It's no longer supported, was known to have all sorts of 'additional and unexpected features', and 8.1.7.4 is much the better bet.

Regards
HJR
> "Allen" <aprinsloo_at_websoft.co.za> wrote in message
> news:236c75fa.0303280302.64b114ab_at_posting.google.com...
> > Hi,
> >
> > Due to my limited Oracle experience this question might seem a bit
> > basic, but I'd appreciate any help.
> >
> > Product: Oracle 8.1.6
> > Platform: MS Server 2000
> >
> > How do I clean out the space used by the temp table space on our
> > Oracle database server. The temp table space is of type "tempory" but
> > keeps filling up and growing, is there a way to automate the clean out
> > of it or do I need to manually do something to it?
> > Previous dbms's I've worked on Sybase and MS Sql Server cleaned out
> > tempory db work space after every commited transaction, this does not
> > seem to be the case with Oracle... or am I msitaken?
> >
> > Thx in advance
> > Allen
> >
>
>
Received on Sat Mar 29 2003 - 17:05:18 CST