sql代码:
set linesize 200
with a as ( select tablespace_name, sum(bytes)/1024/1024 total from dba_data_files group by tablespace_name),
b as ( select tablespace_name, sum(bytes)/1024/1024 free from dba_free_space group by tablespace_name)
select a.tablespace_name, a.total as "TOTAL(M)", b.free as "FREE(M)",round((1-b.free/a.total) * 100, 2) as "USED(%)"
from a, b
where a.tablespace_name=b.tablespace_name;
with a as ( select tablespace_name, sum(bytes)/1024/1024 total from dba_data_files group by tablespace_name),
b as ( select tablespace_name, sum(bytes)/1024/1024 free from dba_free_space group by tablespace_name)
select a.tablespace_name, a.total as "TOTAL(M)", b.free as "FREE(M)",round((1-b.free/a.total) * 100, 2) as "USED(%)"
from a, b
where a.tablespace_name=b.tablespace_name;
或者:
set linesize 200z
select a.tablespace_name, a.total as "TOTAL(M)", b.free as "FREE(M)", (1-b.free/a.total)* 100 as "USED(%)"
from
(select tablespace_name, sum(bytes)/1024/1024 total from dba_data_files
group by tablespace_name) a
,
(select tablespace_name, sum(bytes)/1024/1024 free from dba_free_space
group by tablespace_name) b
where a.tablespace_name=b.tablespace_name
from
(select tablespace_name, sum(bytes)/1024/1024 total from dba_data_files
group by tablespace_name) a
,
(select tablespace_name, sum(bytes)/1024/1024 free from dba_free_space
group by tablespace_name) b
where a.tablespace_name=b.tablespace_name
测试结果:

2012-10-31_115344.jpg