2017-06-14
Oracle 表空间使用率
-- 表空间使用率1:DBA_DATA_FILES、DBA_SEGMENTS SELECT tablespace_name, ROUND(a.total_bytes / 1024 / 1024 / 1024, 0) || 'G' as TOTAL, ROUND((a.total_bytes - DECODE(b.used_bytes, null, 0, b.used_bytes)) / 1024 / 1024 / 1024, 0) || 'G' as FREE, ROUND(DECODE(b.used_bytes, null, 0, b.used_bytes) / 1024 / 1024 / 1024, 0) || 'G' as USED, ROUND(DECODE(b.used_bytes, null, 0, b.used_bytes) / a.total_bytes, 4) * 100 as "USED_RATIO(%)" FROM (SELECT tablespace_name, sum(DECODE(autoextensible, 'YES', maxbytes, 'NO', bytes)) as total_bytes FROM DBA_DATA_FILES GROUP BY tablespace_name) a LEFT JOIN (SELECT tablespace_name, sum(bytes) as used_bytes FROM DBA_SEGMENTS GROUP BY tablespace_name) b USING (tablespace_name) ORDER BY 5 DESC;
-- 表空间使用率2:DBA_DATA_FILES、DBA_EXTENTS SELECT tablespace_name, ROUND(a.total_bytes / 1024 / 1024 / 1024, 0) || 'G' as total, ROUND((a.total_bytes - DECODE(b.used_bytes, null, 0, b.used_bytes)) / 1024 / 1024 / 1024, 0) || 'G' as free, ROUND(DECODE(b.used_bytes, null, 0, b.used_bytes) / 1024 / 1024 / 1024, 0) || 'G' as used, ROUND(DECODE(b.used_bytes, null, 0, b.used_bytes) / a.total_bytes, 4) * 100 as "USED_RATIO(%)" FROM (SELECT tablespace_name, sum(DECODE(autoextensible, 'YES', maxbytes, 'NO', bytes)) as total_bytes FROM DBA_DATA_FILES GROUP BY tablespace_name) a LEFT JOIN (SELECT tablespace_name, sum(bytes) as used_bytes FROM DBA_EXTENTS GROUP BY tablespace_name) b USING (tablespace_name) ORDER BY 5 DESC;
-- 表空间使用率3:DBA_DATA_FILES、DBA_FREE_SPACE SELECT tablespace_name, ROUND(a.total_bytes / 1024 / 1024 / 1024, 0) || 'G' as total, ROUND(DECODE(b.free_bytes, null, 0, b.free_bytes) / 1024 / 1024 / 1024, 0) || 'G' as free, ROUND((a.total_bytes - DECODE(b.free_bytes, null, 0, b.free_bytes)) / 1024 / 1024 / 1024, 0) || 'G' as used, ROUND((a.total_bytes - DECODE(b.free_bytes, null, 0, b.free_bytes)) / a.total_bytes, 4) * 100 as "USED_RATIO(%)" FROM (SELECT tablespace_name, sum(DECODE(autoextensible, 'YES', maxbytes, 'NO', bytes)) as total_bytes FROM DBA_DATA_FILES GROUP BY tablespace_name) a LEFT JOIN (SELECT tablespace_name, sum(bytes) as free_bytes FROM DBA_FREE_SPACE GROUP BY tablespace_name) b USING (tablespace_name) ORDER BY 5 DESC;
参考资料
[1] Oracle查询表空间使用情况