Oracle查询表空间的每日增长量

#############################历史数据库增量，适用各版本oracle=============

SELECT day,total_GB,used_GB,total_GB-used_GB free_GB,round(100*used_GB/total_GB,2) used_percent,case when(used_GB=used_GB-LAG(used_GB,1,0)OVER(ORDER BY day)) then null else used_GB-LAG(used_GB,1,0)OVER(ORDER BY day) end incr_GB
 from 
 (select to_char(snap.END_INTERVAL_TIME, 'yyyy-mm-dd hh24:mi:ss.ff') day,
       round(sum(tsu.TABLESPACE_SIZE * dt.BLOCK_SIZE) / (1024 * 1024 * 1024),2) total_GB,
       round(sum(tsu.TABLESPACE_USEDSIZE * dt.BLOCK_SIZE) / (1024 * 1024 * 1024),2) used_GB
  from DBA_HIST_TBSPC_SPACE_USAGE tsu,
       DBA_HIST_SNAPSHOT          snap,
       V$TABLESPACE               vt,
       DBA_TABLESPACES            dt
 where tsu.SNAP_ID = snap.SNAP_ID
   and tsu.DBID = snap.DBID
   and snap.instance_number = 1
   and tsu.TABLESPACE_ID = vt.TS#
   and vt.NAME = dt.TABLESPACE_NAME 
   and SUBSTR(to_char(END_INTERVAL_TIME, 'yyyy-mm-dd hh24:mi:ss.ff'),12,5)='00:00'
 group by snap.END_INTERVAL_TIME
 order by snap.END_INTERVAL_TIME desc) a 
 order by day desc;

显示效果如下：

===改造不包含某些表空间的增量参考=========

SELECT day,total_GB,used_GB,total_GB-used_GB free_GB,round(100*used_GB/total_GB,2) used_percent,case when(used_GB=used_GB-LAG(used_GB,1,0)OVER(ORDER BY day)) then null else used_GB-LAG(used_GB,1,0)OVER(ORDER BY day) end incr_GB
 from 
 (select to_char(snap.END_INTERVAL_TIME, 'yyyy-mm-dd hh24:mi:ss.ff') day,
       round(sum(tsu.TABLESPACE_SIZE * dt.BLOCK_SIZE) / (1024 * 1024 * 1024),2) total_GB,
       round(sum(tsu.TABLESPACE_USEDSIZE * dt.BLOCK_SIZE) / (1024 * 1024 * 1024),2) used_GB
  from DBA_HIST_TBSPC_SPACE_USAGE tsu,
       DBA_HIST_SNAPSHOT          snap,
       V$TABLESPACE               vt,
       DBA_TABLESPACES            dt
 where tsu.SNAP_ID = snap.SNAP_ID
   and tsu.DBID = snap.DBID
   and snap.instance_number = 1
   and tsu.TABLESPACE_ID = vt.TS#
   and vt.NAME = dt.TABLESPACE_NAME
   -- and dt.TABLESPACE_NAME='USERS'  --包含特定表空间
   and dt.TABLESPACE_NAME not in ('SYSTEM','SYSAUX')--不包括系统表空间和辅助表空间
   and dt.contents not in ('TEMPORARY','UNDO') --不包括临时表空间和UNDO表空间
   and SUBSTR(to_char(END_INTERVAL_TIME, 'yyyy-mm-dd hh24:mi:ss.ff'),12,5)='00:00'
 group by snap.END_INTERVAL_TIME
 order by snap.END_INTERVAL_TIME desc) a 
 order by day desc;

显示效果如下：

http://blog.itpub.net/26736162/viewspace-2657751/