Re: TEMP tablespace grows all the time!

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.

HTH -- Mark D Powell -- Received on Mon Apr 01 2002 - 08:59:07 CST