Hello everyone.
One of the most important tasks we face when administering an Oracle DB is to control the growth of the different components that form it. That is why we will see how to know the free space in Oracle.
The monitoring applications warn us when the files are getting full. They are very useful tools for efficient administration.
It may also be advisable to do this follow-up by means of queries directly on the same database.
View space by tablespaces
DB objects such as tables, indexes, etc. are stored in tablespaces. Once an object is created and stored in the tablespace, as data enters the object, the Oracle engine automatically allocates space within the tablespace.
For this reason, it is very interesting to be able to monitor the free space in Oracle DB at tablespace level, and we have a query that comes in handy to perform this task.
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 tablespace, as well as the free space and the percentage of free space, for all 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 recommended to sort the result of the query. Monitoring applications typically maintain alert thresholds based on the percentage of free tablespace.
To achieve the same effect, you can sort the query result by the column that shows the percentage of free space, with the ORDER BY clause.
order by 4
Free space per datafile
Un tablespace está compuesto por uno o más datafiles.
Therefore, in addition to checking the free and occupied space of the tablespaces, it is also interesting to check the occupancy level of the files that make up the tablespace.
By default, the data files are filled uniformly, to avoid some files being busier than others, but throughout the life of the database, new files can be created or existing ones can be expanded, so that at a given time it is not mandatory that they are at the same occupancy.
That is why it can be interesting to know the usage and free space of each of the Oracle DB datafiles.
The query to know this data is:
select tablespace_name nb_tablespace, file_id, file_name nb_fichero, size_data_file_mb size_file_MB, nvl(free_size_mb,0) free_space_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 the datafiles of the instance. You can filter by a specific tablespace, by setting a filter with WHERE
where d.tablespace_name = 'USERS'
With these two queries we can control the growth of the data in our database.
We hope it has been useful to you.
Greetings.
Database Team
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 a single email per month.
Still do not know Query Performance ? Find out how it can help you in your Oracle environment. More information on their page LinkedIn .
Follow GPS on LinkedIn