Temporary tablespace

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Fri, 29 May 1998 08:56:54 GMT
Message-ID: <01bd8adf$6a494a10$0300a8c0@WORKSTATION>

As you probably know, v7.3.2 introduced the idea of a TABLESPACE with TEMPORARY contents. The idea being that the instance allocated a single segment for sorting/hashing in each temporary tablespace so that the users did not have to create and drop a segment and its extents each time they did a large sort/hash.

The only time segments in TEMPORARY tablespaces are released is when the instance closes down, and the actual segment is cleaned up on the next startup.

Occasionally it is necessary (especially in 24 x 7 OPS sites) to clear down the TEMPORARY tablespaces and the only way I have found to do it in the past is:

	alter tablespace xxx permanent;
	alter tablespace xxx temporary;

except that the second command will not work until the temporary segments have been cleared by smon.

But now - a breakthrough, try:

alter tablespace XXX default storage (......);

(you choose your own default storage clause, and it doesn't have to change the current storage values)

This will cause the instance to flush the temporary segment and clear it down immediately !!!

BTW - the reason for needing to do this in OPS is a bug in the temporary segment code - if one instance needs more space from the tablespace when there is none left unallocated then it should be able to put out a call to request that another instance frees up some space - this doesn't seem to work - hence the need to flush v$sort_segment from time to time.

PS - If you have some 'real' temporary segments in other tablespaces, smon wakes up every 2 hours and five minutes to tidy up - unless something else kicks it (such as a failed create table).

Alter tablespace xxx coalesce
will not clear temp segments, but:

	svrmgrl
	connect internal
	oradebug wakeup {smon pid}
will.
	(smon pid) is the pid from v$process for smon.

--
Jonathan Lewis Received on Fri May 29 1998 - 03:56:54 CDT