Re: Temporary tablespace size

"Jan Gelbrich" <j_gelbrich_at_westfalen-blatt.de> wrote in message news:<bha1qd$v0hkg$1_at_ID-152732.news.uni-berlin.de>...
> "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.

Note:47400.1 didn't work for me, but it is interesting. Note:61997.1 suggests to me that interrupting a sort or create index might leave stuff around.

jg

--
@home.com is bogus.
Give Barney a big hug!