Temporary Segments In Permanent Tablespaces Aren't Cleaned For A Long Time

郑全

Temporary Segments In Permanent Tablespaces Aren't Cleaned For A Long Time (Doc ID 1271120.1)        To BottomTo Bottom       


APPLIES TO:
Oracle Database Exadata Express Cloud Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Database Cloud Exadata Service - Version N/A and later
Oracle Database Backup Service - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Information in this document applies to any platform.

SYMPTOMS
Querying DBA_Segments shows a number of temporary segments in permanent tablespaces that aren't being cleaned up for a long time.

OWNER             SEGMENT_NAME  SEGMENT_TYPE  TABLESPACE_NAME
---------         ------------- ------------- -------------------------------------------
<schema_name>     6.4491        TEMPORARY     TBS_1
<schema_name>     22.3723       TEMPORARY     TBS_2

Checking V$sessions doesn't show any sessions for the owner of the segments:

SQL> select pid from v$process where addr in (select paddr from v$session where username='<schema_name>');

no rows selected

Checking V$session_longops doesn't show any related entries:

SQL> Select to_char(start_time,'dd-mm hh24:mi') start_time, to_char(sysdate, 'dd-mm hh24:mi') cur_time, username from V$session_longops;

no rows selected

Checking if SMON is disabled for cleanup of temporary segments:

SQL> show parameter events

-- If event 10061 is set at level 10, it implies that cleanup of temp segments by SMON is disabled
-- and 1st a check needs to be done if after disabling the event the segments are cleaned up:

SQL> alter system set events '10061 trace name context off';

Forcing SMON to clean up the temporary segments in the tablespace like below does not work:

SQL> ALTER TABLESPACE TBS_1 coalesce;

Bouncing the database does not clean up these segments.

CAUSE
This might be caused by having the permanent tablespaces in read-only mode before the temporary segments were cleaned up.
This causes SMON to not be able to clean them afterwards even when forcing it to using 'alter tablespace ... coalesce'.

Querying DBA_tablespaces shows that the tablespaces are in read-only mode.

SQL> Select tablespace_name, status from dba_tablespaces;

TABLESPACE_NAME                STATUS
------------------------------ ---------
TBS_1                       READ ONLY
TBS_2                       READ ONLY
SOLUTION
Change the tablespace to read-write mode. Then force SMON to clean-up the temporary segments. If needed put the tablespace back in read-only mode.

1. Put one of the table spaces in read-write mode.

SQL> alter tablespace TBS_1 read write;

2. Force SMON to clean up the temporary segments.

SQL> alter tablespace TBS_1 coalesce;

3. Put the table space back in read-only mode.

SQL> alter tablespace TBS_1 read only;


REFERENCES
NOTE:102339.1 - Temporary Segments: What Happens When a Sort Occurs
NOTE:106717.1 - How To Determine The Cause Of a Large TEMPORARY Segment In PERMANENT Tablespace
NOTE:177334.1 - Overview of Temporary Segments