获得表空间增长量和增长率的脚本

对于涉及到数据库的人员,管理员等 都会关心一点 就是每天数据的增长多少,增长率是多少?

SELECT A.NAME, B.TABLESPACE_ID,B.DATETIME,B.USED_SIZE_MB,B.INC_MB,
       CASE WHEN SUBSTR(INC_RATE,1,1)='.' THEN '0'||INC_RATE
            WHEN SUBSTR(INC_RATE,1,2)='-.' THEN '-0'||SUBSTR(INC_RATE,2,LENGTH(INC_RATE))  
            ELSE INC_RATE
       END AS INC_RATEX
  FROM V$TABLESPACE A, 
       (
           SELECT TABLESPACE_ID,DATETIME,
                  USED_SIZE_MB,  
                  (DECODE(PREV_USE_MB,0,0,USED_SIZE_MB)-PREV_USE_MB) AS  INC_MB, 
                  TO_CHAR(ROUND((DECODE(PREV_USE_MB,0,0,USED_SIZE_MB)-PREV_USE_MB)/DECODE(PREV_USE_MB,0,1,PREV_USE_MB)*100,2))||'%' AS INC_RATE
         FROM
         (
           SELECT TABLESPACE_ID, 
                  TRUNC(TO_DATE(RTIME, 'mm/dd/yyyy hh24:mi:ss')) DATETIME, 
                  MAX(TABLESPACE_USEDSIZE * 8 / 1024) USED_SIZE_MB,
                  LAG(MAX(TABLESPACE_USEDSIZE * 8 / 1024),1,0) OVER(PARTITION BY TABLESPACE_ID ORDER BY TRUNC(TO_DATE(RTIME, 'mm/dd/yyyy hh24:mi:ss')) ) AS PREV_USE_MB
             FROM DBA_HIST_TBSPC_SPACE_USAGE
            WHERE TRUNC(TO_DATE(RTIME, 'mm/dd/yyyy hh24:mi:ss')) > TRUNC(SYSDATE - 30)
            GROUP BY TABLESPACE_ID, TRUNC(TO_DATE(RTIME, 'mm/dd/yyyy hh24:mi:ss'))
          
         ) 
       ) B
 WHERE A.TS# = B.TABLESPACE_ID
 ORDER BY B.TABLESPACE_ID,DATETIME;

效果如下:

前提是必须有这个视图存在,不过10G以上都有. 可惜这个视图存的数据才最近9天的.

create or replace view dba_hist_tbspc_space_usage as
select tb.snap_id, tb.dbid, tablespace_id, tablespace_size,
       tablespace_maxsize, tablespace_usedsize, rtime
  from (select distinct snap_id, dbid
          from WRM$_SNAPSHOT where status = 0) sn,
       WRH$_TABLESPACE_SPACE_USAGE tb
  where     sn.snap_id         = tb.snap_id
        and sn.dbid            = tb.dbid;
comment on table DBA_HIST_TBSPC_SPACE_USAGE is 'Tablespace Usage Historical Statistics Information';

为了获得长时间的数据 建议大家亲自动手把视图( dba_hist_tbspc_space_usage)的数据保存在固定表中. 

建个过程,跑个JOB.