Oracle database uses temporary tablespaces to improve the concurrency of multiple sort operations that do not fit in memory and can improve the efficiency of space management operations during sorts. Data created within this tablespace is transient and lasts only for the duration of the session/statment. It can store the following data:

• Intermediate sort results
• Temporary tables and temporary indexes
• Temporary LOBs
• Temporary B-trees

After the database startup, A sort segment is created by the first statement that uses the temporary tablespace for sorting, and is released only at shutdown. By default, a single temporary tablespace named TEMP is created for each new Oracle Database installation. Multiple temporary tablespaces can be created, but only one can be assinged to a user at a given time. A single temporary tablespace can be shared by multiple users.

Quite often we need to check the usage of temprorary tablespace especially when creating an index on a large table — Oracle needs to do sorting based on the value of the column being indexed. If there is no enough temprorary space for sorting, index creation will fail with the following error:

ORA-01652: unable to extend temp segment by 128 in tablespace TEMP

When monitoring the usage of temporary tablespace, which views to use to give you the current acurate values? DBA_TEMP_FILES, DBA_TEMP_FREE_SPACE, V$TEMP_SPACE_HEADER, V$TEMPSEG_USAGE (or V$SORT_USAGE), V$SORT_SEGMENT?

Well, all of them are useful, but one view you have to pay attention to is V$TEMP_SPACE_HEADER. This view has columns called BYTES_FREE, BYTES_USED, BLOCK_FREE, BLOCKS_USED.

BYTES_FREE (BLOCK_FREE) only indicates how much space never been used after a temp data file is added. It doesn’t really tell how much free space a temp data file has and can be used at the moment. It’s kind of like a high water mark. The view V$TEMP_SPACE_HEADER really just shows that these many blocks were touched in each temp file at a certain point when temp usage was at its highest. It is persistents across restarts.

On the contrary, The FREE_SPACE from DBA_TEMP_FREE_SPACE will tell you the real free space at the moment for the whole temporary tablespace.

Here is a simple test you can see the difference:

1. Add a new temp data file and see what V$TEMP_SPACE_HEADER has:

SQL> select d.TABLESPACE_NAME, v.file_id, d.file_name,d.bytes/1024/1024/1024 sz,d.status, 
BYTES_USED/1024/1024/1024 used_GB, BYTES_FREE/1024/1024/1024 free_GB
from dba_temp_files d, V$TEMP_SPACE_HEADER v
where d.file_id=v.file_id(+)
order by v.file_id, d.file_name;

TABLESPACE_NAME   FILE_ID FILE_NAME                               SZ STATUS     USED_GB    FREE_GB
---------------- -------- ------------------------------------------ ------- ---------- --------
--
TEMP                    1 /dbase/system/PROD/temp1.dbf    31.9990234  ONLINE  31.9990234          0
TEMP                    2 /dbase/system/PROD/temp2.dbf            12  ONLINE  .000976563 11.9990234

As you can see USED_GB (USED_BYTES) is almost 0 and FREE_GB (FREE_BYTES) is about 12G — the size of the temp data file.

2. Now try to create a large index (bigger than 32G), query again and you will see USED_GB going up and FREE_GB going down. Also check FREE_SPACE from DBA_TEMP_FREE_SPACE, you will see FREE_SPACE is going down as well.

select t.*, (FREE_SPACE/TABLESPACE_SIZE)*100 "percent free" from DBA_TEMP_FREE_SPACE t;

Once the index is created, you will see the value of DBA_TEMP_FREE_SPACE.FREE_SPACE increases because SORT is done and temp space is reusable. However, you will see the value V$TEMP_SPACE_HEADER.BYTES_FREE stays there.

3. Again try to create a huge index which could exhaust the temp tablespace. You will see V$TEMP_SPACE_HEADER.BYTES_FREE goes down to zero. It is the same for DBA_TEMP_FREE_SPACE.FREE_SPACE. However, after the index creation fails, DBA_TEMP_FREE_SPACE.FREE_SPACE set back, but V$TEMP_SPACE_HEADER.BYTES_FREE stays zero. Actually it will persist across the restart of database.

4. Restart the database, you will V$TEMP_SPACE_HEADER.BYTES_FREE doesn’t change after the bounce.

So DBA_TEMP_FREE_SPACE gives overall usage at the a certain point, but if you want to see more granular information, use V$SORT_SEGMENT or V$TEMPSEG_USAGE(or V$SORT_USAGE) — allocation of sort segments.

Some useful queries to see TEMP tablespace usage:

select u.username, s.sql_fulltext, u.segtype, u.extents, u.blocks from v$tempseg_usage u, v$sql s where s.sql_id in (u.sql_id, u.SQL_ID_TEMPSEG);

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 c
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;

select (s.tot_used_blocks/f.total_blocks)*100 as "percent used"
from 
(select sum(used_blocks) tot_used_blocksfrom v$sort_segment where tablespace_name='TEMP') s, 
(select sum(blocks) total_blocks from dba_temp_files where tablespace_name='TEMP') f;

SELECT b.tablespace, b.segfile#, b.segblk#, b.blocks, a.sid, a.serial#, a.machine, a.module, a.username, a.osuser, a.status
FROM v$session a,v$sort_usage b
WHERE a.saddr = b.session_addr
ORDER BY b.tablespace, b.segfile#, b.segblk#, b.blocks;

It is worth mentioning that V$TEMPSEG_USAGE has SQL_ID and SQL_ID_TEMPSEG. When a session is requesting Oracle to create temp segments for sorting, it is the value from SQL_ID_TEMPSEG will bring you to the correct SQL statement in V$SQL.

References:

• VIEW: “V$SORT_USAGE” Reference Note (Doc ID 67534.1)
• VIEW: “V$TEMP_SPACE_HEADER” Reference Note (Doc ID 180940.1)
• Queries To Monitor Temporary Tablespace Usage (Doc ID 289894.1)
• How Do You Find Who And What SQL Is Using Temp Segments (Doc ID 317441.1)
• How Can Temporary Segment Usage Be Monitored Over Time? (Doc ID 364417.1)
• Mismatch Between V$TEMP_SPACE_HEADER and V$TEMPSEG_USAGE/V$SORT_USAGE (Doc ID 2095211.1)
• How to Find Creator of a SORT or TEMPORARY SEGMENT or Users Performing Sorts (Doc ID 1069041.6)

• Oracle