RE: Temporary segments in temporary tablespaces
- From: "Kenneth Naim" <kennethnaim@xxxxxxxxx>
- To: <lyallbarbour@xxxxxxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 13 Aug 2010 10:00:33 -0400
Or make the code more efficient.
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of lyallbarbour@xxxxxxxxxxxxxxx
Sent: Friday, August 13, 2010 9:55 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: Re: Temporary segments in temporary tablespaces
From what i understand about Temp Tablespaces, the segments will fill up the
tablespace until it's full. The status of those segments will change and
when new sorting needs to happen with the full tablespace, it will overwrite
the old segments that have non-active status.
If your temp tablespace is getting the unable to extend error, it's because
you ahve one query that's sorting so much, it's filling up the whole
tablespace all at one time and then some.
I'm sure someone will correct me if i'm wrong, but if you are getting unable
to extend, then you have to make your temp tablespace bigger, period.
Lyall
-----Original Message-----
From: Dan Peacock <danp@xxxxxxxxxxxxx>
To: oracle-l@xxxxxxxxxxxxx
Sent: Fri, Aug 13, 2010 8:31 am
Subject: Temporary segments in temporary tablespaces
Good Morning List!
I've been working on a "problem" with my developers that I'm stumped by and
I'm hoping the collective can shed some light on it for me. We have an
application that comes in through some OCI libraries and opens a connection
but doesn't close it as the overhead of making the connection is too steep
for the scanner application that uses the library. The consequence of this
is, as time wears on it seems that the temporary sort segments accumulate
for the connected user to the point where there's nothing left for "real"
work and applications start crashing with "unable to extend TEMP" (other
applications use these same libraries and can run some pretty hefty
queries). My understanding was that SMON would come through and clean up
temporary segments that were no longer needed but we did some testing and
don't think this is the case for an open connection.
Here's what we did:
1) We have a SQL statement that we know puts stuff out into the TEMP
tablespace due to sorting. Approximately 24mb of sort space is used by this
statement.
2) We ran the statement, issued a commit to start a new "transaction".
3) Ran the statement again and are now consuming 48mb of TEMP space.
4) We waited for over an hour and the amount of space never went down.
5) Thinking it was just allocated to us and would be reused we tried it
again and are now sitting at 72mb of TEMP space
So, my question is, short of closing and reopening the connection, what can
we do to force these segments to release and get reused. We've already
resized the PGA and that helped some, but it only delays the inevitable.
Any insights would be greatly appreciated.
Dan Peacock
Auto-wares, Inc.
Secretary of the West Michigan Oracle Users Group
--
//www.freelists.org/webpage/oracle-l
Other related posts:
