通过dbeaver查询Oracle数据库表空间使用率

查询数据库总容量，已使用容量，剩余容量

SELECT
ROUND(SUM(a.bytes) / 1024 / 1024 / 1024, 2) AS total_size_gb, -- 总大小（GB）
ROUND(SUM(a.bytes - NVL(f.bytes, 0)) / 1024 / 1024 / 1024, 2) AS used_size_gb, -- 已使用大小（GB）
ROUND(SUM(NVL(f.bytes, 0)) / 1024 / 1024 / 1024, 2) AS free_size_gb -- 剩余大小（GB）
FROM dba_data_files a
LEFT JOIN (SELECT file_id, SUM(bytes) AS bytes FROM dba_free_space GROUP BY file_id) f
ON a.file_id = f.file_id;

 

查询表空间的详细使用情况

SELECT

df.tablespace_name,

 df.total_mb,

nvl(fs.free_mb, 0) AS free_mb,

df.total_mb - nvl(fs.free_mb, 0) AS used_mb,

ROUND((df.total_mb - nvl(fs.free_mb, 0)) / df.total_mb * 100, 2) AS used_percent

FROM

(SELECT tablespace_name, ROUND(SUM(bytes) / 1024 / 1024, 2) AS total_mb

 FROM dba_data_files

GROUP BY tablespace_name) df

LEFT JOIN

 (SELECT tablespace_name, ROUND(SUM(bytes) / 1024 / 1024, 2) AS free_mb

 FROM dba_free_space

GROUP BY tablespace_name) fs

ON df.tablespace_name = fs.tablespace_name

ORDER BY df.tablespace_name;