My Oracle Support Banner

Temporary segments are not cleaned by SMON (Doc ID 793832.1)

Last updated on NOVEMBER 25, 2019

Applies to:

Oracle Database - Enterprise Edition - Version to [Release 9.2 to 11.1]
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Information in this document applies to any platform.


1- After moving indexes from one tablespace to another using , the "ALTER INDEX OWNER.SEGMENT_NAME REBUILD " command. The indexes are moved successfully from the source tablespace to the target tablespace however the old index segments in the source tablespace are not dropped and their space was not released in the source tablespace. The old index segments are still located in the source tablespace with SEGMENT_NAME='a system generated number' and SEGMENT_TYPE=TEMPORARY
2 - The following is a sample of the output in the source tablespace where the space was not released that clarifies the issue :

SQL> select owner,segment_name,segment_type,tablespace_name from dba_segments where owner='TRADEDB' and segment_type='TEMPORARY'


XXXXXX  41.883913    TEMPORARY


1- Set the source tablespace to be read only

2- "ALTER INDEX OWNER.SEGMENT_NAME REBUILD <source_tablespace> <target_tablespace>" 


To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!

In this Document

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.