Re: Are temp segments being freed automacically or not ?

Mark.Powell_at_eds.com (Mark D Powell) wrote in message news:<2687bb95.0307241208.4320b858_at_posting.google.com>...
> Actually, I believe that if the temp tablespace is created as "create
> temporary tablespace" or "create tablespace temp temporary" the temp
> SEGMENT once allocated to the instance will not be freed until the
> instance is shut down. EXTENTS within the temp segment will however
> be made available for reuse by other sessions as the using session
> sort steps end. You can view the temp segment created to support
> either of the above two commands in v$sort_segment and the using
> sessions in v$sort_usage. These views are not populated for temp
> tablespaces created as "create tablespace temp [permanent]" in which
> case multiple segments are allocated and freed.
>
> HTH -- Mark D Powell --

My observation complies with Mark's statement here. It is NEVER de-allocated until shutdown. Thats why sometimes shutdown takes very long while there is nothing to rollback.

Oracle de-allocates by # of extents, regardless of the size. The smaller each extent is ( thus the more extents ) the longer it takes to de-allocate. I used to manage a database with 14 GB of temp space which can be completely allocated at shutdown time and it will take ages to go down. Eventually we sized each extent with 50 MB (from 1 MB). Although we wound up having to give it more space, but shutdown becomes much faster.

BTW, if you use shutdown abort, it will de-allocate when it comes up. So it does not save you any time. Received on Fri Jul 25 2003 - 19:12:11 CDT