Question about persistent connections & temp tablespace
Date: Thu, 20 Mar 2008 08:47:24 -0500
We ran into a temporary tablespace full condition last night. When investigating it this morning, I see a pl/sql developer connection that has been logged in for a month and is using 3 extents in the TEMP tablespace.
I'm curious if long connected sessions (like pl/sql developer, TOAD, etc that are connected for long periods of time and poll the database in the background) that lock extents in the temporary tablespace could make Oracle "think" the temp tablespace is full?
Let say the session grabs 3 extents at the end of the TEMP tablespace tempfile (and autoextend is off), would Oracle be smart enough to use extents for sort operations that occur prior to those 3 extents? Or would it always look for the highwater mark in the tempfile and try to allocate extents beyond that?
Any other reasons not leave sessions like Toad/PlSql developer connected for long periods of time?
Sr. Oracle DBA
Ingram Barge Company
Nashville, TN 37205