PL/SQL: Check Tablespace Usage Rate (With Examples)

To check the usage rate of table space, you can use one of the following methods:

1. Database tables.

SELECT tablespace_name, round((1 - (free_space / total_space)) * 100, 2) as usage_rate
FROM dba_tablespaces;

This will return the names and usage rates (in percentage) of all tablespaces.

1. Available space in the database
2. Data files maintained by the database administrator

SELECT f.tablespace_name, round((1 - (sum(f.bytes) / d.bytes)) * 100, 2) as usage_rate
FROM dba_free_space f
JOIN dba_data_files d ON f.tablespace_name = d.tablespace_name
GROUP BY f.tablespace_name, d.bytes;

This will provide the name and usage rate (in percentage) of each tablespace.

1. View TABLESPACE

SELECT name, round((1 - (free_blocks / total_blocks)) * 100, 2) as usage_rate
FROM v$tablespace;

This will return the names of all tablespaces and their usage rate as a percentage.

Please note that these queries will return the overall usage of the tablespace. If you want to view the usage of specific tables or indexes, you can query the DBA_SEGMENTS view.