Re: TEMP tablespace grows all the time!

From: Kevin Grant <X.Tenag_at_znvyobk.th.rqh.nh>
Date: 2 Apr 2002 00:01:38 GMT
Message-ID: <Xns91E46633D8B54KGRANTBNEQLDAU@132.234.250.31>

mark.powell_at_eds.com (Mark D Powell) wrote in news:178d2795.0204010659.43a3db44_at_posting.google.com:

> tho_pic_at_yahoo.com (Tho Nguyen) wrote in message
> news:<d177c6d9.0203311721.5f09ff41_at_posting.google.com>...

>> zeevikm_at_hotmail.com (SGA) wrote in message
>> news:<b0fb5246.0203310632.3ed23a3a_at_posting.google.com>... 
>> > Hello All.
>> > 
>> > RDBMS: 8.1.7.3
>> > O/S  : Sun Solaris 8
>> > 
>> > 
>> > Localy Managed Temporary tablespace (with unlimited autoextend
>> > enabled on datafiles) is 40G already - and keeps growing.
>> > Tablespace is locally managed, and the DB is 24x7 environment,
>> > so shutdown is not an option to reduce the tablespace size.
>> > 
>> > I know such a problem exists since early 8i versions (at least
>> > 8.1.6 and up), but nobody seems to know the answer ...
>> > 
>> > Please Advice.
>> > Regards.
>>  How about making offline and back online?

>
> 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.

-- 
Kevin Grant
X.Tenag_at_znvyobk.th.rqh.nh - ROT13

Received on Mon Apr 01 2002 - 18:01:38 CST