Check free space in Temp tablespace

Here is a commonly used query to check the free space in a temporary tablespace:

Query 1: Using DBA_TEMP_FREE_SPACE

This view provides information about the free space in the temporary tablespaces.

SELECT
    tablespace_name,
    SUM(bytes)/1024/1024 AS free_space_MB
FROM
    dba_temp_free_space
GROUP BY
    tablespace_name;

• This query will return the available free space in each temporary tablespace in MB.
• The bytes are divided by 1024*1024 to convert from bytes to megabytes.

Query 2: Using V$TEMP_SPACE_HEADER

You can also use V$TEMP_SPACE_HEADER, which provides information about the temporary tablespace files and their usage.

SELECT
    tablespace_name,
    file_id,
    SUM(bytes)/1024/1024 AS free_space_MB
FROM
    v$temp_space_header
GROUP BY
    tablespace_name, file_id;

This query will give you a detailed breakdown of free space available in each temporary file associated with the temporary tablespaces.

Query 3: Checking Free Space Using DBA_DATA_FILES

This query checks the free space in the temporary tablespace by querying the DBA_TEMP_FILES and DBA_DATA_FILES views:

SELECT
    t.tablespace_name,
    f.file_name,
    f.bytes / 1024 / 1024 AS total_size_MB,
    (f.bytes - NVL(s.bytes, 0)) / 1024 / 1024 AS free_space_MB
FROM
    dba_temp_files f
LEFT JOIN
    (SELECT file_id, SUM(bytes) AS bytes
     FROM v$temp_space_header
     GROUP BY file_id) s
    ON f.file_id = s.file_id
WHERE
    t.tablespace_name = f.tablespace_name;

This query joins DBA_TEMP_FILES and V$TEMP_SPACE_HEADER to show the total space and the free space (in MB) for each file in the temporary tablespace.

These queries can help you track the available free space in your Oracle database’s temporary tablespace(s). If you need more granular details (such as how much space is actually being used), you can expand the query to get more detailed statistics from dynamic views like V$TEMP_FILE or V$SORT_SEGMENT.