Temporary_Space

Why can’t I shrink my locally managed tablespace?

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]

 

 

 

Leave a Reply

Your email address will not be published. Required fields are marked *