Free Space available in TEMP Segment

Error : ORA-1652: unable to extend temp segment by 128 in tablespace TEMP



In database there may be permanent and temporary tablespace. The view DBA_FREE_SPACE allows us to show about how much free space in a tablespace have but DBA_FREE_SPACE shows information only about permanent tablespace. It does not show information about temporary tablespace.

v$temp_space_header and v$sort_segment are not supposed to reconcile with each other. The blocks in temp_space_header are the total number of "initialized" blocks in the tempfiles. The blocks in sort_segment are the total number of allocated and usable blocks. Which is evident in following example.

V$TEMP_SPACE_HEADER
ODSPROD1> SELECT TABLESPACE_NAME, FILE_ID, BYTES_USED, BYTES_FREE FROM V$TEMP_SPACE_HEADER;

TABLESPACE_NAME FILE_ID BYTES_USED BYTES_FREE
---------------------------------------- ---------- ---------- ----------
TEMP 3 3145719808 0
TEMP 4 3145719808 0
TEMP 5 3145719808 0
TEMP 6 1073741824 0
TEMP 7 838860800 0
TEMP 1 3145719808 0
TEMP 2 3145719808 0

7 rows selected.

V$SORT_SEGMENT

SELECT tablespace_name,
total_blocks,
used_blocks,
free_blocks,
total_blocks*16/1024 as total_MB,
used_blocks*16/1024 as used_MB,
free_blocks*16/1024 as free_MB
FROM v$sort_segment;

TABLESPACE_NAME TOTAL_BLOCKS USED_BLOCKS FREE_BLOCKS TOTAL_MB USED_MB FREE_MB
---------------------------------------- ------------ ----------- ----------- -------
TEMP 2152576 0 2152576 33634 0 33634


SEGMENT USED:
SELECT b.TABLESPACE,
b.segfile#,
b.segblk#,
b.blocks,
b.blocks*16/1024 as MB,
a.SID,
a.serial#,
a.status
FROM v$session a,
v$sort_usage b
WHERE a.saddr = b.session_addr
ORDER BY b.TABLESPACE,
b.segfile#,
b.segblk#,
b.blocks;

Oracle 9i does not release allocated TEMP segments until you shutdown the database. While the TEMP segment is allocated, it does not mean that it is unavailable for use. When a user requests a disk sort, Oracle will allocate a TEMP segment. Once that sort is done, Oracle releases this TEMP segment for future use, but does not deallocate it. When the next user requests a disk sort, Oracle does not have to allocate a new TEMP segment. It uses the same one that no user is currently using. Oracle manages this for you in 9i. And there is really only one TEMP segment in the TEMP tablespace. Multiple users can utilitize this one segment.

There have been problems in 9i where users running a TEMPORARY TEMP tablespace with TEMPFILES that is Locally Managed where Oracle does not release the sort space once it is no longer being used. This is a known bug. The workaround has been to revert back to Dictionary Managed tablespace for TEMP.