SQL语句如下
select * from
(
select ts.tablespace_name,ts.contents "TABLESPACE_TYPE",
to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') monitor_date,
datafiles,total_gb "TOTAL_SIZE_GB",
(total_gb-free_gb) "USED_SIZE_GB",
free_gb "FREE_SIZE_GB",
round((100-free_gb/total_gb*100),2) "USED_PCT",
round(free_gb/total_gb*100,2) "FREE_PCT"
from dba_tablespaces ts,
(select tablespace_name,round(sum(bytes)/1024/1024/1024,2) free_gb
from dba_free_space group by tablespace_name) fr1,
(select tablespace_name,round(sum(bytes)/1024/1024/1024,2) total_gb,count(*) datafiles
from dba_data_files group by tablespace_name) df1
where ts.tablespace_name=fr1.tablespace_name and ts.tablespace_name=df1.tablespace_name order by "FREE_PCT"
)
union all
select 'TOTAL SUMMARY:','ALL',to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') monitor_date,datafiles,
total_gb "TOTAL_SIZE_GB",
round(total_gb-free_gb,2) "USED_SIZE_GB",
free_gb "FREE_SIZE_GB",
round((100-free_gb/total_gb*100),2) "USED_PCT",
round(free_gb/total_gb*100,2) "FREE_PCT"
from (select round(sum(bytes)/1024/1024/1024,2) free_gb from dba_free_space) fr2,
(select count(*) datafiles,round(sum(bytes)/1024/1024/1024,2) total_gb from dba_data_files) df2
union all
select null,null,null,null,null,null,null,null,null from dual;
这个SQL挺简单的， 就是访问几个数据字典而已，执行计划如下
SQL> set autotrace traceonly;
SQL> select * from
2 (
3 select ts.tablespace_name,ts.contents "TABLESPACE_TYPE",
4 to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') monitor_date,
5 datafiles,total_gb "TOTAL_SIZE_GB",
6 (total_gb-free_gb) "USED_SIZE_GB",
7 free_gb "FREE_SIZE_GB",
8 round((100-free_gb/total_gb*100),2) "USED_PCT",
9 round(free_gb/total_gb*100,2) "FREE_PCT"
from dba_tablespaces ts,
10 11 (select tablespace_name,round(sum(bytes)/1024/1024/1024,2) free_gb
12 from dba_free_space group by tablespace_name) fr1,
13 (select tablespace_name,round(sum(bytes)/1024/1024/1024,2) total_gb,count(*) datafiles
14 from dba_data_files group by tablespace_name) df1
15 where ts.tablespace_name=fr1.tablespace_name and ts.tablespace_name=df1.tablespace_name order by "FREE_PCT"
16 )
17 union all
18 select 'TOTAL SUMMARY:','ALL',to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') monitor_date,datafiles,
19 total_gb "TOTAL_SIZE_GB",
20 round(total_gb-free_gb,2) "USED_SIZE_GB",
21 free_gb "FREE_SIZE_GB",
22 round((100-free_gb/total_gb*100),2) "USED_PCT",
23 round(free_gb/total_gb*100,2) "FREE_PCT"
24 from (select round(sum(bytes)/1024/1024/1024,2) free_gb from dba_free_space) fr2,
25 (select count(*) datafiles,round(sum(bytes)/1024/1024/1024,2) total_gb from dba_data_files) df2
26 union all
27 select null,null,null,null,null,null,null,null,null from dual;
31 rows selected.
Elapsed: 00:50:32.18
Execution Plan
----------------------------------------------------------
Plan hash value: 3463738489
--------------------------------------------------------------------------------------------------------------------
| Id | Operation