Question about persistent connections & temp tablespace

From: Taylor, Chris David <>
Date: Thu, 20 Mar 2008 08:47:24 -0500
Message-ID: <>

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?

For example:
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?


Chris Taylor
Sr. Oracle DBA
Ingram Barge Company
Nashville, TN 37205
Office: 615-517-3355
Cell: 615-354-4799

Received on Thu Mar 20 2008 - 08:47:24 CDT

Original text of this message