Subject:            Removing `stray` TEMPORARY Segments
Author:            RPOWELL
Modified:           26 Mar 96 12:02:46          



Introduction
~~~~~~~~~~~~
	In some releases of Oracle 7 it is possible to get TEMPORARY segments
	left lying around. This short article explains in summary how to 
	DELETE the segments and free up the space.

Identifying Segments
~~~~~~~~~~~~~~~~~~~~
	TEMPORARY segments are used in a number of operations within Oracle.
	These should always get cleaned up once an operation has completed
	but there are some obscure circumstances where this does not occur.

	To check what temporary segments exist log into Sqldba or 
	Server Manager as a DBA user and:

		SELECT * FROM dba_segments where segment_type='TEMPORARY';

	This will show all TEMPORARY segments. It is perfectly normal to
	see these segments when sorts, index creation etc.. are in progress. 
	It is also perfectly normal for SMON to take a short while to clean
	up TEMP segments. However, if they remain after more than 15 minutes
	then see the rest of this note.

Deleting Stray TEMPORARY Segments
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
	Note: This will *NOT* work in Oracle 7.3.2.1 

	If the TEMPORARY segment does not disappear after the associated 
	operation has completed you can 'post' SMON to clean up the segment 
	thus:

	a) Determine the tablespace holding the segment. The TABLESPACE_NAME
	   column from DBA_SEGMENTS should give you this.

	b) Determine a maximum size extent you can use on the tablespace
	   with the temporary segment in it.

	   Eg: SELECT * FROM dba_free_space 
		WHERE tablespace_name='YOUR_TABLESPACE'
		ORDER BY file_id, block_id;

	   (Note that contiguous extents can be merged)

	c) Run a CREATE TABLE command that will FAIL allocating the second
	   or subsequent extent.
	   Eg: Assume we determined we have a 50Mb free space above and 
		the general free extent size is 5Mb:

		CREATE TABLE RECLAIM ( A number )
		TABLESPACE YOUR_TABLESPACE
		STORAGE (INITIAL 5M NEXT 100M MINEXTENTS 2);

	d) Provided this does NOT fail allocating the initial extent but DOES
	   fail trying to get space after allocating the initial extent
	   this should post the SMON process to cause a cleanup in the
	   named tablespace.

	e) If the RECLAIM table did actually create DROP it and repeat the 
	   command such that it fails.

    	Alternatively you can wait and the segment should eventually be 
	cleaned up by SMON. The frequency of cleanup by SMON depends on the
	Oracle release but can typically be as long as 12 hours.

Additional Notes
~~~~~~~~~~~~~~~~
	Be careful when creating a dummy table that you do not cause excessive
	fragmentation.
	TEMPORARY segments that are actually in use cannot be removed until 
	the owning process either completes or aborts.