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

Last updated on DECEMBER 06, 2016

Applies to:

Oracle Database - Enterprise Edition - Version 10.2.0.4 and later
Information in this document applies to any platform.
*** Checked for relevance on 15-OCT-2014 ***

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
--------- ------------- ------------- -------------------------------------------
SICHERUNG 6.4491        TEMPORARY     DATA4704
SICHERUNG 22.3723       TEMPORARY     DATA4702


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='SICHERUNG');

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 DATA4704 coalesce;


Bouncing the database does not clean up these segments.

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