Tablespace doluluk oranı belli bir eşik değeri (örneğin %90 limitini) aştığında otomatik alert mailini alabiliriz, eski sonuçlarla karşılaştırma yapmak istiyorsak tablomuzun history’sini de oluşturabiliriz;

CREATE TABLE TABLESPACE_SIZE_DAILY
(
  LOG_AS_DATE      DATE,
  TABLESPACE_NAME  VARCHAR2(20 BYTE),
  GBYTE_USED       NUMBER,
  GBYTE_FREE       NUMBER,
  GBYTE_SUM_AREA   NUMBER,
  PERCENT_USED     NUMBER
);
CREATE TABLE TABLESPACE_SIZE_DAILY_HIST
(
  LOG_AS_DATE      DATE,
  TABLESPACE_NAME  VARCHAR2(20 BYTE),
  GBYTE_USED       NUMBER,
  GBYTE_FREE       NUMBER,
  GBYTE_SUM_AREA   NUMBER,
  PERCENT_USED     NUMBER
);


CREATE OR REPLACE PROCEDURE TABLESPACE_SIZE_CHECK
IS
   -- tablespace alanlarının kullanım durumları,dolu ve boş alanlar,yüzdeleri
   -- Orhan Eripek 16.12.2013
   v_dyntask   VARCHAR2 (2000);
BEGIN
   EXECUTE IMMEDIATE 'TRUNCATE TABLE tablespace_size_daily';
   COMMIT;
   v_dyntask :=
      'INSERT     /*+ APPEND NOLOGGING */ALL
      INTO tablespace_size_daily
           (log_as_date, tablespace_name, GBYTE_used, GBYTE_free, GBYTE_sum_area,percent_used)
      INTO tablespace_size_daily_hist
           (log_as_date, tablespace_name,GBYTE_used, GBYTE_free, GBYTE_sum_area,percent_used)
SELECT systarih, tablespace_name, TRUNC(GBYTE_used,1) as GBYTE_used, TRUNC(GBYTE_free,1) as
GBYTE_free, TRUNC(GBYTE_sum_area,1) as GBYTE_sum_area, TRUNC(percent_used,1) as percent_used
FROM                   
(SELECT SYSDATE AS systarih, a.tablespace_name,
                            a.BYTES/1024/1024/1024 AS GBYTE_used, b.BYTES/1024/1024/1024 AS GBYTE_free,
                            ((a.BYTES/1024/1024/1024)+(b.BYTES/1024/1024/1024)) AS GBYTE_sum_area,
                            ROUND (((a.BYTES - b.BYTES) / a.BYTES) * 100, 2 ) as percent_used
                       FROM (SELECT   tablespace_name, SUM (BYTES) BYTES
                                 FROM SYS.dba_data_files
                             GROUP BY tablespace_name) a,
                            (SELECT   tablespace_name, SUM (BYTES) BYTES,
                                      MAX (BYTES) max_free_area
                                 FROM SYS.dba_free_space
                             GROUP BY tablespace_name) b
                      WHERE a.tablespace_name = b.tablespace_name
                   ORDER BY ((a.BYTES - b.BYTES) / a.BYTES) DESC)';
   EXECUTE IMMEDIATE v_dyntask;
   COMMIT;
END;
/


CREATE OR REPLACE PROCEDURE TABLESPACE_SIZE_EMAIL
IS
-- Orhan Eripek 16.12.2013
v_text long;
BEGIN
  EXECUTE IMMEDIATE 'ALTER SESSION SET smtp_out_server = ''10.10.10.10''';
  
 v_text:=v_text||'log_date    '||'    tablespace_name '||'    GBYTE_used   '||'   GBYTE_free '||'
    GBYTE_sum_area    '||'   percent_used '||chr(10);
 
 v_text:=v_text||'________'||'      ___________'||'       ________'||'      _________'||' 
 _____________  '||'     _________'||chr(10);
                                                                                                                                                                      
for text in (select to_char(log_as_date,'DD.MM.YYYY HH24:MI:SS') as tarih,tablespace_name,
GBYTE_used,GBYTE_free, GBYTE_sum_area, percent_used from tablespace_size_daily
where percent_used>'90')
loop
 v_text:=v_text||text.tarih||'       '||text.tablespace_name||'                  '||text.GBYTE_used||'                               '||text.GBYTE_free||'                              '||text.GBYTE_sum_area||'                           '||text.percent_used||chr(10);
 end loop;

UTL_MAIL.send(sender => 'no-reply@......com.tr',
 recipients => 'orhan.eripek@.....com.tr',
 subject => 'Otomatik Alert Mail: Prod Tablespace kullanim orani %90 ı gecmistir!!!',
 message => v_text,
 mime_type => 'text; charset=us-ascii');
 END;
 /

Bu procedure’leri istediğiniz zaman periyodunda job ile çalıştırabilirsiniz,
bu manuel yöntem dışında Enterprise Manager konsoldan da otomatik mail mekanizmaları oluşturulabilir

DECLARE
 X NUMBER;
 BEGIN
 DBMS_JOB.SUBMIT
 ( job       => X
 ,what      => 'TABLESPACE_SIZE_CHECK;'
 ,next_date => to_date('24.02.2015 06:00:00','dd/mm/yyyy hh24:mi:ss')
 ,interval  => 'TRUNC(SYSDATE+1)+6/24'
 ,no_parse  => FALSE
 );
 SYS.DBMS_OUTPUT.PUT_LINE('Job Number is: ' || to_char(x));
 COMMIT;
 END;
 /
Reklam