On Oracle 8.1.7 on AIX4.3.3 the temporary tablespace has a fair amount of allocated space
in it that is not released. Here is some basic info. to help.

select owner,segment_name,tablespace_name,segment_type
from sys.dba_segments
where tablespace_name = 'TEMP';

owner segment_name tablespace_name segment_type
----- ------------ --------------- ------------
SYS 3.16962 TEMP TEMPORARY



select TABLESPACE_NAME, CONTENTS
from DBA_TABLESPACES
where TABLESPACE_NAME = 'TEMP';

TABLESPACE_NAME CONTENTS
------------------------------ ---------
TEMP TEMPORARY



select TABLESPACE_NAME,TOTAL_BLOCKS,USED_BLOCKS,FREE_BLOC KS
from v$sort_segment;

TABLESPACE_NAME TOTAL_BLOC USED_BLOCK FREE_BLOCK
------------------------------- ---------- ---------- ----------
TEMP 23660 0 23660



An article on MetaLink says this is "normal behavior" for v7.3 and above but I have not
noticed this in other DBs and am looking for a 2nd. opinion and a better explanation. Please
share your thoughts.

The same posting on MetaLink states:
"Default Storage specification for your TEMPORARY tablespace:
Use the following guidelines to specify DEFAULT STORAGE:
Set INITIAL=NEXT.Since a process always writes data equal to
SORT_AREA_SIZE to a temporary segment, a good value for the extent
size is (n*s + b)
where: n is a positive integer
s is the value of SORT_AREA_SIZE initialization parameter
b is the value of DB_BLOCK_SIZE initialization parameter
Using this value optimizes temporary segment usage by allowing
sufficient space for a header block and multiple sort run data to be
stored in each extent."

If you use n=2 have DB_BLOCK_SIZE = 16384 and SORT_AREA_SIZE = 2097152 you get something
like this:

(n*s + b)
(2 * 16384 + 2097152) = 2129920
2129920 / 1024 / 1024 = 2.03125 M or 2080 K

This seems like a rather strange number. Any thoughts, comments, suggestions?