Re: How to get rid of temporary segments

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

-- 
========================================
Brian Peasland
Raytheons Systems at
  USGS EROS Data Center
These opinions are my own and do not
necessarily reflect the opinions of my 
company!
========================================