Tablespace Doluluk Oranlarının Günlük Takibi

Merhaba, 

Database içerisinde kullanılan tablespace' lerin büyüklüklerinin takip edilmesi sistemin sağlıklı işlemesi açısından son derece kritik önem taşımaktadır.
Tablespace' lerin size' larını takip etmenin bir sürü yöntemi vardır. Bunlardan biri Enterprise Manager konsol Manage Metric linkinden (varolan metricleri edit de edebilirsiniz) tablepspace' ler ile ilgili size özel thresholds lar tanımlayabilirsiniz. Örneğin bir tablespace' in %85' i dolduğundan warning, %95' i dolduğunda da critical alert vermesini sağlayabilirsiniz. Belirttiğiniz bir şekilde bir case oluştuğunda mail, sms bile attırabilirsiniz. Yada benim gibi bu işi günlük olarak manuel takip ediyorsanız, size yardımcı olması açısından aşağıdaki bir procedür oluşturabilirsiniz. Bu procedür özetle hergün belli bir saatte çalışarak bir tabloya benim belirlemiş olduğum kriterlere ait bilgileri insert ediyor bende sadece o tabloyu select ederek kontrol ediyorum. Procedür aslında şunları yapıyor ; 

1 - tablespace' lerin kullanım miktarlarını buluyor,
2 - tablespace' lerin free alanlarını buluyor,
3 - bulduğu bu değerleri bir tabloya tarih bilgisi ile birlikte insert ediyor, 
4 - her bir tablespace için, günlük ne kadar free alan olması gerektiğini set ediyor, (bu değeri bir sefere mahsus biz belirliyoruz),
5 - o anki free alan ile bizim free olmasını istedimiz alan arasındaki farka bakıp datafile eklenipeklenmemesine kara veriyor. 

Aşağıda bir örneğini oluşturmaya çalıştım. 

-- Öcelikle procedürümüzün dolduracağı tabloyu oluşturalım, 
CREATE TABLE KAMIL.TABLESPACE_KONTROL
(
  TABLESPACE_NAME   VARCHAR2(35 BYTE),
  GB_USED           NUMBER,
  GB_KULLANILAN     NUMBER,
  GB_FREE           NUMBER,
  PERCENT_USED      NUMBER,
  SDATE             DATE,
  TOLERANS_GB       NUMBER,
  AKSIYON           VARCHAR2(50 BYTE),
  GUNLUK_BUYUME_GB  NUMBER
) ; 
Table created.


-- exception durumunda doldurulacak olan tabloyu oluşturalım, 
CREATE TABLE KAMIL.TABLESPACE_KONTROL_LOG
(
  TARIH  DATE,
  HATA   VARCHAR2(300 BYTE)
) ; 
Table created.


-- procedürümüzü create edelim. 
--(birincisi, burada hangi user altına create ediyorsanız procedüre içerisinde kullanılan tablolara select etme hakkı vermeyi, ikinci olarak update cümlelerindeki tablespace' lerin isimlerini kendi sistemizdekiler ile değiştirmeyi unutmayın.) 


CREATE OR REPLACE PROCEDURE KAMIL.P_TABLESPACE_KONTROL(PAR_TARIH DATE DEFAULT TRUNC(SYSDATE)) IS
   SQL_ERROR_TEXT          VARCHAR2(1024);
   V_STRING                VARCHAR2(1024);
   CURSOR C1 IS
  select a.TABLESPACE_NAME tablepsace_name,
    a.gb gb_used,
    (a.gb - b.gb) gb_kullanılan,
    b.gb gb_free,
    round(((a.gb-b.gb)/a.gb)*100,2) percent_used,
    trunc(sysdate) sdate
from
    (select TABLESPACE_NAME,
        trunc(sum(Decode(BYTES/1024/1024/1024,BYTES/1024/1024/1024,BYTES/1024/1024/1024,0.00000001)),2) gb
        from     dba_data_files
        where  trunc((Decode(BYTES/1024/1024/1024,BYTES/1024/1024/1024,BYTES/1024/1024/1024,0.00000001)),2)> 0
        group     by TABLESPACE_NAME 
        
        ) a,
    (select TABLESPACE_NAME,
        trunc(sum(Decode(BYTES/1024/1024/1024,BYTES/1024/1024/1024,BYTES/1024/1024/1024,0.0000000001)),2) gb
        from     dba_free_space
        group     by TABLESPACE_NAME) b
where     a.TABLESPACE_NAME=b.TABLESPACE_NAME ;
BEGIN
   FOR C1REC IN C1 LOOP
      BEGIN
        INSERT INTO KAMIL.TABLESPACE_KONTROL
        (tablespace_name,
         gb_used,
         gb_kullanilan,
         gb_free,
         percent_used,
         sdate)
        VALUES
        (C1REC.tablepsace_name,
        C1REC.gb_used,
        C1REC.gb_kullanilan,
        C1REC.gb_free,
        C1REC.percent_used,
        C1REC.sdate);
        EXCEPTION
         WHEN OTHERS THEN
            ROLLBACK;
            SQL_ERROR_TEXT := SQLERRM||' (1) p_tablespace_kontrol ';
            INSERT INTO kamil.tablespace_kontrol_LOG(TARIH, HATA)
               VALUES(PAR_TARIH,
                            SUBSTR(SQL_ERROR_TEXT,1,256));
            COMMIT;
       end;
   END LOOP;
insert into kamil.TABLESPACE_KONTROL_LOG (tarih, hata)  values (sysdate, 'işlendi');
COMMIT;
    begin
UPDATE kamil.tablespace_kontrol SET TOLERANS_GB = 0  WHERE TABLESPACE_NAME =  'UNDOTBS1'  and tolerans_gb is null;
UPDATE kamil.tablespace_kontrol SET TOLERANS_GB = 1  WHERE TABLESPACE_NAME =  'SYSAUX'  and tolerans_gb is null;
UPDATE kamil.tablespace_kontrol SET TOLERANS_GB = 10  WHERE TABLESPACE_NAME =  'USERS'  and tolerans_gb is null;
UPDATE kamil.tablespace_kontrol SET TOLERANS_GB = 0  WHERE TABLESPACE_NAME =  'TEMP'  and tolerans_gb is null;
UPDATE kamil.tablespace_kontrol SET TOLERANS_GB = 1  WHERE TABLESPACE_NAME =  'STUDY1'  and tolerans_gb is null;
UPDATE kamil.tablespace_kontrol SET TOLERANS_GB = 5  WHERE TABLESPACE_NAME =  'SMALL'  and tolerans_gb is null;
UPDATE kamil.tablespace_kontrol SET TOLERANS_GB = 5  WHERE TABLESPACE_NAME =  'RMAN_PROD'  and tolerans_gb is null;
UPDATE kamil.tablespace_kontrol SET TOLERANS_GB = 2  WHERE TABLESPACE_NAME =  'TEST'  and tolerans_gb is null;
commit;
UPDATE kamil.tablespace_kontrol SET AKSIYON = 'DATAFILE EKLE TOLERANSIN ALTINA DÜŞMÜŞ' WHERE gb_free < TOLERANS_GB AND AKSIYON IS NULL ;
UPDATE kamil.tablespace_kontrol SET AKSIYON = 'DATAFILE EKLEMEYE GEREK YOK' WHERE gb_free >= TOLERANS_GB AND AKSIYON IS NULL ;
commit;
update kamil.tablespace_kontrol z set gunluk_buyume_gb = (
SELECT (son_durum - onceki_durum) gunluk_buyume_gb
  FROM (SELECT tablespace_name, gb_kullanilan son_durum
          FROM tablespace_kontrol
         WHERE TRUNC (sdate) = TRUNC (SYSDATE)) a,
       (SELECT tablespace_name, gb_kullanilan onceki_durum
          FROM tablespace_kontrol
         WHERE TRUNC (sdate) = TRUNC (SYSDATE - 1)) b
     where a.tablespace_name = b.tablespace_name
and  z.tablespace_name = a.tablespace_name
and  z.tablespace_name = b.tablespace_name
)
where trunc(z.sdate) = trunc(sysdate);
commit;
   END;
 end;
/
Procedure created.


-- bir gün için tablomuzu dolduralım, 
exec KAMIL.P_TABLESPACE_KONTROL(sysdate) ;
PL/SQL procedure successfully completed.


-- Gelen değerleri select edelim, 
select * from kamil.tablespace_kontrol where sdate = trunc(sysdate)  ;  


-- log tablomuzu kontrol edelim, 
select * from tablespace_kontrol_log ;


Umarım faydalı olmuştur.

Kamil TÜRKYILMAZ

www.kamilturkyilmaz.com