Re: TEMP tablespace grows all the time!

Reading the document you referenced (and as you state in your final sentence), it seems to imply that just the OPPOSITE (of earlier statements in this thread) is the problem: creating a temporary tablespace with tempfiles ("create temporary tablespace temp tempfile xxx...") causes the sort segments not to be dropped until the instance is shutdown, and that SMON will drop the sort segments for "PERMANENT" temporary tablespaces, created via the first statement ("create tablespace temp datafile xxx temporary...").

In simpler language, if your database needs to be up 24x7, don't use "create temporary tablespace...." Use "create tablespace ... temporary" instead.

In article <178d2795.0204020610.44e583c9_at_posting.google.com>, mark.powell_at_eds.com (Mark D Powell) wrote:

>> > To SGA, this may be a 'feature' of Oracle. In certain circumstances
>> > the rdbms does not release the sort space associated with sessions
>> > created through a web server even after the session terminates. This
>> > sort space is then unavailable for reuse so either the sort tablespace
>> > grows very large or your system runs out of sort. There are been
>> > dozens of complaints filed on the metalink forums about this. This
>> > problem occurs when the temporary tablespace was created using
>> > permanent data sets instead of temporary data sets, that is,
>> > create temporary tablespace temp datafile 'xxx' was used instead of
>> > create temporary tablespace temp tempfile 'xxx'
>> >
>> > The only way to release the space is to bounce the instance. If you
>> > rebuild your temp tablespace to use tempfiles instead of datafiles at
>> > the same time you should eliminate the problem. See metalink document
>> > id 1965678 updated 10-Oct-2001.
>>
>> Probably you meant:
>>
>> "create tablespace temp datafile 'xxx' temporary was used instead of
>> create temporary tablespace temp tempfile 'xxx'"
>>
>> 'datafile' is an invalid option for the 'create temporary tablespace'
>> command.
>>
>> Kev.
>
>Maybe though it seems the note implied the above was possible in 8.1,
>but then Oracle support notes are not always clearly worded. I cannot
>even find the ID today when I search for it but doc id: 148205.1,
>Temporary Tablespace Extents are Not Being Deallocated in 8.1, does
>seem to cover the problem, but it recommends the use of datafiles, not
>tempfiles, with type temporary tablespaces.
>
>-- Mark D Powell --
Received on Wed Apr 03 2002 - 07:51:31 CST