依靠dba_hist_tbspc_space_usage视图 获得表空间的每日增长量

该语句貌似摘自落落总的博客。具体我也忘记了。

SQL> select a.name, b.*
  2    from v$tablespace a,
  3         (select tablespace_id,
  4                 trunc(to_date(rtime, 'mm/dd/yyyy hh24:mi:ss')) datetime,
  5                 round(max(tablespace_usedsize * 8 / 1024),2) used_size_MB
  6            from dba_hist_tbspc_space_usage
  7           where trunc(to_date(rtime, 'mm/dd/yyyy hh24:mi:ss')) >
  8                 trunc(sysdate - 17)
  9           group by tablespace_id,
 10                    trunc(to_date(rtime, 'mm/dd/yyyy hh24:mi:ss'))
 11           order by tablespace_id,
 12                    trunc(to_date(rtime, 'mm/dd/yyyy hh24:mi:ss'))) b
 13   where a.ts# = b.tablespace_id;


NAME                           TABLESPACE_ID DATETIME        USED_SIZE_MB
------------------------------ ------------- --------------- ------------
SYSTEM                                     0 13-MAR-14            1466.38
SYSTEM                                     0 14-MAR-14            1466.38
SYSTEM                                     0 15-MAR-14            1466.44
SYSTEM                                     0 16-MAR-14            1466.44
SYSTEM                                     0 17-MAR-14            1466.44
SYSTEM                                     0 18-MAR-14            1466.44
SYSTEM                                     0 19-MAR-14            1466.44
SYSTEM                                     0 20-MAR-14            1466.44