查看数据库历史增长情况
此处是通过计算数据库所有表空间的历史增长情况来计算数据库历史情况。
--不含undo和temp

with tmp as
(select rtime,
sum(tablespace_usedsize_kb) tablespace_usedsize_kb,
sum(tablespace_size_kb) tablespace_size_kb
from (select rtime,
e.tablespace_id,
(e.tablespace_usedsize) * (f.block_size) / 1024 tablespace_usedsize_kb,
(e.tablespace_size) * (f.block_size) / 1024 tablespace_size_kb
from dba_hist_tbspc_space_usage e,
dba_tablespaces f,
v$tablespace g
where e.tablespace_id = g.TS#
and f.tablespace_name = g.NAME
and f.contents not in ('TEMPORARY','UNDO'))
group by rtime)
select tmp.rtime,
tablespace_usedsize_kb,
tablespace_size_kb,
(tablespace_usedsize_kb -
LAG(tablespace_usedsize_kb, 1, NULL) OVER(ORDER BY tmp.rtime)) AS DIFF_KB
from tmp,
(select max(rtime) rtime
from tmp
group by substr(rtime, 1, 10)) t2
where t2.rtime = tmp.rtime;

--含undo和temp

with tmp as
(select min(rtime) rtime,
sum(tablespace_usedsize_kb) tablespace_usedsize_kb,
sum(tablespace_size_kb) tablespace_size_kb
from (select rtime,
e.tablespace_id,
(e.tablespace_usedsize) * (f.block_size) / 1024 tablespace_usedsize_kb,
(e.tablespace_size) * (f.block_size) / 1024 tablespace_size_kb
from dba_hist_tbspc_space_usage e,
dba_tablespaces f,
v$tablespace g
where e.tablespace_id = g.TS#
and f.tablespace_name = g.NAME)
group by rtime)
select tmp.rtime,
tablespace_usedsize_kb,
tablespace_size_kb,
(tablespace_usedsize_kb -
LAG(tablespace_usedsize_kb, 1, NULL) OVER(ORDER BY tmp.rtime)) AS DIFF_KB
from tmp,
(select min(rtime) rtime
from tmp
group by substr(rtime, 1, 10)) t2 where t2.rtime = tmp.rtime