How to check the tablespace usage in Oracle database?
Here’s a handy query to get the values of tablespace size, used and available free tablespace and usage percentage on Oracle database:
SELECT a.tablespace_name Tablespace, ROUND(( 1 - ( fbytes / tbytes ) ) * 100, 1) Percent_Used, ROUND(tbytes / 1024 / 1024, 1) MB_Total, ROUND(fbytes / 1024 / 1024, 1) MB_Free, ROUND(( tbytes - fbytes ) / 1024 / 1024, 1) MB_Used FROM (SELECT tablespace_name, SUM(bytes) tbytes FROM dba_data_files GROUP BY tablespace_name UNION ALL SELECT tablespace_name, SUM(bytes) tbytes FROM dba_temp_files GROUP BY tablespace_name) a left outer join (SELECT tablespace_name, SUM(bytes) fbytes FROM dba_free_space GROUP BY tablespace_name UNION ALL SELECT tablespace_name, SUM(user_bytes) fbytes FROM dba_temp_files GROUP BY tablespace_name) b ON a.tablespace_name = b.tablespace_name
Query retrieves results presented as in the table below:
