Re: How to get rid of temporary segments

Rob Diaz wrote:
>
> It does say TEMPORARY. And like I said, when we bounce the database it
> clears everything out (.008MB used).
>
> Brian Peasland <peasland_at_edcmail.cr.usgs.gov> wrote in message
> news:3A2E4600.234ED151_at_edcmail.cr.usgs.gov...
> > 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!
> > ========================================

The principle behind temp tablespaces is that you do NOT want to clear them out...the first time the tablespace is ever used, oracle has to do all sorts of dictionary mgt as the extents build up...

In the ol' days when you're sort was done, all these were chucked out and the whole (expensive) process would start again...

Temporary tablespaces solved this - rather than wipe the tspace clean, it just keep all the segments/extents for other people to re-use - much more efficient.

Even better from 8i is to use tempfiles for your temp tspaces...even less overhead..

HTH

-- 
===========================================
Connor McDonald
http://www.oracledba.co.uk (mirrored at
http://www.oradba.freeserve.co.uk)

"Some days you're the pigeon, some days you're the statue"