Free space in Temporary Tablespace

In database there may be permanent and temporary tablespace. The view DBA_FREE_SPACE allows us to show about how much free space in a tablespace have but DBA_FREE_SPACE shows information only about permanent tablespace. It does not show information about temporary tablespace.

In order to know about temporary tablespace free space usage we have to query V$TEMP_SPACE_HEADER view. With an example it is described.

1)Create one temporary and Permanent Tablespace.
I used OMF file system. To know about it search about OMF in my blog.

SQL> ALTER SYSTEM SET db_create_file_dest='/oradata2';
System altered.

SQL> CREATE TABLESPACE PERMANENT_T;
Tablespace created.

SQL> CREATE TEMPORARY TABLESPACE TEMP_T;
Tablespace created.

2)Query DBA_FREE_SPACE and we will see PERMANENT_T is listed but TEMP_T is not listed.
SQL> SELECT DISTINCT TABLESPACE_NAME FROM DBA_FREE_SPACE;

TABLESPACE_NAME
------------------------------
UNDOTBS1
SYSAUX
USERS
DATA
SYSTEM
PERMANENT_T

6 rows selected.

3)To know information about free space of temporary tablespace query V$TEMP_SPACE_HEADER.

SQL> SELECT TABLESPACE_NAME, FILE_ID, BYTES_USED, BYTES_FREE FROM V$TEMP_SPACE_HEADER;

TABLESPACE_NAME FILE_ID BYTES_USED BYTES_FREE
------------------------------ ---------- ---------- ----------
TEMP 1 6291456 14680064
TEMP_T 2 1048576 103809024