对于涉及到数据库的人员,管理员等 都会关心一点 就是每天数据的增长多少,增长率是多少?
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_RATEWHEN SUBSTR(INC_RATE,1,2)='-.' THEN '-0'||SUBSTR(INC_RATE,2,LENGTH(INC_RATE)) ELSE INC_RATEEND AS INC_RATEXFROM 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_RATEFROM(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_MBFROM DBA_HIST_TBSPC_SPACE_USAGEWHERE 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'))) ) BWHERE A.TS# = B.TABLESPACE_IDORDER BY B.TABLESPACE_ID,DATETIME;
效果如下:
前提是必须有这个视图存在,不过10G以上都有. 可惜这个视图存的数据才最近9天的.
create or replace view dba_hist_tbspc_space_usage asselect tb.snap_id, tb.dbid, tablespace_id, tablespace_size,tablespace_maxsize, tablespace_usedsize, rtimefrom (select distinct snap_id, dbidfrom WRM$_SNAPSHOT where status = 0) sn,WRH$_TABLESPACE_SPACE_USAGE tbwheresn.snap_id = tb.snap_idand sn.dbid = tb.dbid;comment on table DBA_HIST_TBSPC_SPACE_USAGE is 'Tablespace Usage Historical Statistics Information';
为了获得长时间的数据 建议大家亲自动手把视图(dba_hist_tbspc_space_usage)的数据保存在固定表中.
建个过程,跑个JOB.
