My Oracle Support Banner

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

Last updated on AUGUST 04, 2018

Applies to:

Oracle Database - Enterprise Edition - Version and later
Information in this document applies to any platform.


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

--------- ------------- ------------- -------------------------------------------
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:


Bouncing the database does not clean up these segments.


To view full details, 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 a vibrant support community of peers and Oracle experts.