Check Oracle Tablespace Usage with SQL

1 year ago
Emily Johnson
2 minutes

You can use the following SQL query to view the usage of Oracle user tablespaces:

SELECT tablespace_name,
       sum(bytes) / 1024 / 1024 "Total Size (MB)",
       sum(bytes - blocks * block_size) / 1024 / 1024 "Used Size (MB)",
       sum(blocks * block_size) / 1024 / 1024 "Free Size (MB)",
       round((1 - sum(bytes - blocks * block_size) / sum(bytes)) * 100, 2) "Used Percentage"
FROM dba_data_files
GROUP BY tablespace_name;

This query will return the name, total size, used size, available size, and usage percentage of the user table space.

Please note that you need to have the correct permissions to access the DBA_DATA_FILES view. If you do not have access to the DBA_DATA_FILES view, you can try using the USER_TABLESPACES view to query the usage of user tablespaces.

SELECT tablespace_name,
       round(sum(bytes) / 1024 / 1024) "Total Size (MB)",
       round(sum(bytes - blocks * block_size) / 1024 / 1024) "Used Size (MB)",
       round(sum(blocks * block_size) / 1024 / 1024) "Free Size (MB)",
       round((1 - sum(bytes - blocks * block_size) / sum(bytes)) * 100, 2) "Used Percentage"
FROM user_segments
GROUP BY tablespace_name;

This query will return the name, total size, used size, available size, and usage percentage of the user tablespace.

Please note that the above query statement is only applicable for checking the usage of the user tablespace. If you want to check the usage of other tablespaces (such as system tablespace or temporary tablespace), you will need to modify the view name in the query accordingly.

bannerAds