Temp Tablespace Matters in Oracle

 When managing Oracle temporary tablespaces, it's important to prioritize timely data commitment or rollback to release temporary space efficiently. 

Additionally, keep an eye out for sessions that remain active without progressing for an extended period; terminating these sessions may be necessary to optimize resource usage. Another crucial consideration is the allocation of small sport segments at the end of a temporary file, which can hinder successful file shrinking. To gain insights into this issue, check the High Water Mark (HWM) of the file to understand its allocation status and potential space usage implications. These practices help maintain the performance and manageability of Oracle temporary tablespaces effectively.

Following are some queries to manage these Temp tablespaces efficiently:

Use this query to get Temp tablespace usage:

SQL> select FILE#, STATUS,  Bytes/1024/1024 Size, tf.name Filename, ts.name TS_NAME  

    from v$tempfile tf , v$tablespace ts where tf.ts# = ts.ts#;

Use this query to get allocated temp space:

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;

  

Use this query to get high water mark of Temp Tablespaces:

SQL>select sum( u.blocks * blk.block_size)/1024/1024 "MB. in sort segments", (hwm.max * blk.block_size)/1024/1024 "HWM"

    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;   

 

Use this query to shrink the Temp Tablespaces:

SQL> alter tablespace temp shrink space;