Script para analizar tendendencia de crecimiento de tablespaces

A continuación les voy a pasar un script que permite monitorear como van llenandose los tablespaces y ademas obtiene una proyección de crecimiento a futuro. A mi me sirve para minimizar el riesgo de que se queden sin espacio los tablespaces (no suelo usar autoextend) y así poder realizar realocaciones o resizing en forma anticipada.

Rem
Rem  Tendencia_Crecimiento_x_Tablespace
Rem
Rem  NOMBRE
Rem       Tendencia_Crecimiento_x_Tablespace.sql
Rem
Rem  DESCRIPCION
Rem       Reporte para mostrar como fue creciendo un tablespace por hora, dia,
Rem       semana y mes. Tambien realiza proyecciones de crecimiento por semana -
Rem       mes y muestra STATUS (Aplica para 10+)    
Rem
Rem
Rem      provedo  04/03/09  -- Creado
Rem
set line 150
col "%Used" format a10
col "%Proy_1s" format a10
col "%Proy_1m" format a10
col tsname format a20
select tsname,
round(tablespace_size*t2.block_size/
1024/1024,2) TSize,
round(tablespace_usedsize*t2.block_size/1024/1024,2) TUsed,
round((tablespace_size-tablespace_usedsize)*t2.block_size/1024/1024,2) TFree,
round(val1*t2.block_size/1024/1024,2) "Dif_1h",
round(val2*t2.block_size/1024/1024,2) "Dif_1d",
round(val3*t2.block_size/1024/1024,2) "Dif_1s",
round(val4*t2.block_size/1024/1024,2) "Dif_1m",
round((tablespace_usedsize/tablespace_size)*100)||'%' "%Used",
round(((tablespace_usedsize+val3)/tablespace_size)*100)||'%' "%Proy_1s",
round(((tablespace_usedsize+val4)/tablespace_size)*100)||'%' "%Proy_1m",
case when ((((tablespace_usedsize+val3)/tablespace_size)*100 < 80) and
          (((tablespace_usedsize+val4)/tablespace_size)*100 < 80)) then 'NORMAL'
     when ((((tablespace_usedsize+val3)/tablespace_size)*100 between 80 and 90) 
             or
          (((tablespace_usedsize+val4)/tablespace_size)*100 between 80 and 90)) 
    then 'WARNING'
else 'CRITICAL' end STATUS
from
(select distinct tsname,
rtime,
tablespace_size,
tablespace_usedsize,
tablespace_usedsize-first_value(tablespace_usedsize) 
over (partition by tablespace_id order by rtime rows 1 preceding) val1,
tablespace_usedsize-first_value(tablespace_usedsize) 
over (partition by tablespace_id order by rtime rows 24 preceding) val2,
tablespace_usedsize-first_value(tablespace_usedsize) 
over (partition by tablespace_id order by rtime rows 168 preceding) val3,
tablespace_usedsize-first_value(tablespace_usedsize) 
over (partition by tablespace_id order by rtime rows 720 preceding) val4
from (select t1.tablespace_size, t1.snap_id, t1.rtime,t1.tablespace_id, 
             t1.tablespace_usedsize-nvl(t3.space,0) tablespace_usedsize
     from dba_hist_tbspc_space_usage t1,
          dba_hist_tablespace_stat t2,
          (select ts_name,sum(space) space 
           from recyclebin group by ts_name) t3
     where t1.tablespace_id = t2.ts#
      and  t1.snap_id = t2.snap_id
      and  t2.tsname = t3.ts_name (+)) t1,
dba_hist_tablespace_stat t2
where t1.tablespace_id = t2.ts#
and t1.snap_id = t2.snap_id) t1,
dba_tablespaces t2
where t1.tsname = t2.tablespace_name
and rtime = (select max(rtime) from dba_hist_tbspc_space_usage)
and t2.contents = 'PERMANENT'
order by "Dif_1h" desc,"Dif_1d" desc,"Dif_1s" desc, "Dif_1m" desc

Un ejemplo de la salida resultado de correr el script es la siguiente:

TSNAME                    TSIZE      TUSED      TFREE     Dif_1h     Dif_1d     Dif_1s     Dif_1m %Used      %Proy_1s   %Proy_1m   STATUS
-------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- --------
SYSAUX                      580        551         29        .13      -3.31     -17.81     -17.81 95%        92%        92%        CRITICAL
TS_TEL                    61440   41097.13   20342.88          0     748.63      -2776      -2776 67%        62%        62%        NORMAL
DESA_TS                    7552    3278.56    4273.44          0         12      14.19      14.19 43%        44%        44%        NORMAL
USERS                     23205     720.44   22484.56          0        .06        .25        .25 3%         3%         3%         NORMAL
SYSTEM                     3620    1067.75    2552.25          0          0       2.06       2.06 29%        30%        30%        NORMAL
TS_DATA                   10240   10220.44      19.56          0          0          0          0 100%       100%       100%       CRITICAL
SCH_DATA                    900     899.69        .31          0          0          0          0 100%       100%       100%       CRITICAL
EXAMPLE                     100      68.19      31.81          0          0          0          0 68%        68%        68%        NORMAL
ROP                        1024          0       1024          0          0          0          0 0%         0%         0%         NORMAL
TS_INDEX                   4096     506.31    3589.69          0          0          0          0 12%        12%        12%        NORMAL
SCH_INDEX                   100       6.94      93.06          0          0          0          0 7%         7%         7%         NORMAL

Ahora voy a describir las columnas del reporte:

TSNAME       : Nombre del Tablespace         
TSIZE        : Espacio total del Tablespace en Mb (no toma en cuenta autoextend del
            tablespace)
TUSED        : Espacio utilizado (Mb)
TFREE        : Espacio Libre (Mb)
Dif_1h       : Diferencia entre el espacio alocado hace 1 hora y el espacio actual
            (Mb).
Dif_1d       : Diferencia entre el espacio alocado hace 1 dia y el espacio actual
            (Mb).
Dif_1s       : Diferencia entre el espacio alocado hace 1 semana y el espacio actual
            (Mb).
Dif_1m       : Diferencia entre el espacio alocado hace 1 mes y el espacio actual
            (Mb).
%Used        : Porcentaje de Uso actual del tablespace.
%Proy_1s     : Porcentaje de Uso Proyectado a 1 semana adelante.
%Proy_1m     : Porcentaje de Uso Proyectado a 1 mes adelante.
STATUS       : Status de alocación: menor al 80% es normal, entre 80% y 90% es
            warning y mayor al 90% es critical.

El script toma en cuenta que el AWR guarda 1 mes de historia y corre cada 1 hora.