Saturday, July 15, 2023

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)

No comments:

Post a Comment