Re: Temp Tablespace Question

SG wrote:

> Running 10g RAC (10.1.0.3) on Redhat ES 3.0.
>
> First of all, I'm new to the Oracle world so if I am way off track, please
> correct me.
> My understanding of a temp tablespace and its function is that a temp extent
> is not "deallocated" once allocated for sorting ops and will remained
> allocated for the life of the instance. However, once allocated, they can be
> reused by new sessions. Is that correct? How can one tell what segments are
> available for reuse? I have used "select * from v$sort_usage;" and it
> returns o rows. If an application connects to the database, and is running a
> dynamic sql loop statement, won't that continually expand the temp
> tablespace file size until it reaches the physical storage limit or max size
> configed limit, only to return an error of not being able to extend the
> table? I am needing a little more clarificaton as I am debating an issue
> with the database vs. the app such that the file layout is not incorrect
> with regards to space requirements, but the application will chew up all the
> space no matter what the storage limit is. Of course, shutting down and
> restarting the instance will flush the extents? Please advise on that.
>
> Also, if the connection with the loop sql statement is closed. Won't the
> allocated segments in the temp tablespace be reused if the application
> reconnects and starts the same sql loop statement again? Is there a way to
> check if that is happening or if the subsequent connection is only adding to
> the previously used segments. The main issue is a constantly growing temp
> tablespace file. What is the best way to definitively see what is happening
> with the temp tablespace when the app connects, disconnects, then
> reconnects. Does it reuse segments, does it keep adding/creating/extending?
> Any help is greatly appreciated. TIA.
>
> S G

Actually one pretty much ignores the temp tablespace unless and until there is a specific reason, generally beginning with ORA-, to do otherwise.

The architecture and concepts are all documented at http://tahiti.oracle.com.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)