Temp Tablespace Question

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 Received on Fri Mar 04 2005 - 12:16:23 CST