Why can’t I shrink my locally managed tablespace?

Table of Contents

I. Generic Temporary Tablespace considerations

When a large job that uses a temporary tablespace finishes executing, the database doesn’t immediately release the space used by the job in the temporary tablespace, even after the job completes.

The DBA_TEMP_FREE_SPACE view shows the total free space available, including the space currently allocated to a temporary tablespace and available for reuse as well as space that are currently unallocated.

Queries used :

-> TEMP-TSP : Show space usage

SQL> select FILE#, STATUS,  Bytes/1024/1024 Size_MB, tf.name Filename, tsp.name TSP_NAME  
    from v$tempfile tf , v$tablespace tsp where tf.ts# = tsp.ts#;

-> TEMPSEG-Usage: Show SQL used for temp space allocated in v$tempseg_usage:

SQL> SELECT a.username, a.sid, a.serial#, a.osuser, b.tablespace, b.blocks, c.sql_text
      FROM v$session a, v$tempseg_usage b, v$sqlarea WHERE a.saddr = b.session_addr AND c.address= a.sql_address
      AND c.hash_value = a.sql_hash_value ORDER BY b.tablespace, b.blocks;

-> HWM: Shows High-Water-Mark for temp tablesapces

SQL>select sum( u.blocks * blk.block_size)/1024/1024 "MB. in sort segments", (hwm.max * blk.block_size)/1024/1024 "MB. High Water Mark"
    from v$sort_usage u, (select block_size from dba_tablespaces
     where contents = 'TEMPORARY') blk, (select segblk#+blocks max from
     v$sort_usage where segblk# = (select max(segblk#) from v$sort_usage) ) hwm group by hwm.max * blk.block_size/1024/1024;

I. Shrink tablespace doesn’t work as data is not commited yet.

Lets start with a 2 MB temporary tablespace with not sort operation in place.

-> TEMP-TSP :
     FILE# STATUS     SIZE_MB FILENAME                       TSP_NAME
---------- ------- ---------- ------------------------------ ----------
         1 ONLINE   1.9921875 /u01/app/ora112/oradata/H11203 TEMP
                              /temp01.dbf
% ls -l  temp01.dbf
-rw-r-----   1 ora112   asmadmin 2097152 Jun  4 14:50 temp01.dbf

Now Issue a large sort operation and interrupt this query with <ctrl>C. This will make allow us to remain the cursor open untill commit or rollback and let us to check multiple things.Note we don’t run any commit or rollback yet !

+++ TEMP-TSP :
     FILE# STATUS     SIZE_MB FILENAME                       TSP_NAME
---------- ------- ---------- ------------------------------ ----------
         1 ONLINE  109.992188 /u01/app/ora112/oradata/H11203 TEMP
% ls -l  temp01.dbf
-rw-r-----   1 ora112   asmadmin 115343360 Jun  4 15:04 temp01.dbf
--> TEMP TS increased to 115 MByte
SQL> alter tablespace temp shrink space;
+++ TEMP-TSP :
 FILE# STATUS     SIZE_MB FILENAME                       TSP_NAME
-> TEMPSEG-Usage:
 USERNAME          SID    SERIAL# OSUSER          TABLESPACE      BLOCKS SQL_TEXT
 ---------- ---------- ---------- --------------- ------------ --------- --------------------------------------------------
 SCOTT             250       1805 helmut          TEMP             13952 select * from scott.hh_tab order by info1, info2,
-> Now Commit data and free  v$tempseg_usage
 +++ TEMPSEG-Usage:
 no rows selected
 ->  Shrink space should work now
 SQL> alter tablespace temp shrink space;
 Tablespace altered.
 +++ TEMP-TSP :
 FILE# STATUS     SIZE_MB FILENAME                       TSP_NAME
 ---------- ------- ---------- ------------------------------ ----------
 1 ONLINE   1.9921875 /u01/app/ora112/oradata/H11203 TEMP
                      /temp01.dbf
 %  ls -l  temp01.dbf
 -rw-r-----   1 ora112   asmadmin 2097152 Jun  4 16:07 temp01.dbf

As expect the tablspace is resize to 2MByte again

II. Shrink tablespace doesn’t work even we have freed same space but HWM is adjusted yet

If the HWM mark is equal the file size the temp TX can’t be resized !
In this scenario we are using 2 SQL sessions each using 100 Mbyte sort space


USERNAME          SID    SERIAL# OSUSER          TABLESPACE      BLOCKS SQL_TEXT
---------- ---------- ---------- --------------- ------------ --------- --------------------------------------------------
SCOTT             250       1805 helmut          TEMP              13952  select * from scott.hh_tab order by info1, info2, id
SCOTT              19      20671 helmut          TEMP              13952  select  info1, info2, id  from scott.hh_tab order by info1, info2
    
 -> TEMP-TSP :
 TOTAL_KB CURRENT_FREE_KB CURRENT_USED_KB MAX_USED_KB
 ---------- --------------- --------------- -----------
 225272            2040          223232      224256

-> HWM:
 MB. in sort segments MB. High Water Mark
 -------------------- -------------------
 218                 219
 

 Tablespace altered.
-> TEMP-TSP:
  FILE# STATUS     SIZE_MB FILENAME                       TSP_NAME
---------- ------- ---------- ------------------------------ ----------
         1 ONLINE  218.992188 /u01/app/ora112/oradata/H11203 TEMP
-> TEMPSEG-Usage:
USERNAME          SID    SERIAL# OSUSER          TABLESPACE      BLOCKS SQL_TEXT
---------- ---------- ---------- --------------- ------------ ---------- --------------------------------------------------
SCOTT              19      20671 helmut          TEMP             13952 
    select  info1, info2, id  from scott.hh_tab order by info1, info2
-> HWM:
MB. in sort segments MB. High Water Mark
-------------------- -------------------
                 109                 219
-> HWM is not reset , but space is freed after the commit

Note if we have deleted the last transaction first Oracle will release 100 Mbyte sort space !

[color-box color="yellow"]

Summary:

Try to commit/rollback your data as soon as possible to free your temporary space
Check for sessions hanging around a long time and not progressing - kill them if needed
Be aware the even a small sport segment allocated at the end of a temp can avoid to successfully shrink that file. To get an idea what is gong on check the HWM of that file. [/color-box]

Temporary_Space