Introduction:

Temporary tablespaces hold temporary data when we perform join or sorting operations. Sometimes these temp tablespaces become full, which will affect the database performance. And it’s crucial to monitor the capacity to ensure enough space for temporary data to be processed.

This article will explain how to check temporary tablespace usage in Oracle databases using different tools.

What is Temporary Tablespace Usage?

As mentioned above, the temporary data generated while performing sort and join operations are stored in temporary tablespaces. They also get removed automatically when the operations are done.

But due to the heavy load on the databases by performing many operations by more than one user, it becomes full. That’s when we need to check them regularly to see the size and used spaces.

Check Temporary Tablespace Usage using SQL Statement?

We have a dynamic performance view called “V$TEMP_SPACE_HEADER” which is responsible for providing information about the temporary tablespaces.

To check temporary tablespace usage in Oracle databases, we can use the following SQL statement:

Example

SELECT TABLESPACE_NAME
      , (BYTES_USED / 1024 / 1024) AS MB_USED
      , (BYTES_FREE / 1024 / 1024) AS MB_FREE
      , (BYTES_USED / 1024 / 1024) + (BYTES_FREE / 1024 / 1024) AS MB_TOTAL
FROM   V$TEMP_SPACE_HEADER; 

Output:

In this SQL statement, we selected four columns as follows:

• TABLESPACE_NAME: Which holds the tablespace name
• MB_USED: The amount of space is being used in bytes, and we divided by 1024 twice to display the value in megabytes.
• MB_FREE: The amount of space that is being used in bytes, and we also divided by 1024 twice (once to convert bytes to kilobytes, and again to convert kilobytes to megabytes).
• MB_TOTAL: which is the total amount of space in megabytes; it’s basically the summation of the second and third columns.

In this example, we can see that the temporary tablespace named “TEMP” uses 10 MB of space, has 10 MB of free space, and a total of 20 MB of space. This information can be used to monitor the temporary tablespace usage and ensure enough space is available for temporary data processing.

Check This: Article about How to Drop and Recreate Tempfile if you want to manage the tempfile of a temp tablespace

Check Temp Tablespace Usage by Session

We can monitor temporary tablespace usage by a session by executing the following SQL query:

Example:

SELECT VS.SID
      ,VS.SERIAL#
      ,VS.USERNAME
      ,VS.OSUSER
      ,SU.BLOCKS * 8192 / 1024 / 1024 MB_USED
FROM   V$SORT_USAGE SU, V$SESSION VS
WHERE  VS.SADDR = SU.SESSION_ADDR;

Explanation:

In the above query, we have joined two views (V$SORT_USAGE & V$SESSION), the first view, “V$SORT_USAGE” is used to get the information about the temporary segments, and it has the “SESSION_ADDR” column to join with “V$SESSION” to get extra pieces of information about each session.

Check Temp Tablespace Usage by User

We can also monitor temp tablespace usage by each user using our database; just by taking the above query, we can get the information we want:

Example:

SELECT   VS.USERNAME, SUM (SU.BLOCKS * 8192 / 1024 / 1024) MB_USED
FROM     V$SORT_USAGE SU, V$SESSION VS
WHERE    VS.SADDR = SU.SESSION_ADDR
GROUP BY VS.USERNAME;

Explanation:

In the above query, we selected the username column and grouped the result by the same column to get temporary tablespace usage in MB by user “X”.

What happens when the Temp Tablespace is Full?

When the temp tablespace is full, operations requiring temporary space will fail. For example, if a large query requires temporary space to store results, it may fail with an error message indicating that the tablespace is full.

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

This error message indicates that Oracle could not allocate additional temporary space, and the query could not be completed.

What is the Maximum Size of a Temp Tablespace?

The maximum size of a temp tablespace depends on several factors, including the block size of the database and the number of datafiles allocated to the tablespace.

To determine the maximum size of a temp tablespace, we can use the following query:

Example:

-- get the maximum size of the temp tablespace in Megabytes
SELECT SUM (BYTES_USED + BYTES_FREE) / 1024 / 1024 “Max Temp Size (MB)” FROM V$TEMP_SPACE_HEADER;

Output:

When this query is run, it will return the maximum size of the temp tablespace in megabytes.

Conclusion

Monitoring temporary tablespace usage in Oracle is an important task to avoid the failure of queries that indicate the temp tablespace is full. Following the SQL statements explained above in the article, we can quickly identify the temp tablespace usage in different scenarios like user and session.