Force cleanup of temporary segments through DROP_SEGMENT event in Oracle database

We recently had scenario where  temp segments were not getting released  even though  no  active sessions were there  and  "alter tablespace temp  coalesce;"   was not helping  .   

This needs to be done only after oracle support  recommendation .and this  feature  surprisingly exists from Oracle version  8 . 

This routine does what SMON does in the background, i.e. drops temporary segments.

Before force dropping temp segment try to gracefully clean . Sometimes temp can be resized after restart of database 

 select sum(blocks*8/1024) size_mb from v$tempseg_usage where tablespace='TEMP';

Shrink TEMP Tablespace using alter tablespace command

SQL> ALTER TABLESPACE temp SHRINK SPACE KEEP 50M;.

Shrink TEMPFILE using alter tablespace command

SQL> ALTER TABLESPACE temp SHRINK TEMPFILE '/u01/app/oracle/oradata/TEST11G/temp01.dbf' KEEP 40M;

Shrink TEMP Tablespace to the smallest possible size:

SQL> ALTER TABLESPACE temp SHRINK SPACE;

DESCRIPTION : 

Finds all the temporary segments in a tablespace which are not currently locked and drops them.

PARAMETER : Level parameter used in command 

level - tablespace number+1. If the value is 2147483647 then

temp segments in ALL tablespaces are dropped, otherwise, only

segments in a tablespace whose number is equal to the LEVEL

specification are dropped.

Manually release the temporary segment, the utilization rate will not decrease, but the content of the temporary tablespace will be released

alter session set events 'immediate trace name DROP_SEGMENTS level 4'

Description: TS# of temp tablespace is 3, So TS#+1=4

View temp content details

select se.inst_id,se.username,se.sid,su.extents,su.blocks*to_number(rtrim(p.value)) as Space, tablespace,segtype,sql_text from gv$sort_usage su,gv$parameter p,gv $session se,gv$sql s

where p.name='db_block_size' and su.session_addr=se.saddr and s.hash_value=su.sqlhash and s.address=su.sqladdr order by

se.username,se.sid;

If the query finds that most of the temp temporary table space is lob temporary fields, you can set the 60025 event

alter system set event="60025 trace name context forever" scope=spfile;

Restart to take effect

References : 

EVENT: DROP_SEGMENTS - Forcing cleanup of TEMPORARY segments (Doc ID 47400.1)

Note 177334.1 Overview of Temporary Segments 

Note 35513.1 Removing 'Stray' TEMPORARY Segments

Note 61997.1 SMON - Temporary Segment Cleanup and Free Space Coalescing

NOTE:160426.1 - TEMPORARY Tablespaces : Tempfiles or Datafiles ?

Note 102339.1 Temporary Segments: What Happens When a Sort Occurs 

Note 1039341.6 Temporary Segments Are Not Being De-Allocated After a Sort 

Note 68836.1 How To Efficiently Drop (or Truncate) A Table With Many Extents

Note 132913.1 How To Free Temporary Segment in Temporary Tablespace Dynamically

Resize Of Temporary Tablespace with KEEP keyword not working (Doc ID 1663229.1)