Mantenimiento de datafiles

Adjunto un pequeño script que tengo configurado vía crontab, para tener controlado cuando los tablespaces llegan a cierto umbral que se pasa por parámetro.

Lo que se hace es redimensionar el datafile añadiendo 5Gb y me notifica vía email si ha habido algún tablespaces que haya sufrido ese mantenimiento.

¡Espero que os sirva!

#!/bin/sh

. /home/oracle/.bashrc

UMBRAL=${1}

getInfo_Tablespace() {
    sqlplus -s ${USER}/${PASS}@${SID} <<EOF
     set lines 200 pages 9000
     set termout off
     set heading off
     set feedback off; 
     spool INFO_TABLESPACE.log
     Select 'Tablespace '||"Name"||' Ocupado: '||Trunc("(Used) %",2)||'%' Info, "Name" Tablespace_Nane
      From  (
      Select d.Status "Status",
         d.Tablespace_Name "Name",
         To_Char(Nvl(a.Bytes / 1024 / 1024 / 1024, 0), '99,999,990.90') "Size (GB)",
         To_Char(Nvl(a.Bytes - Nvl(f.Bytes, 0), 0) / 1024 / 1024 / 1024,
                 '99999999.99') "Used (GB)",
         To_Char(Nvl(f.Bytes / 1024 / 1024 / 1024, 0), '99,999,990.90') "Free (GB)",
         Nvl((a.Bytes - Nvl(f.Bytes, 0)) / a.Bytes * 100, 0) "(Used) %"
       From Sys.Dba_Tablespaces d,
         (Select pp.Name Tablespace_Name, Sum(Bytes) Bytes
            From v\$DATAFILE rr, v\$tablespace pp
           Where rr.TS# = pp.TS#
           Group By pp.Name
           ) a,
         (Select Tablespace_Name, Sum(Bytes) Bytes
            From Dba_Free_Space
           Group By Tablespace_Name) f
      Where d.Tablespace_Name = a.Tablespace_Name(+)
        And d.Tablespace_Name = f.Tablespace_Name(+)
        And Not (d.Extent_Management Like 'LOCAL' And d.Contents Like 'TEMPORARY')
     ) Where "(Used) %" >= ${UMBRAL} And Instr("Name",'SYS') =  0 And Instr("Name",'UNDOTBS1') = 0;
    set heading on;
    set feedback on; 
    spool off;
    exit;
EOF
}

lanza_sqlplus() {
    sqlplus -s ${USER}/${PASS}@${SID} <<EOF
    set lines 200 pages 9000
    set termout off
    set heading off
    ${SQL}
    EXIT;
EOF
}
   getInfo_Tablespace

   LINEAS=`cat INFO_TABLESPACE.log`

   if [[ "$LINEAS" ]]
   then
     while IFS='' read -r line
     do
      set __ $line
      TABLESPACE=${6}
      if [[ "${TABLESPACE}" ]]
      then
      SQL=$(cat <<EOF
       Select Name, Ceil((BYTES + (5*(1024*1024*1024)))/1024/1024) Size_Mb
        From (
         Select r.FILE#, r.NAME , BYTES
          From v\$datafile r, v\$Tablespace z
         Where z.name = '${TABLESPACE}'
           And z.ts# = r.TS#
         Order By CREATION_TIME Desc
             )
        Where Rownum = 1;
EOF
    )

     set __ $(lanza_sqlplus)

     DATAFILE=${2}
     TAMANYO=${3}

     if [[ "${DATAFILE}" ]]
     then
     echo ${2}
     SQL=$(cat << EOF
     begin
      execute immediate 'ALTER DATABASE DATAFILE ''${DATAFILE}'' RESIZE  ${TAMANYO}M';
     end;
     /
EOF
)    
     echo ${SQL}

     $(lanza_sqlplus)

      . /home/oracle/scripts/email.sh "Mantenimiento Tablespace ${TABLESPACE}  with more ${UMBRAL}%"  "Mantenimiento Tablespace ${TABLESPACE}  with more ${UMBRAL}%"
     fi
     fi
     done <  INFO_TABLESPACE.log
   fi