Free UP Space in Temp Tablespace without shutting the instance
Vivek Sharma, April 17, 2003 - 9:52 pm UTC
Dear Tom,
I have a Temporary Tablespace which is of type Temporary. To free up the space from this tablespace, I follow this simple steps and notice that the free space shoots up. I change the pctincrease of the tablespace to 1 and then issue coalesce command 2-3 times and again change the pctincrease to 0.
This knowledge was shared to me by one of my fellow DBA but I am unaware what oracle does internally to free up the space.
Can u please share ur knowledge on this with some example and also please let me know whether the steps are correct. Is there any performance issues. I do it normally in the night when no one is connected to the database. Can it be done in the day time when there are maximum users ?
Thanks in Advance.
Vivek Sharma
April 18, 2003 - 11:08 am UTC
you have a PERMANENT tablespace whose type is TEMPORARY.
What you are doing is SELF DEFEATING, you have not "freed up" anything. what you have done is "undone" the expensive work of allocating the extents. Now you just have to allocate them all over again!
You see, a temp segment is made up of many extents. In order to remove the large overhead of allocate/free/allocate/free/allocate/free extents -- we allocate the extent ONCE and keep it. We then manage the extents in v$sort_usage/v$sort_segment. The next time someone needs temp space, instead of allocating an extent -- we simply find one we already allocated and give it to them.
What you have done effectively and brutally defeats this optimization.
What you SHOULD do is
a) use true temporary tablespaces. They will be locally managed, with uniform extent sizes
b) stop doing the self defeating thing you are doing now. It is just wasting cycles on your machine, making things take longer in the morning, and providing nothing beneficial in return!
alter the temporary tablespace to be permanent
A reader, May 26, 2003 - 4:06 am UTC
<quote>
If you really truely want to "free" the space (although the space is in fact *free*, others will use it), you can alter the temporary tablespace to be permanent and then alter it right back to temporary. you'll see it shrink in allocated space right them
</quote>
just curious,..
how to convert the temporary tablepsace to permanent and then back to temporary. i tried but i could not.
ge$kumar@devadw.world>alter tablespace TEMP PERMANENT;
alter tablespace TEMP PERMANENT
*
ERROR at line 1:
ORA-03217: invalid option for alter of TEMPORARY TABLESPACE
May 26, 2003 - 9:40 am UTC
that works only for temporary tablespaces that can be permanent.
eg: if you use create tablespace temp .... temporary; -- you can alter that to be permanent.
if you use create TEMPORARY TABLESPACE, you cannot.
Bill, January 06, 2006 - 3:00 pm UTC
Hi Tom:
My database has a true TEMPORARY TABLESPACE 'TEMP' (locally managed, tempfile-based, TEMPORARY TYPE). Under OEM 10g R1, the usage of 'TEMP' keeps increasing with time (83%+). Under the latest version of TOAD (v8.6), it reports that the TEMP tablespace has 0% usage.
If I understand correctly, the high usage figure in OEM does not mean the actual usage as it is internally managed.
Sometimes, we get this ORA-1652 unable to extend temp segment by 128 in tablespace 'TEMP' on one occasion and same error on other tablespaces that have <80% usages.
My question:
How do I really know the actually usage in TEMP if the reported figure does not represent the true usage?
Thanks,
January 06, 2006 - 4:47 pm UTC
that shows how much of temp is allocated as a segment.
v$sort_usage shows how much is currently being used by an active "sort" or "temp" operation.
A reader, February 02, 2006 - 5:58 am UTC
good
Space within the space
Ron, August 15, 2006 - 5:12 pm UTC
OK, I understand that space isn't deallocated from a true temporary tablespace. But within the allocated temporary space, when are the extents marked available for use by other sessions? We have a situation where sessions are accumulating temporary extents indefinitely (as indicated by V$SORT_USAGE and V$TEMPORARY_LOBS.) The offending sessions don't appear to release their temporary extents as "available" for other sessions to use, until they are terminated.
I may have just re-phrased the original question in this thread. Hopefully, I've left less room for misinterpretation. Thank you!
August 15, 2006 - 6:08 pm UTC
when the session that used the extent is done with it.
eg: say you opened 'select * from all_objects order by 1, 2, 3, 4, 5, 6, 7, 8, 9, 10'
while that cursor is open, you'll have your temp extents.
close it, and they'll go away.
if you never closed it, like never closing a file in the OS, you'll own them.