Kirti,
Yes Sir, you are no the money, my temporary tablespaces were of
type "TEMPORARY", which is what I was referring to all the while
by the term "true temporary tablespaces". In situations where
the temporary tablespace is of type "PERMANENT", then yes I
totally agree with your and Chris's observations.
My point with the discussion of "true temporary tablespaces", is
that extent allocation is done on an as needed basis based as
required by the sorting process. The sort segment just keeps on
growing subject to the size of the temporary tablespace. Extent
deallocation of the sort segment (which is 1 per true temporary
tablespace) is done on instance shutdown. That is how the "sort
extent pool" algorithm works. This fact can very easily
verified by viewing v$sort_segment and v$sort_usage.
Cheers,
Gaja.
- "Deshpande, Kirti" <Kirti.Deshpande_at_gtedc.gte.com> wrote:
> Hey Gaja,
> Was the temporary tablespace of 'TERMPORARY' type in all your
> databases?
> Then, what you say (about temp segments dropped at startup)
> makes sense. I
> have had to 'wake up' SMON to reclaim temp segments a few
> times and it did
> work. However, the tablespace was not of 'TEMPORARY' type. I
> used the
> oredebug command to wake up SMON not what the article from
> OraMag showed.
> IMO oradebug method is the fastest. The little 'trick' from
> the OraMag
> article has to do a DML to the DD table, whereas oradebug is
> just a command.
>
> - Kirti
>
> > -----Original Message-----
> > From: Gaja Krishna Vaidyanatha [SMTP:gajav_at_yahoo.com]
> > Sent: Monday, June 19, 2000 6:26 PM
> > To: Multiple recipients of list ORACLE-L
> > Subject: RE: Locally Managed Tablespace Survey
> >
> > Chris,
> >
> > Thanks for the link and I did check it out. It talks about
> how
> > to "force" SMON to clean up a temp segment left over by an
> > aborted transaction, by faking a change to the TEMP
> tablespace's
> > default storage clause. It also talks about how SMON will
> drop
> > the temp segment on wake up after 2 hours.
> >
> > My point is that, the temp segment really does not get
> dropped
> > after 2 hours even after SMON wakes up, at least I have not
> seen
> > it do that. I have gone days waiting for this to occur, but
> in
> > vain.
> >
> > In everything that I have seen and tested, the temp segment
> gets
> > dropped only on instance shutdown. Again, my tested
> versions
> > were 8.0.5.X on various platforms. If you do find evidence
> that
> > does suggest that the temp segment does gets dropped, please
> be
> > kind enough to drop a line, as I would like to learn the
> > "circumstances" under which that occurred. I will be one
> heck
> > surprised if that in fact occurs, as it will go directly
> against
> > some of the "fundamental rationale" for "true temporary"
> > tablespaces. Thank you for taking the time to research this
> and
> > sharing the knowledge.
> >
> > Best Regards,
> >
> > Gaja.
> >
> >
> > --- "Gait, Christopher" <cgait_at_condor.nrl.navy.mil> wrote:
> > > I've been trying to track down the source I got this from,
> but
> > > not
> > > succeeded. There is a good hint on cleaning up the temp
> > > segments here:
> > >
> > > http://www.oracle.com/oramag/code/tips2000/Tip18.html
> > >
> > > The article mentions 'more than two hours'in connection
> with
> > > SMON waking up
> > > to clean out the temp segments, but not the specific '25
> > > cycles' I have
> > > heard of. The figure of 125 minutes is 25 standard 5
> minute
> > > cycles for SMON,
> > > and is supposedly the time between normal wakeups for temp
> > > cleanup.
> > >
> > > Regards,
> > > Christopher Gait
> > > Oracle DBA
> > >
> > > (Semi)permanent address: cjgait_at_yahoo.com
> > > http://www.geocities.com/athens/3623
> > >
> > > "Death is nature's way of saying: NEXT!"
> > >
> >
Gaja Krishna Vaidyanatha | gajav_at_yahoo.com
Brio Technology | (972)-304-1170
"Opinions and views expressed are my own and not of Brio Technology"
Received on Tue Jun 20 2000 - 10:28:30 CDT
