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)
