Know the free space in Oracle DB

Hello everyone.
One of the most important tasks we face when we manage an Oracle database is to have controlled the growth of the different components that make it up. That is why we will see how to know the free space in Oracle.

know the free space in Oracle

Monitoring applications warn us when the files are being filled. They are very useful tools to perform the administration efficiently.

It may also be advisable to do this follow-up by means of querys directly on the same database.

View space by tablespaces

Database objects such as tables, indexes, etc. are stored in tablespaces. Once an object is created and stored in the tablespace, as data is entered into that object, it is the Oracle engine that is responsible for automatically allocating the space within the tablespace.

For this reason, it is very interesting to be able to monitor the free space in Oracle databases at the tablespace level, and we have a query that comes in handy to perform this work.

select df.tablespace_name nb_tablespace,
 round(sum(df.bytes) / 1024 / 1024) espacio_total_MB,
 round(free.bytes/ / 1024 / 1024) espacio_libre_MB,
 round(100 * (free.bytes / round(sum(df.bytes))),2) Porcentaje_Libre
From dba_data_files df
 join (select tablespace_name, sum(bytes) bytes 
 From dba_free_space 
 group by tablespace_name) free on df.tablespace_name=free.tablespace_name
group by df.tablespace_name, free.bytes

This query returns the tablespaces, with the size of the same, as well as the free space and the free space procentage, for all the tablespaces of the instance.
You can monitor only one tablespace, adding the filter with the WHERE clause and the name of the tablespace.

where df.tablespace_name = 'nb_tablespace'

It is advisable to sort the result of the query. Monitoring applications typically maintain alert thresholds based on the free percentage of the tablespace.

To find the same effect, you can sort the query result by the column showing the percentage of free space, using the ORDER BY clause.

order by 4

Free space by datafiles

A tablespace is composed of one or more datafiles.

That is why, in addition to reviewing the free and occupied space of the tablespaces, it is also interesting to review the occupancy level of the files that make up the tablespace.

By default, the data files are filled uniformly, to avoid that some files are busier than others, but throughout the life of the database you can create new files or expand existing ones, so at a certain time it is not mandatory that they are with the same occupation.

That is why it can also be interesting to know the use and free space of each of the datafiles of the Oracle database.
The query to know this data is:

select tablespace_name nb_tablespace, 
 file_id, file_name nb_fichero, 
  size_data_file_mb tamaño_fichero_MB,
 nvl(free_size_mb.0) espacio_libre_MB
from (
 select d.tablespace_name, d.file_id, d.file_name, 
 d.bytes/1024/1024 size_data_file_mb, 
 sum(f.bytes)/1024/1024 free_size_mb 
 from dba_data_files d left outer join dba_free_space f on d.file_id=f.file_id
 group by d.tablespace_name, d.file_id, d.file_name, d.bytes
order by tablespace_name, file_name;

This query returns all datafiles in the instance. It can be filtered by a specific tablespace, putting a filter with WHERE

where d.tablespace_name = 'USERS'

With these two querys we can have controlled the growth of the data in our database.
We hope you found it useful.

Database computer

If you don’t want to miss tricks like this and stay up to date with this world, subscribe to our newsletter. We will keep you informed with only one email per month.

Comments are closed.