select sum("tablespace_size(M)"), sum("used_size(M)"), sum("free_size(M)"),
round(sum("used_size(M)")/sum("tablespace_size(M)")*100,2) "used_percentage(%)"
from (
select t2.tablespace_size "tablespace_size(M)",
t2.tablespace_size - nvl(t1.free_size,0) "used_size(M)",
nvl(t1.free_size,0) "free_size(M)",
round((t2.tablespace_size - nvl(t1.free_size,0))*100/t2.tablespace_size,2) "used_percentage(%)"
from dba_tablespaces t0,
(select tablespace_name,round(sum(bytes)/1024/1024) free_size from dba_free_space group by tablespace_name) t1,
(select tablespace_name,round(sum(bytes)/1024/1024) tablespace_size from dba_data_files group by tablespace_name) t2
where t0.tablespace_name = t1.tablespace_name(+) and
t0.tablespace_name = t2.tablespace_name(+) and not
(t0.extent_management like 'LOCAL' and t0.contents like 'TEMPORARY')
union all
select t2.tablespace_size "tablespace_size(M)",
nvl(t1.used_size,0) "used_size(M)",
t2.tablespace_size - nvl(t1.used_size,0) "free_size(M)",
round(nvl(t1.used_size,0)*100/t2.tablespace_size,2) "used_percentage(%)"
from dba_tablespaces t0,
(select tablespace_name,round(sum(bytes_cached)/1024/1024) used_size from v$temp_extent_pool group by tablespace_name) t1,
(select tablespace_name,round(sum(bytes)/1024/1024) tablespace_size from dba_temp_files group by tablespace_name) t2
where t0.tablespace_name = t1.tablespace_name(+) and
t0.tablespace_name = t2.tablespace_name(+) and
t0.extent_management like 'LOCAL' and
t0.contents like 'TEMPORARY');

ITPub博客

Size Database

最新文章