Bilinen Adıyla LOGON TRIGGER

Güvenlik anlamında bir adım daha ileri giderek sisteme  connect olan bazı userların sadece bir (veya birkaç ip’ den) gelmesini istiyorsunuz. Veya zaten birkaç ip’ den geliyordur siz bunların dışındaki olası girişlerin önüne geçmek isteyebilirsiniz. Aşağıdaki trigger da aynen bu işlemi gerçekleştirmektedir. Belirlenmiş olan kullanıcıların hangi ip adresinden geldiğine bakıp izin verilen adres ise connect’ inı kurmasını sağlayıp, farklı bir ip (bizim tanımlamadığımız bir ip)  adresinden ise kullanıcıya hata dönüp sisteme connection kurmasına izin vermemektedir. 
Continue reading

Tablodaki Herhangi Bir Alanı Random Bir Değerle Doldurmak

Bugün bir arkadaşıma, kullanmakta oldukları bir tablodaki number olan bir alanı birbirinden farklı değerlerle doldurmaları gibi bir gereksinimleri olmuş. Bende burdan yola çıkarak kapsamı biraz daha genişleterek number, string ve date alanları için birbirinden farklı olarak değerlerle bu alanları nasıl doldurabiliriz diye ufak bir çalışma yaptım.

Şimdi çalışmamız için örnek bir tablo oluşturalım.
Continue reading

ORA-30036: unable to extend segment by 8 in undo tablespace

Zaman zaman database’ de tablolara yüklü miktarda insert işlemleri olur. Özellikle bahsettiğimiz bir raporlama database’  ise bu işlem kaçınılmaz olur ve yapılan işlemin boyutu undo tablespace’ inizin boyutundan fazla olduğunda (undo tbs autoextend olmadığını varsayarsak)   “ORA-30036: unable to extend segment by 8 in undo tablespace ‘UNDOTBS2′  hatasını almanız kaçınılmaz olacaktır.  Ora-30036 hatasının önüne geçmek için yapılan insert işlemini parçalamak ve daha ufak bölümlerde yapmak sizin için kaçınılmaz bir çözüm olacaktır. Bu işlemi de yaparken tablo içerisindeki bir alana göre bir den fazla where conditionı ile sorgu yazmak yerine tek sorgu ile ama belirli sayıda data üzerinde sırayla işlem yaparak undo’ ya fazla yüklenmeden tabiri caizse patlatmadan işleminizi gerçekleştirebilirsiniz. Örnek olması açısından şöyle bir bilgi verebilirim, raporlama database’ lerimizden birinde yer alan 54 gb bir datayı farklı bir schema altına aşağıdaki script ile yaklaşık 1 saat gibi bir sürede taşımıştım. (Aşağıda belirtmiş olduğum tuning yöntemlerinden hiçbirini kullanmadan) Tabi bu süreyi etkileyen diğer faktörleride göz önünde bulundurmak lazım. (işlemin yapıldığı andaki database deki yoğunluk, kullanılan server’ ın kapasitesi, taşınan tablespace’ lere ait dbf’ ler üzerindeki o anki i/o durumu vs.)
Continue reading

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

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.