查询表空间存储变化SQL

跟踪每天的数据量，还可以统计周和月的增量情况，预计未来存储需求以kb为单位

select name,
sum(case substr(rtime,1,10) when '12/23/2011' then diff else null end) as "20111223",
sum(case substr(rtime,1,10) when '12/24/2011' then diff else null end) as "20111224",
sum(case substr(rtime,1,10) when '12/25/2011' then diff else null end) as "20111225",
sum(case substr(rtime,1,10) when '12/26/2011' then diff else null end) as "20111226",
sum(case substr(rtime,1,10) when '12/27/2011' then diff else null end) as "20111227",
sum(case substr(rtime,1,10) when '12/28/2011' then diff else null end) as "20111228",
sum(case substr(rtime,1,10) when '12/29/2011' then diff else null end) as "20111229"
from (
select t.name,
h.tablespace_usedsize,
h.rtime,
(h.tablespace_usedsize - lag(h.tablespace_usedsize,1,null)
over(partition by tablespace_id order by snap_id)) as diff
from v$tablespace t, dba_hist_tbspc_space_usage h
where t.ts#=h.tablespace_id)
group by name
order by 1;