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

Last updated on DECEMBER 06, 2016

Applies to:

Oracle Server - Enterprise Edition - Version: 9.2.0.1 to 11.1.0.7 - Release: 9.2 to 11.1
Information in this document applies to any platform.

Symptoms

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'

 OWNER  SEGMENT_NAME  SEGMENT_TYPE TABLESPACE_NAME

XXXXXX  41.883913    TEMPORARY

Changes

1- Set the source tablespace to be read only

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

Cause

Sign In with your My Oracle Support account

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

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms