Re: How to get rid of temporary segments

I have seen this behavior on Digital Unix, HP-UX, and Solaris. In all three cases the temp tablespace was contents = TEMPORARY. The only way I have found to get rid of the allocation is to bounce the instance.

Liz

In article <3A2E4600.234ED151_at_edcmail.cr.usgs.gov>, peasland_at_edcmail.cr.usgs.gov says...
> Was the tablespace created as TEMPORARY? It sounds to me like the
> tablespace was created as PERMANENT, but is being used for sorting. You
> can verify this by issuing "select TABLESPACE_NAME,CONTENTS from
> dba_tablespaces;". The TEMP tablespace should say TEMPORARY in the
> CONTENTS field.
>
> HTH,
> Brian
>
>
> Rob Diaz wrote:
> >
> > I have a similar question, though I don't know if the problem is the same.
> >
> > A client of ours is always complaining about the fact that after they
> > resummarize their data in our application, the TEMPORARY tablespace is about
> > 3.9 GB. Oracle reports that 3.89 GB are "in use". I have re-created the
> > situation here, only it was 3.1 GB (3056MB allocated, 3054MB used). Anyway,
> > this never changed. I just bounced the db and it is now freeing up the
> > temporary space. The client keeps complaining that if they run a second
> > resummarization it tries to grow the tablespace some more until it runs out
> > of disk space. They are using Oracle 7 (UNIX) and I am using Oracle 8.0
> > (NT4) and 8.16 (Win 2K).
> >
> > I have seen this behavior on all of these versions of Oracle.
> >
> > What I am asking is if there is a way to force oracle to clear the temporary
> > space when the query that is using it completes.
> >
> > Thanks
> >
> > Rob
> > Yurasis Dragon <yurasis_spamNOSPAM_at_yahoo.com> wrote in message
> > news:amtq2t45qptulapi2r959ei9f8e9pjtncb_at_4ax.com...
> > > Short of bouncing a database how do I get rid of
> > > temporary segments. I have one, owned by SYS,
> > > that is almost 2 gb in size and will cause problems
> > > if we have a big sort occuring anytime soon.
> > >
> > > Any ideas? Thanks in advance.
> > >
> > >
>
>
Received on Thu Dec 07 2000 - 08:23:47 CST