How to view table space size in PL/SQL?

1 year ago
Jackson Davis
1 minute

In PL/SQL, you can use the following SQL query statement to check the size of table spaces:

SELECT tablespace_name, 
       ROUND(SUM(bytes) / 1024 / 1024, 2) AS total_size_mb,
       ROUND(SUM(bytes - decode(max(bytes), NULL, 0, max(bytes))) / 1024 / 1024, 2) AS used_size_mb,
       ROUND(MAX(bytes) / 1024 / 1024, 2) AS largest_extent_mb
FROM dba_data_files
GROUP BY tablespace_name;

This query will return the name, total size, used size, and maximum expansion size (maximum size of a single data file) for each tablespace. The dba_data_files is a system view that contains information on data files, querying this view allows obtaining relevant tablespace information.

bannerAds