Re: Should a temporary segment disappear after 125 minutes?

Thanks. The "something obvious" I missed is the words later in the article "Temporary segments in proper TEMPORARY tablespaces (a new feature for v7.3) are supposed to exist for the lifetime of the instance to cut down the number of recursive transactions on the data dictionary."

It's still strange "oradebug wakeup [SMON ID]" doesn't work for either Oracle 8.0.5 (Solaris) or 8.1.7 (NT). (I didn't check it in Oracle 7.3). ALTER TABLESPACE set its PCTINCREASE to 0 works.

BTW, what's the meaning of your word "accidents" in the first paragraph? You probably meant the "ephemeral" temporary segments that exist in permanent tablespace while the index is being created?

Yong Huang
yong321_at_yahoo.com

"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message news:<991942519.3933.0.nnrp-02.9e984b29_at_news.demon.co.uk>...
> The temp segments that smon clears up every 125 minutes
> (although that seems to have change to 120 minutes in 8.1)
> are the accidents, and temporary segments created in
> permanent tablespaces.
>
> The temporary segment you are seeing sounds like the
> typical instance-level temporary segment that is tracked
> by the 'sort extent pool' - If you check a bit further down
> the paper you will find the bit about 'my temp tablespace
> doesn't clear down'.
>
> Bear in mind, by the way, that that paper was written
> for 7.3, so it's getting a bit out of date.
>
> --
> Jonathan Lewis
> Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk
>
> Practical Oracle 8i: Building Efficient Databases
> Publishers: Addison-Wesley
>
> Reviews at: http://www.jlcomp.demon.co.uk/book_rev.html
>
>
>
> Yong Huang wrote in message ...
> >Oracle 8.0.5 Enterprise Ed on Sparc Solaris 2.6
> >
> >In a small test database that just started up, I created an index and
> >left one temporary segment 3.132 in TEMP (which is a
> >dictionary-controlled temporary tablespace with pctincrease 0). I
> >didn't set any event in init.ora or at session level. I thought that
> >after 125 minutes SMON should come in and drop this segment, according
> >to Jonathan Lewis' paper. But it's still here after 3 hours. I just
> >tried oradebug wakeup 6 in svrmgrl, where 6 is the SID of SMON. It
> >didn't get rid of this segment either. Maybe I'm missing something
> >obvious. Thanks for an explanation.
> >
> >I know it'll go away if I bounce the database. I also know leaving it
> >in TEMP is OK. What I'm doing is only for studying how Oracle works.
> >
> >Yong Huang
> >yong321_at_yahoo.com
Received on Fri Jun 08 2001 - 16:18:43 CDT