Oracle Tablespace Query: Check Space Usage

1 year ago
Jackson Davis
1 minute

To check the information of table spaces in Oracle database, you can use the following query:

SELECT tablespace_name, file_name, bytes/1024/1024 AS "Size (MB)", 
bytes/1024/1024 - (sum(nvl(bytes,0))/1024/1024) as "Used (MB)", 
(sum(nvl(bytes,0))/1024/1024) as "Free (MB)", 
round(sum(nvl(bytes,0))*100/sum(sum(nvl(bytes,0))) over(),2) as "Used %"
FROM dba_data_files
GROUP BY tablespace_name, file_name, bytes
ORDER BY tablespace_name;

This query will display the name of each tablespace, file name, size in MB, used space in MB, remaining space in MB, and percentage of space used.

bannerAds