Kevin Grant <X.Tenag_at_znvyobk.th.rqh.nh> wrote in message news:<Xns91E46633D8B54KGRANTBNEQLDAU_at_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.
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.
Received on Tue Apr 02 2002 - 08:10:38 CST
