Re: Temporary tablespace size

"Joel Garry" <joel-garry_at_home.com> schrieb im Newsbeitrag news:91884734.0308111526.45d7f73_at_posting.google.com...
> rooty_hill2002_at_yahoo.com.au (Gary) wrote in message
news:<171bd226.0308102036.ec5015_at_posting.google.com>...
> > Hi! All,
> >
> > I am Oracle newbie and I am using 9i2 on HP-UX 11i.
> >
> > I am puzzled by the size of the temporary tablespaces at the moment.
> > My Data Warehouse team is building a new data mart at the moment, lots
> > of sorting, I suppose. The database is of about 50G, it takes 3.5G
> > temporary tablespace and it just stays there and never changes, even
> > when after I shutdown the instance and no new session is connected.
> >
> > I thought temporary tablespace is the last thing I need to worry
> > about. I made it locally managed and big enough. Automated segment
> > space management is not available for temporary tablespace as far as I
> > know.
> >
> > Just curious to want to know. I know the fact that sort segment
> > created for each instance is reused and it is only dropped if the
> > tablespace is dropped. Does this have anything to do with it?
>
> It's supposed to be cleaned up on startup. I've seen where you need
> to startup twice to make it clean up. I don't know if this is a
> feature or a bug. Try it. Also see notes on metalink explaining temp
> ts's.
>

AFAIK, the temp TS is cleaned automatically _only_ on startup or rebounce, I have seen it recently on my system (OEE 8.1.7) Otherwise, the TS grows incidently and is then kept by the size once needed, like a High Water Mark.

I am not aware of any other ways to shrink it back down than rebouncing the instance ... is anybody else here who would know ? on the other hand, I have not seen any comments for the need of "reorganizing" temp TSs,
so far it seems not critical. Received on Tue Aug 12 2003 - 01:32:44 CDT