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;
Output:
freespace4.png
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;
