How to find use size and free size of the tablespace in Oracle


In this article, we will discuss how to find use size and free size of the tablespace in Oracle. We will use dba_data_files use for use size and dba_free_space use for free size. The tablespace is divided into one or more logical storage units within a database called Tablespace. The tablespace is a logical storage unit. The tablespace is logical because a tablespace is not visible in the file system of the machine on which the database resides.

Use size of the tablespace:

SQL> Desc dba_data_files;

SQL> Select * from dba_data_files;

SQL> select tablespace_name, ‘Mb’||’ ‘||round(sum(bytes/1024/1024)) “Used_Size”
from dba_data_files
group by tablespace_name;

Free size of the tablespace:

SQL> Desc dba_free_space;

SQL> Select * from dba_free_space;



SQL> Select tablespace_name, ‘Mb’||’ ‘||round(sum(bytes/1024/1024)) “Free_Size”
from dba_free_space
group by tablespace_name;

Finding free and use temporary table space in Oracle:

The usage temporary tablespace can’t be found out exactly using DBA_FREE_SPACE. To find out the true value of temporary table space we may need to use V$TEMP_SPACE_HEADER data dictionary.

SQL> SELECT tablespace_name, SUM (bytes_used), SUM (bytes_free)
FROM v$temp_space_header
GROUP BY tablespace_name;