Oracle Veritabanı: Pivot ve Unpivot Tablolar

Pivot 

Normal tablolarda bilgiler sütun-değer çiftleri şeklinde tutulmaktadır.

Örnek olarak Scott örnek şemasındaki emp tablosunu kullanabiliriz. Gerçi pivot işlemlerini bütün tablolarda uygulayabiliriz.

Bu tabloda her çalışanın işi, adı soyadı, departman numarası gibi bilgiler tutulmaktadır.

Burada her departman da kaç tane kullanıcının bulunuduğunu görmek için aşağıdaki gibi bir sorgu yazabiliriz.

Departman başına düşen toplam kullanıcıyı bulsak da hangi kullanıcıların hangi departmanda olduğunu temiz bir şekilde göremiyoruz. Bize isim bazında hangi departmana ait olduklarını gösteren bir rapor görmek istersek aşağıdaki gibi bir pivot tablo sorgusu uygulayabiliriz.

for deptno in (...) ifadesi değerlerimizin bunlara kısıtlanmasını sağlar. Dikkat edilmesi gereken başka bir konuda 4 sütunu burada bulunmasıdır. Bu da demek oluyorki tablonun tamamının transposesi alınmamaktadır. Sadece bir kısmının değerleri yukarıya çıkartılırken buna göre veriler gösterilmektedir.

Özet Olarak:

Yukarıda crosstab raporu hazırlamış olduk. 2.resimdeki verileri açıp  isim bazında hangi gruplara ait olduklarını göstermiş olduk. Eskiden departman no'ları satır verileri olarak gösterilirken şimdi başlık olarak kolonlarda gözükmektedir. Bir tablonun transpose edilmesi de denilebilinir.


Unpivot:

Unpivot ile pivot olarak yaratılmış tabloları eski haline getiririz.

Örnek olarak tablomuzu 3.resimdeki gibi pivot olarak yarattığımızı düşünelim.

create table deneme as select * from (select ename,deptno from scott.emp) pivot (count(deptno) for deptno is (10,20,30));

Bu şekilde yarattıktan sonra 2. resimdeki sonuçları elde etmek için unpivot işlemi yapmamız gerekir. Bu durumda da aşağıdaki gibi bir sorgu örneği kullanabiliriz.

select * from deneme
unpivot
(
departments
for deptno in (10,20,30)
);

Oracle Veritabanı: SQL Trace

Session Trace'inin Açılması:

Şu anki session'ımız için SQL Trace açmak istiyorsak:

ALTER SESSION SET sql_trace = true; 

Trace'ini yazdığımız session'ın trace dosyasının daha belirgin olarak gözükmesi için ismine bir ayarda ekleyebiliriz.

ALTER SESSION SET sql_trace = true;  
 ALTER SESSION SET tracefile_identifier = mysqltrace; 

Session Trace'inin Kapatılması:

ALTER SESSION SET sql_trace = false;

Başka Session'lar İçin Trace Açılması:

Bunun için ilk olarak problemli session'ın SID ve SERIAL# 'ının belirlenmesi gerekir. Bunu da v$session tablosundan görebiliriz.

Örnek SID=11,Serial#=14907

ALTER SYSTEM SET timed_statistics = true;  
Execute dbms_system.set_sql_trace_in_session(11, 14907, true);

Kullanıcı kendi session'ınında SQL'ini çalıştırdıktan sonra da kapatılır.

 execute dbms_system.set_sql_trace_in_session(11, 14907 , false);

Başkasının trace dosyasının bulunması için o kullanıcının sid ve serial$'Inın bilinmesi gerekir.

select tracefile from v$session join v$process on (addr=paddr) and audsid=(select audsid from v$session where sid=&a and serial#=&b);

Bu örneğimiz için SID=11,Serial#=14907 kullanmaktayız, ancak başka database'lerde farklı değerler kullanabiliriz.


Trace Dosyalarının Büyüklükleri:

Standart büyüklük işletim sistemin block size'ıdır. Yani 4mb'dır. Bununla birlikte MAX_DUMP_FILE_SIZE  parametresini değiştirirsek trace dosyalarının büyüklüklerini değiştirebiliriz. Trace sırasında dosyanın bölünmemesi için dosyanın yeterince büyük olması gerekmektedir.

http://berkeoz1.blogspot.com.tr/2014/01/oracle-veritaban-oracle-error-ora-48913.html

Trace Dosyalarını Formatlanması

Bunun için TK*Prof  aracını kullanabiliriz.

TK*Prof Kullanımı:
http://berkeoz1.blogspot.com.tr/2013/11/oracle-veritaban-tkprof-tkprof-nedir.html



Referans:
http://berkeoz1.blogspot.com.tr/2014/01/oracle-veritaban-oracle-error-ora-48913.html
http://berkeoz1.blogspot.com.tr/2013/11/oracle-veritaban-tkprof-tkprof-nedir.html

Oracle Veritabanı: Synonym

SYNONYM:

Oracle synonym'leri belirli bir nesneyi işaret eden ve buna ya aynı adı ya da istediğimiz bir adı verebilmemiz sağlayan objelerdir. Synonym'ler ile tablo, view, sequence, procedure, stored function, package, materialized view, Java class schema object gibi bütün veritabanı nesnelerini işaret eden objeleri oluşturabiliriz.

Synonym'ler ile hangi schemada bulunursak bulunalım, tablo ismine bağlı kalmadan istediğimiz verilere erişebiliriz. Uygulamaların veriyi değiştirmeden çalışması için çok uygundurlar. Verinin sahibinin veya bulunduğu database veya schema'nın nerede olduğuna bakılmaksızın uygun haklar verildikten sonra gerekn programlar tarafından kullanılabilinirler.

Gereken Yetkiler:

Kendi şemamızda synonym yaratmak için "CREATE SYNONYM' yetkisi
Başkalarının şemasında synonym yaratmak için "CREATE ANY SYNONYM" yetkisi
Genel bir synonym yaratmak için "CREATE PUBLIC SYNONYM" yetkisi. Public synonym'i bütün kullanıcılara açıktır. Ancak synonym kullanabilmek ile onun datalarına erişmek farklı olaylardır. Bunun için o nesne üzerine aynı zamanda select yetkisi de verilmelidir.(Farklı bir işlem yapılmayacağı varsayılırsa)

Syntax:

Create or replace synonym schema.synonym for schema.object@dblink;

dblink ifadesi nesne sadece uzaktaki bir veritabanındaysa kullanılır.
or replace ifadesi var olanı değiştirmek için kullanılır.
For ifadesi de hangi veritabanı nesnesi için olacağını gösterir.

Synonym'leri Nereden Kontrol Edebiliriz?

user_synonyms
all_synonyms
dba_synonyms;

Synonym'leri Nasıl Kaldırırız?

drop synonym synonym_adı;

Not: Olmayan nesneler için de synonym'ler yaratılabilinir. Synonym'lerle eşli tablolar synonym'i etkilemeden kaldırılabilinir.

Referans:

Synonym'ler - http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_7001.htm

Oracle Veritabanı: Index Organized Tables

Index Organized Table(IOT) Nedir?

Index Organized Table normal bir tablonun aksine, kendi yapısına sahip bir tablodur. Row id'si yoktur.
Tablosuz bir index gibi düşünülebilinir. Normal bir tablo bir sürü veriyi sırasız bir şekilde tutat.  IOT tabloları verileri B-tree index yapısı içerisinde tutar. Normal bir tabloda her satırın belirli bir yeri varken, IOT bazlı tablolarda satırların belirli bir yeri yoktur. IOT'da datalar sıralı bir şekilde tutulurlar. Zamanı geldiğinde bu satırların yerleri değişebilir. Herhangi bir satır insert edildiğinde var olan bir satırın yerinin değişmesine neden olabilir.


Index Organized Table Avantajları:

Index bazlı tablolarda  satırlar primary key bazlı tutulmaktadır. Buna göre

-Tablolardaki verilere primary key üzerinden arama yapılığında hızlı erişim sağlayabiliriz. IOT tablolarında I/O  operasyonları minimize edilir. Sürekli rowid'ye bakılmaz.

-Çok erişilen tablolar için avantajlıdır. IOT bazlı tabloları online olarak reorganize edebiliriz.

-Yer olarak kazanım sağlar. Rowid'ler için fazladan yer gerekmez; çünkü rowid'ler kayıtlı olarak tutulmaz.

Index Organized Table Yaratma Örneği: 

 CREATE TABLE calisanlar  
   ( isim      CHAR(20),  
    yas     NUMBER
   )segment creation immediate  

 ORGANIZATION INDEX TABLESPACE IND_TBS;  

Tablespace ifadesi opsiyoneldir.  Eğer kullanıcının bulunduğu tablespace'de yaratılmayacaksa  kullanılı Segment Creation ifadesi de hemen dictionary'de gözükmesi için gereklidir. Eğer yazılmassa tabloya veri atılana kadar IOT tablomuz  gözükmez.

Index Organized Table Üzerinde DML:

IOT yapılı tablolarda aynen sıradan tablolardaki gibi değişitirilebilinirler.

INSERT INTO calisanlar VALUES ('Berke',20);  
 SELECT * FROM calisanlar;  
 UPDATE calisanlar SET isim = 'AHMET' WHERE isim = 'Berke';  
 DELETE FROM calisanlar WHERE yas = 20;

Oracle Veritabanı: Regular Expressions - Düzenli İfadeler

Regular Expression'lar Nedir?

Regular expression'lar  string ifadeler içerisinde aramak oluşturulan şablonlardır. Bu şekilde kompleks şablonlar oluşturulabilinir.

Örnek:

 select * from apps.fnd_user where regexp_like(user_name,'B');

İçinde B harfi bulunan kullanıcıları görmek için regexp_like fonksiyonunda user_name sütununda B harfini aratabiliriz.

select * from apps.fnd_user where regexp_like(user_name,'B(E|C)');

İçerisinde B harfi olup da bu B harfini E veya C ile takip eden kullanıcıları görmek için 'B(E|C)'  ifadesini kullanırız. Böylece ismi içerisinde BE veya BC olan kullanıcıları görebiliriz.

Veritabanındaki Regexp SQL Fonksiyonları:

Regexp_like: Bir tablonun kolonunda bir karakter dizisi aramak için where ifadesinden sonra kullanılır. Yukarıdaki örnekteki gibi regexp içerisinde şartlar sıralandıktan sonra kullanılır.

Syntax:http://docs.oracle.com/cd/B12037_01/server.101/b10759/conditions018.htm#SQLRF00501

Regexp_replace: Regexp_like gibi çalışan ama karşılaştığı her ifadeyi fonksiyon içerisinde belirtilen başka bir ifadeyle değiştirir.

Syntax:http://docs.oracle.com/cd/B12037_01/server.101/b10759/functions115.htm#SQLRF06302

Regexp_instr:  Aranacak karakter şablonunun nereden itibaren aranmaya başlanacağını gösterir.

Syntax:http://docs.oracle.com/cd/B12037_01/server.101/b10759/functions114.htm#SQLRF06300

Regexp_substr: Substr fonksiyonuyla karşılaşılan kelime dizilerini sütunların içindeki verilerden çıkarabiliriz.

Syntax:http://docs.oracle.com/cd/B12037_01/server.101/b10759/functions116.htm#SQLRF06303

Regular Expression Oluşturan Karakter Şablonları:

Syntax	Operatör İsmi		Tanımı:
.	Nokta		Herhangi bir karakter
+	Artı		öncesinde gelen ifadeden bir veya birden fazla karşılaşılması
?	Soru işareti		Sıfır ya da bir kere öncesinde gelen ifadeyle karşılaşılması
*	Yıldız		Sıfır ya da birden fazla  öncesindeki ifadeyle karşılaşılması
{m}	Aralık		öncesinde gelen ifadeden m tane karşılaşılması durumu
{m,}	Aralık		öncesinde gelen ifadeden en az m tanesi ile karşılaşılması durumu
{m,n}	Aralık		öncesinde gelen ifadeden en az m en fazla n tanesi ile karşılaşılması durumu
[...]	Köşeli parantez		Parantezler içerisindeki kısımları eşler.
[^...]	Köşeli parantez		Parantezler içerisindekilerin dışındakileri eşler

Örnekler:

[a.z]: içinde a'dan z'ye kadar olaran bütün karakterler eşlenir. [^0-9] :içinde sayı olmayanların örneği Regular Expression'lar Oluşturmak: Dizilerimizi oluşturmak için ihtiyaçlarımıza göre ifadelerimizi oluştururuz. Örneğin arayacağımız sütunda "abc" dizilerini bulmak istiyorsak "abc"  şeklinde arama yapabiliriz. Nokta Karakteri: Nokta karakteri arama dizisi içerisindeki herhangi bir  karakteri ifade eder. Yani  "a.c" ile ifade edilen bir dizi ile "aac","abc","acc" vb bütün diziler aranır. Kısaca a ile başlayıp c ile biten bütün diziler aranır. + Karakteri: + ile öncesinde gelen ifadeden bir veya birden fazla defa karşılaşılması durumunda o değeri geri döndürür. Örnek olarak "a+" şeklinde arama yaparsak içinde "a","aa","aaa",... gibi bütün örnekleri eşler. ? Karakteri: ? ile bir karakterin hiç olmadığı ya da bir kere karşılaşıldığı durumları ararız. Örneğin "ab?c"  şeklinde arama yaptığımız aranacak olanlar kelime dizileri "ac" veya "abc"'dir. * Karakteri: * bize öncesinde gelen karakterin 0 ya da daha fazla karşılaşılma durumunu gösterir. Referanslar: http://psoug.org/reference/regexp.html http://docs.oracle.com/cd/B12037_01/appdev.101/b10795/adfns_re.htm http://turkceoracle.com/2011/03/oracle%E2%80%99da-duzenli-ifadeler-regular-expressions.html

Oracle Veritabanı: Storage Clauses - Depolama Seçenekleri

Veritabanı Yönetimi:

Veritabanının başlangıcından itibaren yaratılan nesnelerde nasıl depolama yapılacağının seçenekleri belirtilir. Bunu yarattığımız tablolarda, index'lerde, tablespace'lerde uygulayabiliriz. Ertesinde de, create ve alter komutlarıyla değişiklikler uygulanabilinir.

Storage İfadesi:

Storage ifadesinde değerlerin nasıl ve hangi değerlerle tutulacağını belirtiriz. Storage altında belirli parametreler vardır. Bu parametreleri aşağıdaki örnekte inceleyeceğiz.

 CREATE TABLE APPS.AMS_ACT_METRICS_MONTHLY_MV  
 (  
  TIME_ID    NUMBER             NOT NULL,  
  START_DATE  DATE,  
  END_DATE   DATE,  
  OBJECT_TYPE  VARCHAR2(30 BYTE),  
  OBJECT_ID   NUMBER,  
  METRIC_VALUE NUMBER,  
  METRIC_ID   NUMBER             NOT NULL  
 )  
 TABLESPACE APPS_TS_SUMMARY  
 RESULT_CACHE (MODE DEFAULT)  
 PCTUSED  0  
 PCTFREE  10  
 INITRANS  10  
 MAXTRANS  255  
 STORAGE  (  
       INITIAL     16K  
       NEXT       128K  
       MINEXTENTS    1  
       MAXEXTENTS    UNLIMITED  
       PCTINCREASE   0  
       BUFFER_POOL   DEFAULT  
       FLASH_CACHE   DEFAULT  
       CELL_FLASH_CACHE DEFAULT  
       )  
 LOGGING   
 NOCOMPRESS   
 NOCACHE  
 PARALLEL ( DEGREE DEFAULT INSTANCES DEFAULT )  
 MONITORING; 

Parametreler sırasıyla:

Initial: Yaratılacak nesnenin ilk  extent'inin ne kadar büyüklükte olacağını gösterir. Default değeri 5 Oracle block'udur. 8kb'lık blocksize'ı olan bir veritabanı için 40kb normal değerdir.

Next: Nesnenin initial block'tan sonraki ilk extent'inin boyutunu belirler. Bunun da default değeri 5  block'tur.
Bu değerin kullanılması için PCTINCREASE adlı parametre 0 olmalıdır. Eğer bu parametre 0'dan büyükse initial'dan sonraki extent Next ile belirlenen extent olur. Next ile belirlenen extent'den sonraki extent yani 3.extent Next ile belirlenen boyutun PCTINCREASE ile çarpılıp  Next ile toplanmış halidir. Yani PCTINCREASE 10 olsaydı 3. extent 128kb +(128*10)kb boyutunda olacaktı.

Minextents: Yaratılacak nesne için initial extent sayısını belirler.

Maxextents: Nesnenin sahip olabilieceği maksimum sayıdaki extent'i belirler. Unlimited olarak belirlenmesi tavsiye edilir.

Pctincrease: İnitial extent'den sonraki artış miktarını belirler.

Pctfree: Bir database bloğunda ileride gelebilecek update'ler için ne kadar boşluk bırakılması gerektiğini gösteren parametredir. Bu parametre sayesinde bir block belirtilen değere kadar doldurulur sonra da pctfree oranına göre yeni gelen datalar eklenmez. Ancak bu sütunlara yapılacak yeni değişiklikler(update'ler) bu alana yansıtılır.

select pct_free from dba_tables; sorgusuyla istenen tablonun alanı görülebilinir.


Referanslar:

http://docs.oracle.com/cd/B19306_01/server.102/b14200/clauses009.htm

Oracle Veritabanı: Bash Script İçerisinde SQL*PLUS ile Değer Girmek - Prompting For Input In Bash Script With SQL*Plus

Önceki yazımızda(Oracle Veritabanı: SQL*PLUS ve Script Etkileşimi - Interaction Between SQL*PLUS and Bash Shell Scripts - Execution of SQL Scripts) Bash Shell'de, SQL script'leriyle nasıl etkileşim haline geçebileceğimizi belirttik. Bu yazımızda da yazdığımız bir Bash Shell script'inin içinde çağırdığımız sql script'ine bir değer atamanın nasıl yapıldığını göstereceğiz.

--------------
#!/bin/bash
ls
sqlplus -s berke/berke <set serveroutput on;
declare
x char;
z char;
begin
select '&x' into z from dual;
dbms_output.put_line(z);
end;
/
exit;
EOF
ls
--------------

Yukarıdaki script içeriğimizde dual tablosundan bir select çekip orada da hangi değeri istediğimizi x değişkenine atıyoruz. Bu script'i çalıştırdığımızda ise bunu başaramıyoruz çünkü bize declare ile başlattığımız programımızın sonundaki yani "/" işaretinden sonraki ilk kelimeyi kendisine değer olarak almaktadır. Bu durumda x değişkenine exit kelimesi atanır.(x=exit)  Biz burada kendimize bir değer sorulmasını istiyorsak bunu yapmanın 2 yolu vardır. İlki sql kodunu burada yazmaktansa bir script olarak çalıştırmaktır.  2'si ise dışardan değer olarak almaktır.

1- SQL Script'ini Shell Script İçerisinden Çağırmak:

İlk aşamada bash script'imizi aşağıdaki gibi oluştururuz.

------------
#!/bin/bash
ls
sqlplus -s apps/apps @deneme.sql
-----------

Deneme.sql adlı SQL script'imizi bize girdi(input) sorması için aşağıdaki gibi oluştururuz.

------------------
set serveroutput on;
select '&x' from dual;
exit;
-----------------

Örnek çıktısı aşağıdaki gibidir.

------------------------
Enter value for x: 10
old   1: select '&x' from dual
new   1: select '10' from dual

'1
--
10
------------------------

2- Dışardan Değer Atamak:

Buradaki mantığımızda script'imizin içerisinde kullanacağımız değeri script'e girmeden önce belirleriz. sonrasında SQL*Plus'a bağlandıktan sonra "$"'ı kullanaraktan o parametremizin değerini işleyebiliriz.

------------------------
#!/bin/bash
echo "Deger giriniz: \c"
read y
echo "Degerimiz: "$y
sqlplus -s apps/apps << EOF
set heading off;
set feedback off;
select $y from dual;
exit;
EOF
------------------------


Örnek Çıktı:
------------------------
Deger giriniz \c
10
Degerimiz: 10

        10
------------------------
Modülerliği Arttırmak:

Script'imizi modüler yapmak içinse programımız tam bittiği anda değerlerimizi yerleştirebiliriz. Örneğin aşağıdaki programımızda 2 kere değer isteyen sonra da bu değerleri kullanan algoritma bulunmaktadır. Değişkenleri sürekli değiştirmektense programımızın sonuna program içerisinde herhangi bir yerde kullanılacak "a" ve "b" parametreleri için değerleri yerleştirebiliriz. Bu durumda a parametresinin değeri C  ve b parametresini değeri D olur.(a='C' ve b='D')

---------------
ls
sqlplus -s apps/apps <set serveroutput on;
declare
d varchar2(10);
f varchar2(10);
begin
select '&a' into f  from dual;
DBMS_OUTPUT.PUT_LINE(f);
select '&b' into d from dual;
dbms_output.put_line(d);
end;
/
C
D
exit;
EOF
---------------

Yukarıdaki script ile bash shell script'imizin içine değerlerde yapacağımız küçük değişikliklerle programımızın yönünü değiştirebiliriz.

Scriptimizin sonucu aşağıdaki gibidir.
---------------
Enter value for a: old   7: select '&a' into f  from dual;
new   7: select 'C' into f  from dual;
Enter value for b: old   9: select '&b' into d from dual;
new   9: select 'D' into d from dual;
C
D

PL/SQL procedure successfully completed.
---------------


Referanslar:

http://www.unix.com/shell-programming-scripting/24394-sqlplus-here-document-eof-vs-eof.html
http://www.oracle-base.com/articles/misc/oracle-shell-scripting.php

http://www.java2s.com/Tutorial/Oracle/0540__Function-Procedure-Packages/OutputtotheSQLplus.htm

Oracle Veritabanı: SQL*PLUS ve Script Etkileşimi - Interaction Between SQL*PLUS and Bash Shell Scripts - Execution of SQL Scripts

-Bir bash script'i içerisinden prosedür çağırmak için:

ls
sqlplus berke/berke << eof

select sysdate from dual;

declare
begin
dbms_output.put_line('abcde');
end;
/
exit;
/
eof
ls

Bu script'i çağırdığımızda aşağıdaki gibi bir sonucun çıktığını görebiliriz.  

 ./abcd.sh  
 abcd.sh     addm      awr       db_control.sh  
 abcd.sh1    addmrpt.sql   awrrpt.sql   db_control.sql  
   
 SQL*Plus: Release 11.2.0.2.0 Production on Thu Mar 20 15:22:32 2014  
   
 Copyright (c) 1982, 2010, Oracle. All rights reserved.  
   
   
 Connected to:  
 Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production  
 With the Partitioning, Automatic Storage Management, OLAP, Data Mining  
 and Real Application Testing options  
   
 SQL> SQL> SQL>   
 SYSDATE  
 ---------  
 20-MAR-14  
   
 SQL>  2  3  4  5 abcde  
   
 PL/SQL procedure successfully completed.  
   
 SQL> Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production  
 With the Partitioning, Automatic Storage Management, OLAP, Data Mining  
 and Real Application Testing options  
 abcd.sh     addm      awr       db_control.sh  
 abcd.sh1    addmrpt.sql   awrrpt.sql   db_control.sql  

Bu script'in amacı bir bash script içerisinden sql ile sonuç çıkartıp gerekli cevapları alıp devamında işletim sistemi komutlarını girebiliriz. Böyle bir durumla maintenance script'i yazarken karşılaşmıştım. RAM'de hafıza durumu, cpu durumu gibi kontrolleri yaptıktan sonra SQL ile SQL*PLUS'a bağlanıp oradan çeşitli komutlar çalıştırıp yine tekrar işletim sistemine geri dönmek için bu şekilde komut yazmam gerekmişti.

-Klasör içerisindeki SQL script'ini çalıştırmak için  kısaca script'imizin başına  '@' işaretini ekleyerek devam edebiliriz.

sqlplus berke/berke @abcd.sql  

-Sysadmin olarak sql script'inin bash'ten çalıştırılması için aşağıdaki örneğimizi kullanabiliriz. 

 sqlplus -s "/ as sysdba" @$ORACLE_HOME/rdbms/admin/awrrpt.sql 

-Sh içerisinden yani terminalden çok satırlı SQL çalışması:

Yukarıdaki örneğimizden farklı olarak  bu sefer terminalden giriş yapmak istediğimizde aşağıdaki kodu kullanabiliriz. Diğer örneğimizde kodumuzu bir text'e yazmaktaydık.


sqlplus berke/berke << -eof
select sysdate from dual;
exit;
/
eof

-SQL*PLUS'ta çalıştırdığımız bir sorgunun sonucunu  alıp bir değişkene atamak içinse aşağıdaki örneği kullanabiliriz. Burada "code" adlı değişkenimize değerimizi atamak için değerimizi sqlplus'tan gönderdiğimiz sorguyla atarız.


code=""
code=$(sqlplus -s berke/berke <set heading off;
select sysdate from dual where 1=2;
EOF)

a=$(code)
if [ "$a" == "no rows selected" ]; then
echo "Tarih çıkmadı"
else
echo "Tarih $a"

Oracle Veritabanı: External Tables - Harici Tablolar

Harici tablolar ile Oracle veritabanı dışında bulunan dosyalar okunabilinir ve sorgulanabilinir. Oracle_Loader driver'ı  dışarıdaki dosyaların formatlarının düzenlenmesiyle birlikte içerideki dataların okunmasını sağlar. Harici dosyalar üzerine DML gerçekleştirilemez. Bu tablolara veri eklenip çıkartılamaz. Üzerlerine index tanımlanamaz. Harici dosyalar üzerine view ve synonym'ler yaratılabilinir; çünkü daha önceden bu tabloların tanımları yapılmıştır.

Harici tabloların oluşturulmasından önce bu tabloların bulunduğu klasörün belirtilmesi gerekir. Yerleştirilecek klasör veritabanında metadata olarak belirtilmelidir. Bunu da aşağıdaki klasör yaratma komutuyla yaratabiliriz.

create or replace directory temp_dir as '/home/users/oracle';

Silmek için de drop directory klasör_ismi;

Harici tabloları depolayacağımız klasörler belirtildikten sonra bu tabloların veritabanındaki eşleniklerini yaratırız. Yani tablomuzun tanımını veritabanında oluştururuz.

 CREATE TABLE emp (  
  name   VARCHAR2(50),  
  surname VARCHAR2(50)  
 )  
 ORGANIZATION EXTERNAL (  
  TYPE ORACLE_LOADER  
  DEFAULT DIRECTORY temp_dir  
  ACCESS PARAMETERS (  
   RECORDS DELIMITED BY NEWLINE  
   FIELDS TERMINATED BY ','  
   MISSING FIELD VALUES ARE NULL  
   (  
    name   CHAR(50),  
    surname CHAR(50)  
   )  
  )  
  LOCATION ('emp.txt')  
 ) 

Tablomuzun oluşturulmasındaki adımlar şöyledir.

1-Veritabanında sorgulayacağımız kolonları belirleriz.
2-"Organization External ile harici tablomuzun özelliklerini belirleriz.
   -Tablomuzun bulunduğu klasör
   -Access Parameters yani kayıtların nasıl formatlandığı
   -Verilerin text dosyasından nasıl okunacağını belirten kısım
   -Harici tablonun bulunduğu işletim sistemindeki dosya adı. (Bu örneğimizdeki adı 'emp.txt'. Bu dosya /home/users/oracle altındadır.)

Örnek olarak aşağıdaki resimden de görebiliriz.

Dosyamız veritabanında yaratıldığı anda harici dosyamızın bulunduğu klasördeki log dosyası oluşturulur.

 more EMP_20250962.log   
   
   
  LOG file opened at 03/17/14 14:42:03  
   
 Field Definitions for table EMP  
  Record format DELIMITED BY NEWLINE  
  Data in file has same endianness as the platform  
  Rows with all null fields are accepted  
   
  Fields in Data Source:   
   
   NAME              CHAR (20)  
    Terminated by ","  
    Trim whitespace same as SQL Loader  
   SURNAME             CHAR (20)  
    Terminated by ","  
    Trim whitespace same as SQL Loader  
 EMP_20250962.log: END  

Harici tablomuzun bulunduğu yeri ve içeriğini aşağıdan görebiliriz.

 >pwd  
 /home/users/oracle  
 >more emp.txt   
 berke,oz  
 ali,veli  
 mehmet,ahmet 

Harici tablomuzun bulunduğu dosyayı silersek ya da yerini değiştirirsek ORA-29913 hatasını alırız.

 ORA-29913: ODCIEXTTABLEOPEN başvurusunu yürütme hatası  
 ORA-29400: veri kartuşu hatası  
 KUP-04040: file emp.txt in TEMP_DIR not found  

Bu hatayla dosyamızın bulunamadığını anlayabiliriz.

Harici dosyadan okunamayan veriler "bad file" adı verilen bir dosyaya yazılırlar. Bununla ilgili bir parametre de vardır. Aynı şekilde "log file"'ın nereye çıkarılabileceğini gösteren bir parametre de yer alır. Aşağıdaki gibi örneğimizde görebiliriz.

  CREATE TABLE emp (   
  name  VARCHAR2(50),   
  surname VARCHAR2(50)   
  )   
  ORGANIZATION EXTERNAL (   
  TYPE ORACLE_LOADER   
  DEFAULT DIRECTORY temp_dir   
  ACCESS PARAMETERS (   
   RECORDS DELIMITED BY NEWLINE   
  BADFILE bdump:'%a_%p.bad'  
  LOGFILE bdump:'%a_%p.log'  
   FIELDS TERMINATED BY ','   
   MISSING FIELD VALUES ARE NULL   
   (   
   name  CHAR(50),   
   surname CHAR(50)   
   )   
  )   
  LOCATION ('emp.txt')   
  ) 

Access Parametreleri:

Access parametreleri içerisinde harici tablo dosyasının nasıl formatlandığı yazılır. Harici dosyadan her sütun alınamayabilinir ya da data tipleri farklı olabilir.

 create table apps.emp   
 (name varchar2(20)  
 )  
  organization external  
  (  
  type ORACLE_LOADER  
  Default Directory temp_dir  
  Access Parameters   
  (  
   Records Delimited By Newline  
   Fields terminated by ','  
   Missing Field values are null  
   (name char(20),  
   surname char(20)  
   )  
  )  
  Location ('emp.txt')  
  )  
    
  ;  
  select * from emp; 

Tablonun sütun tanım kısmından "surname" tablosunu çıkartırsak dosyadan "surname " bilgileri alınmaz. Ancak aşağıdaki gibi bir örnek yaratırsak hata alırız. Çünkü tablodaki 2. sütunu belirtmememize rağmen 2.sütunu veritabanına tanımlayınca okunamayan sütun bilgisi hatası alırız.

 create table apps.emp   
 (name varchar2(20),surname  varchar2(20)  
 )  
  organization external  
  (  
  type ORACLE_LOADER  
  Default Directory temp_dir  
  Access Parameters   
  (  
   Records Delimited By Newline  
   Fields terminated by ','  
   Missing Field values are null  
   (name char(20)  
   )  
  )  
  Location ('emp.txt')  
  )  
    
  ;  

Delimited By:

"Delimited by" ile tablonun içerisindeki satırların nasıl ayrıldığını gösteririz. Bu satırlar çeşitli karakter ifadeleriyle ayrılabilinirler.

Referanslar:
External Tables: http://docs.oracle.com/cd/E11882_01/server.112/e25494/tables.htm#ADMIN12898

Oracle Veritabanı: ADDM Raporunun Otomatikleştirilmesi - Automating ADDM Report

Oracle veritabanı için yaptığımız günlük kontrollerde AWR raporunu otomatikleştirdikten sonra aynı şekilde ADDM raporunu da otomatikleştirmek istedim. Temelde aynı olmakla birlikte ADDM raporu çıkartıldığında CLOB kolonuna yazmaktadır. O yüzden CLOB kolonundan dosya yazdırmamız gerekmektedir.

Script'imizin ana hatları AWR raporunu otomatikleştirirkenki kullandığımız script'ten oluşmaktadır. Bununla beraber ADDM raprunun oluşturulması için task yaratılması, instance analizinin yapılması sonrasında da bunun bir dosyaya yazılması bulunmaktadır.

DECLARE   
  cursor c_instance is   
   SELECT instance_number, instance_name   
   FROM gv$instance   
   ORDER BY 1   
  ;   
     
  v_dir   CONSTANT VARCHAR2(256) := '/home/users/oracle';   
  --v_dir   VARCHAR2(256) ;   
     
  v_dbid  v$database.dbid%TYPE;   
  v_dbname  v$database.name%TYPE;   
  v_inst_num v$instance.instance_number%TYPE := 1;   
  v_begin  NUMBER;   
  v_end   NUMBER;   
  v_start_date VARCHAR2(20);   
  v_end_date VARCHAR2(20);   
  v_options  NUMBER := 8; -- 0=no options, 8=enable addm feature   
  v_file  UTL_FILE.file_type;  -- l_fHandler         UTL_FILE.FILE_TYPE;  
  v_file_name VARCHAR(50);   
  c_chunk          CONSTANT PLS_INTEGER := 32767;  
  l_pos           PLS_INTEGER := 1;  
  p_clob CLOB;  
  tname varchar2(100);  
  BEGIN   
  -- get database id   
  SELECT dbid, name   
   INTO v_dbid, v_dbname   
   FROM v$database;   
     
     
  -- get end snapshot id   
  SELECT MAX(snap_id)   
   INTO v_end   
   FROM dba_hist_snapshot   
   WHERE to_char(begin_interval_time,'HH24') = '18';   
  dbms_output.put_line('end snap_id '||v_end);   
     
  -- get start snapshot id   
  SELECT MAX(snap_id)   
   INTO v_begin   
   FROM dba_hist_snapshot   
   WHERE to_char(begin_interval_time,'HH24') = '07'   
   AND snap_id < v_end;   
  dbms_output.put_line('begin snap_id '||v_begin);   
      
  SELECT to_char(begin_interval_time,'YYMMDD_HH24MI')   
   INTO v_start_date   
   FROM dba_hist_snapshot   
   WHERE snap_id = v_begin   
   AND instance_number = v_inst_num   
  ;   
  dbms_output.put_line('v_start_date '||v_start_date);   
    
  SELECT to_char(begin_interval_time,'HH24MI')   
   INTO v_end_date   
   FROM dba_hist_snapshot   
   WHERE snap_id = v_end   
   AND instance_number = v_inst_num   
  ;    
  dbms_output.put_line('v_end_date '||v_end_date);   
     
     
  execute immediate('create or replace directory TEMP_DIR as '''||v_dir||'''');   
    
  FOR v_instance IN c_instance LOOP   
   dbms_output.put_line('v_instance.instance_name:'||v_instance.instance_name);   
    
  tname := 'ADDM Raporu';   
  DBMS_ADDM.ANALYZE_INST(tname, v_begin, v_end);  
        
  select dbms_addm.get_report(tname) into p_clob from dual;  
      
   clob_to_file('TEMP_DIR', 'addm_' || v_instance.instance_name ||'_'|| v_instance.instance_number || '_' || v_start_date || '_' || v_end_date || '.html',p_clob );  
   
    
   
   END LOOP;   
   
  dbms_addm.delete(tname);  
  execute immediate('drop directory TEMP_DIR');   
  EXCEPTION   
  WHEN OTHERS THEN   
   DBMS_OUTPUT.PUT_LINE(SQLERRM);   
   BEGIN   
   execute immediate('drop directory TEMP_DIR');   
   EXCEPTION   
   WHEN OTHERS THEN   
    null;   
   END;   
  END;   
  /  

v_instance döngüsünün amacı RAC ortamları için instance tespit etmektir.

ADDM raporunun yaratılmasını dbms_addm.analyze_inst ile yapmaktayız. Burada dbms_addm.analyze_inst yerine dbms_addm.analyze_db ya da başka tip addm raporları oluşturabiliriz.

tname := 'ADDM Raporu';   
  DBMS_ADDM.ANALYZE_INST(tname, v_begin, v_end);  
        
  select dbms_addm.get_report(tname) into p_clob from dual;  
      
   clob_to_file('temp_dir', 'addm_' || v_instance.instance_name ||'_'|| v_instance.instance_number || '_' || v_start_date || '_' || v_end_date || '.html',p_clob ); 

Dosya yaratma ve silme haklarını kullanıcımıza vermek için aşağıdaki sorguları kullanabiliriz.

 grant create any directory to (kullanıcı);  
 grant drop any directory to (kullanıcı);  

Buradaki "clob_to_file" prosedürü daha önceden yazdığımız "Oracle Veritabanı: Clob'u Dosyaya Yazdırmak - Writing a Clob Colum To An OS File " adlı yazımızda bulabilirsiniz.


Referans:
ADDM prosedürleri: http://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_addm.htm#CACDBJFG

Oracle Veritabanı: Clob'u Dosyaya Yazdırmak - Writing a Clob Colum To An OS File

Clob sütununu işletim sisteminde herhangi bir dosyaya yazdırmak için aşağıdaki prosedürü kullanabiliriz.

 CREATE OR REPLACE PROCEDURE clob_to_file  
 ( p_directory        IN VARCHAR2,  
  p_filename         IN VARCHAR2,  
  p_clob           IN CLOB ) IS  
   
  c_chunk          CONSTANT PLS_INTEGER := 32767;  
  l_fHandler         UTL_FILE.FILE_TYPE;  
  l_pos           PLS_INTEGER := 1;  
   
 BEGIN  
   
  l_fHandler := UTL_FILE.FOPEN(p_directory, p_filename, 'W', c_chunk);  
  WHILE l_pos < DBMS_LOB.GETLENGTH(p_clob) LOOP  
   UTL_FILE.PUT(l_fHandler, DBMS_LOB.SUBSTR(p_clob, c_chunk, l_pos));  
   l_pos := l_pos + c_chunk;  
   UTL_FILE.FFLUSH(l_fHandler);  
  END LOOP;  
  UTL_FILE.FCLOSE(l_fHandler);  
   
 END;  
 /  

Dikkat edilmesi gereken konular ise p_directory olarak belirttiğimiz varchar2 değişkeni veritabanında yarattığımız bir klasör adına denk gelmelidir. "p_clob"'da clob tipinde bir değişkendir. Örnek olarak klasör  böyle yaratılabilinir.

 create or replace directory temp_dir as '/home/users/oracle'; 

Oracle Veritabanı: AWR Raporunun Otomatikleştirilmesi - Automating AWR Report

AWR raporlarının nasıl çalıştırıldığını daha önce de görmüştük. $ORACLE_HOME/rdbms/admin altındaki awrrpt.sql scriptini çalıştırarak raporumuzu oluşturabiliriz. Bununla birlikte raporumuzu oluştururken bizden bazı girdileri isteyecektir. İnteraktif olarak bunları girmek yerine direk bu girdileri verebilirsek işimizi otomatize etmiş oluruz.

Otomatikleştirme işleminin yararı ise snapshot ayarlarından bağımsız olarak istediğimiz kadar raporları tutmuş oluruz. Raporları tutmaklada kalmayıp bunun analiz edilmiş çıktısını da saklamış oluruz. Böylece ileriki zamanlarda raporlara ihtiyaç duyduğumuzda veya çeşitli bilgiler için analiz etmek istediğimizde bu raporlar elimizin altında olacaktır.

Belirtilen script'i damir-vadas.blogspot.com'da bulabilirsiniz. Bu script'e günlük kontrollerim sırasında ihtiyacım olduğu için gerek duydum. Böylece günlük olarak hem iş yükünü hem de karşılaşılan sorunları görebilecektim.

Script'de Neler Yapılmakta?

Script  içerisinde raporumuza koyacağımız bilgileri belirlenmektedir. Örneğin DBID'si, Snapshot id'sini gibi bilgileri alınmaktadır. Snapshot bilgilerini alırken de 07-18 saat dilimleri arası alınmaktadır. Sonrasında v_dir değişkeniyle belirtilen klasörün tanımı veritabanına yapılır. Bu tanım ALL_DIRECTORIES tablosundan görülebilinir.

select * from all_directories  

Ardından da dosya açılır ve rapor dosyanın içine yazdırılır.  Script içine fazladan dbms_output.put_line('1'); gibi komutlar eklenmiştir. Bu script'in ortam değişikliği durumunda debug edilmesi, karşılaşılan hataların hangi aşamada görülmesini sağlamak içi konulmuştur.

Script çalıştırılırken klasör adına, snapshot aralığına ve gerekli yetkilere dikkat edilmelidir.

set serveroutput on  
 set linesize 166  
 set pagesize 600  
 set trimout on  
   
 DECLARE  
  cursor c_instance is  
   SELECT instance_number, instance_name  
   FROM  gv$instance  
   ORDER BY 1  
  ;  
   
  v_dir     CONSTANT VARCHAR2(256) := '/home/users/oracle';  
  --v_dir     VARCHAR2(256) ;  
    
  v_dbid    v$database.dbid%TYPE;  
  v_dbname   v$database.name%TYPE;  
  v_inst_num  v$instance.instance_number%TYPE := 1;  
  v_begin    NUMBER;  
  v_end     NUMBER;  
  v_start_date VARCHAR2(20);  
  v_end_date  VARCHAR2(20);  
  v_options   NUMBER := 8; -- 0=no options, 8=enable addm feature  
  v_file    UTL_FILE.file_type;  
  v_file_name  VARCHAR(50);  
   
 BEGIN  
  -- get database id  
  SELECT dbid, name  
   INTO v_dbid, v_dbname  
   FROM v$database;  
   dbms_output.put_line('1');  
   
  -- get end snapshot id  
  SELECT MAX(snap_id)  
   INTO v_end  
   FROM dba_hist_snapshot  
   WHERE to_char(begin_interval_time,'HH24') = '18';  
  dbms_output.put_line('end snap_id '||v_end);  
  dbms_output.put_line('2');  
  -- get start snapshot id  
  SELECT MAX(snap_id)  
   INTO v_begin  
   FROM dba_hist_snapshot  
   WHERE to_char(begin_interval_time,'HH24') = '07'  
    AND snap_id < v_end;  
  dbms_output.put_line('begin snap_id '||v_begin);  
    dbms_output.put_line('3');  
  SELECT to_char(begin_interval_time,'YYMMDD_HH24MI')  
   INTO v_start_date  
   FROM dba_hist_snapshot  
   WHERE snap_id = v_begin  
    AND instance_number = v_inst_num  
  ;  
  dbms_output.put_line('v_start_date '||v_start_date);  
  dbms_output.put_line('4');  
  SELECT to_char(begin_interval_time,'HH24MI')  
   INTO v_end_date  
   FROM dba_hist_snapshot  
   WHERE snap_id = v_end  
    AND instance_number = v_inst_num  
  ; dbms_output.put_line('5');  
  dbms_output.put_line('v_end_date '||v_end_date);  
    
  -- Thanx to Yu Denis Sun - we must have directory defined as v_dir value!  
  execute immediate('create or replace directory xx_some_temp_dir as '''||v_dir||'''');  
   dbms_output.put_line('6');  
  -- let's go to real work...write awrs to files...   
  FOR v_instance IN c_instance LOOP  
   dbms_output.put_line('v_instance.instance_name:'||v_instance.instance_name);  
   v_file := UTL_FILE.fopen('XX_SOME_TEMP_DIR', 'awr_' || v_instance.instance_name ||'_'|| v_instance.instance_number || '_' || v_start_date || '_' || v_end_date || '.html', 'w', 32767);  
   FOR c_report IN (  
    SELECT output  
     FROM TABLE(dbms_workload_repository.awr_report_html( v_dbid,  
                                v_instance.instance_number,  
                                v_begin,  
                                v_end,  
                                v_options  
                               )  
          )  
   ) LOOP  
    UTL_FILE.PUT_LINE(v_file, c_report.output);  
   END LOOP;  
   UTL_FILE.fclose(v_file);  
   dbms_output.put_line('7');  
  END LOOP;  
   dbms_output.put_line('8');  
  execute immediate('drop directory xx_some_temp_dir');  
  dbms_output.put_line('9');  
 EXCEPTION  
  WHEN OTHERS THEN  
   DBMS_OUTPUT.PUT_LINE(SQLERRM);  
    dbms_output.put_line('10');  
   IF UTL_FILE.is_open(v_file) THEN  
    UTL_FILE.fclose(v_file);  
   END IF;  
   BEGIN  
    execute immediate('drop directory xx_some_temp_dir');  
   EXCEPTION  
    WHEN OTHERS THEN  
     null;  
   END;  
 END;  
 /

Burada günlük olarak çalışma koşulu max(snap_id) ve begin_interval_time koşulu ile sağlanmaktadır. Böylece saat 18 snapshot'ı doğal olarak en son gün için yüksek olacağı hep o günün AWR raporu alınacaktır.

Aşağıda yazdığım kod ise $HOME dizininde raporun bir benzeri varsa çıkmakta, yoksa da awrrpt.sql scriptini çalıştırmaktadır.

 cd ;  
 x=$(date +"%y%m%d");  
 ls *$x* >> /dev/null 2>&1;  
 if [ $? != 0 ];  
 then  
  sqlplus -s "/ as sysdba" @$ORACLE_HOME/rdbms/admin/awrrpt.sql;  
 else  
  exit;  
   
 fi 

Referans:
1-AWR Raporunun Otomatik Çalıştırılması Script :http://damir-vadas.blogspot.com.tr/2009/11/automated-awr-reports-in-oracle-10g11g.html

Oracle: Pearson Vue`dan Oracle Sinavi Nasil Satin Alinir? - How To Buy An Oracle Exam From Pearson Vue?

Oracle sinavlarina girmek icin once Pearson Vue sitesinde bir hesap acariz.  Sitemizin adi: http://www.pearsonvue.com/

Burada hesabimizi actiktan sonra ornek olarak SQL Fundamentals sinavinin nasil alindigini gosterecegiz.

Ilk adimda sitemize gireriz ve burada `Sign In` secenegini seceriz.
   

`Sign in` secenegini sectikten sonra istedigimiz sinavin alanini ve markasini seceriz. Bizim icin bu sol tarafta IT ve sag tarafta da Oracle secenegidir.

Bu islemi de gerceklestirince karsimiza asagidaki ekran gelir. Buradan da istegimize gore `non-proctered` veya `proctered` secenegini tiklariz. `non-proctered` olan gozlemlenmeyen olandir.

Secimimizi yaptigimizda da hesap bilgileri bizden istenir. Bu bilgileri girdikten sonra  asagidaki gibi 1Z0-051-ENU-ONLINE opsiyonunu secerekten sinav satin alinma yolunda ilerlenir.

Oracle Veritabanı: Initialization Parameters - Başlangıç Parametreleri - Pfile - Spfile

Oracle instance'ı başladığında ilk önce initialization parametreleri ve parametre dosyası okunur. Bu dosyada en azından DB_NAME ifadesi bulunmalıdır. Diğer bütün parametrelerin kendi default standart değerleri bulunmaktadır.

Parametre dosyası bir text dosyası olabilir, pfile olabilir ya da spfile dosyası olabilir. Spfile dosyası bir binary dosyadır. Text dosyası şeklinde editlenemez. Veritabanının okuyacağı bir dosyadır. Kendimiz yaratabileceğimiz gibi DBCA aracı ile de yaratabiliriz. Yarattıktan sonra da buradaki parametreleri "alter system"  komutlarıyla düzeltebiliriz.

Oracle instance'ı başladığında ilk aranan şey binary parametre dosyasıdır. Bu binary parametre dosyasını bulamazsa, düzenlenebilir text dosyasını arar. Aradığı lokasyon ise $ORACLE_HOME/dbs altındadır. Pfile, yani text dosyasının ismi init$ORACLE_SID.ora şeklinde olur. Aynı şekilde spfile dosyasının adı da spfile$ORACLE_SID.ora'dır.

Veritabanındaki Belli Başlı Parametreler:

DB_NAME:

DB_NAME parametresinin büyüklüğü en fazla 8 karakter olabilir. Veritabanı yaratılırken bu isim data file'larına, redolog dosyalarına, control file'lara yazılır.

DB_DOMAIN:

DB_DOMAIN değeri network domain'ini belirtir.

FRA(Fast Recovery Area)'nın Belirlenmesi:

FRA Oracle veritabanının backup'la ilgili dosyaları tutup sakladığı yerdir. Veritabanıyla ilgili dosyaların tutulduğu yerden farklıdır. Veritabanıyla ilgili dosyalar derken data file, control file ve online redo log dosyalarından bahsetmekteyiz. Oracle FRA alanının kullanılmasını tavsiye eder. Fast Recovery Area ile ilgili parametreler.:

1-DB_RECOVERY_FILE_DEST:  

FRA'nın yerinin belirlenmesi için bir parametre bulunmaktadır. Bu parametre bir klasörü gösteriyor olabilir, bir dosya sistemini işaret ediyor olabilir ya da ASM'de bir klasör olabilir.

2-DB_RECOVERY_FILE_DEST_SIZE:

FRA için korunacak toplam bytes cinsinden alanı gösterir.  DB_RECOVERY_FILE_DEST parametresinden önce set edilmelidir. Çünkü DB_RECOVERY_FILE_DEST'de yeterince yer olmayabilir. Buna göre yeterli alan olup olmadığı kontrol edilir.

Control Files'ların Belirlenmesi:

Control files parametresi veritabanının kullanacağı parametreleri göstermektedir. Oracle en az 2 tane control file kullanılmasını ve bunların ayrı yerlerde depolanmasını tavsiye etmektedir.

DB_BLOCK_SIZE:

DB_BLOCK_SIZE olarak en genel kullanılan block büyüklüğü kullanılmalıdır. Genelde 8k kullanılmaktadır. Block size'ın önemi, block'lar ne kadar büyük olursa tek block'ta o kadar fazla veri tutulabilinir. Bu da tek seferde daha fazla verinin okunmasına, disk ve memory I/O'sundan tasarruf edilmesini sağlar.

Block Size'ı farklı olan tablespace'ler yaratılabilse de bunlar için SGA'de daha küçük boyutlu block cache'leri yaratılması gerekir.

PROCESSES:

Processes parametresi işletim sisteminden bağlanabililinecek process sayısını ifade eder. Bunun için minimum değer herbir Oracle Veritabanı background process'i için en az bir tane ve user process'leri için de bir tane ayrılması gerekir.

DDL_LOCK_TIMEOUT:

DDL(Data Definition Language) ifadeleri veritabanında belirli yapılar üzerinde işlem yapılmasını engellemektedir. Bu yüzden yapıların üzerine lock koymaktadır. Eğer bu lock'ı koyamazsa DDL ifadesi fail eder. DDL ifadelerinin bu tür durumlara karşı korunması için DDL_LOCK_TIMEOUT parametresi set edilebilinir. Bu parametrede DDL ifadesinin bu lock'ları elde edenee kadar ne kadar bekleyeceğini gösterir.

Undo Space Yönetimi

1-UNDO_MANAGEMENT:

Undo_management ile sistemin automatic_undo_management kullanarak mı başlayacağını belirtmektedir. Undo tablespace'inde undo datalarıyla ilgili bilgiler bulunur. Çoğunlukla commit edilmemiş bilgiler yer alır. Bu parametre auto'ya çekilerekten sistem automatic undo management açılır.

2-UNDO_TABLESPACE:

Bir instance automatic_undo_management ile açıldığında otomatik olarak undo tablespace arayacaktır. Undo  data'ları bir yerde tutması gerektiği için undo tablespace veritabanı yaratılırken default olarak üretilen undo tablespace'idir. Bunun dışında ayrı bir yerde undo tablespace yaratmak için önemli bir parametredir.  

COMPATIBLE: 

Compatible parametresi ile veritabanının uygunluk seviyesi belirlenir. Eğer veritabanının versiyonu 11.2'yken 10.0.0 olarak belirlenirse 11.2 ile gelen yeni özellikler devredışı bırakılırlar. Veritabanının uygunluk parametresi yükseltilebilinirken daha düşük seviyeye çekilemez.

Parametrelerin SPFILE ile Saklanması:

Oracle veritabanı için parametreler genellikle text dosyalarında tutulmaktadır ancak parametreleri daha düzenli olarak tutmak istersek binary dosyalarda tutabiliriz. Burada parametreler veritabanının başlamasında ve kapanmasında sabi kalırken; text dosyasında yapılan değişiklikler instance bazındadır.

SPFILE Yaratılması:

Spfile yaratmak için var olan pfile dosyası yani text dosyamız kullanılır. Bunun için sysdba yetkisi gerekmektedir. Spfile'ı yaratmak için instance'ın açık veya kapalı olmasına gerek yoktur. Başta pfile ile açıldıysa instance, spfile yaratılıp restart edildikten sonra, veritabanı spfile dosyasını tekrar bulur.

create spfile from pfile;  

Parametre Değerlerinin Değiştirilmesi:

"Alter System" ifadesiyle parametreler değiştirilebilinir. Eğer text dosyasını parametre dosyası olarak kullanıyorsak "alter system" ifadesi parametrelerin sadece instance bazında değiştirilmesini sağlar; çünkü sistemden verilen komutun text dosyasını değiştirmesi gbi bir süreç yoktur. Bu yüzden eğer text dosyası kullanılıyorsa parametreler manuel olarak instance kapandığında değiştirilmelidir.

2 çeşit parametre sistemde bulunmaktadır. Dinamik parametreler, instance açıkken değiştirilebilinirler.
Statik parametreler ise değiştirildiklerinde hemen etki etmezler. Bunların etki etmesi için instance restart'ı gereklidir.

Parametreler değiştirilirken "alter system" ile birlikte "set" ifadesi kullanılır. "Scope" ile birlikte değişikliğin nerelerde etkili olacağını belirtebiliriz.

Scope=SPFILE; ile parametrenin değişikliğinin sadece spfile'a uygulanması sağlanır. Şu anki instance'a etkisi olmaz. Tekrar startup edildiğinde değişiklik etkisini gösterir. Statik parametrelerin değişikliğinde kullanılabilinir.

Scope=MEMORY'de değişiklik sadece  instance için geçerli olur. Anında etkisini görebiliriz. Statik parametrelerin değişikliğinde kullanılamaz.

Scope=BOTH'da değişiklik hem MEMORY'e yani anında instance için geçerli olacak şekilde değiştirilir. Hem de spfile'a yazılır. Statik parametrelerin değişikliğinde kullanılamaz.

Veritabanındaki Parametrelerin Hangilerinin Dinamik Hangilerinin Statik Olduğunun Bulunması:

Veritabanındaki parametreleri değiştirirken hangilerinin değişikliğinin hemen olabileceğini görmek için aşağıdaki tabloyu inceleyebiliriz.

 select NAME, ISSES_MODIFIABLE, ISSYS_MODIFIABLE from v$parameter where name like '%%';

Bu tabloya göre hangi parametrenin statik hangi parametrenin dinamik olduğunu bulabilir, sonrasında yaptığımız değişikliğin instance bazında mı yoksa session bazında mı olup olmadığını görebiliriz. Eğer bu yukarıdaki tabloda ISSES_MODIFIABLE kolonu belirtilen parametre için "True" ise o parametre hemen değiştirilebilinir.

SPFILE'dan PFILE Yaratılması:

"Create PFILE"  ile spfile dosyasında pfile yaratılabilinir. Spfile'da binary olarak depolanmaktan olan parametreleri raporlarmak için böyle bir işlemi gerçekleştirebiliriz. Bununla birlikte eğer spfile bir değişiklik yapmak istiyorsak spfile'dan pfile yaratıp tekrar spfile yaratabiliriz. Pfile yaratmak için de ayrıca sysdba yetkisine sahip olmamız gerekir.

Pfile'ın üretilmesi için alternatif bir yolda aşağıdaki ifadedir.

 CREATE PFILE FROM MEMORY;  

Aynı işlem spfile'ın yaratılması için de geçerlidir.

CREATE SPFILE FROM MEMEORY

SPFILE'ın Kaybolması Durumunda Ne Yapılabilinir?

1-Eğer instance açıksa spfile memory'den çıkartılabilinir.

CREATE SPFILE FROM MEMEORY

2-Aynı şekilde pfile'dan yaratılabilinir.

create spfile from pfile;

3-Backup'tan dönülebilinir. Her veritabanı backup'ında spfile'da backup'lanır.

4- Eğer yukarıdaki hiçbir yöntem gerçekleştirilemezse alert log'da bulunan dosyalardan yola çıkarak pfile yaratılabilinir ve sonrasında spfile yaratılabilinir.

SPFILE veya PFILE ile Veritabanının Başlatılması:

Pfile ile

startup pfile='/home/users/oracle/initabc.ora';  

Spfile ile

startup spfile='/home/users/oracle/spfile.ora';  

Oracle Veritabanı: Bir Başka Kullanıcı Olarak Bağlanmak - Connecting As An Another User

Veritabanımızda bazen başka bir kullanıcı olarak giriş yapmamız gerekebilir. Bunun için Oracle tarafında bunu mümkün kılabilecek bir özellik bulunmaktadır. Bu özellik sayesinde veritabanına başka bir kullanıcıyla giriş yapabiliriz. Bunun için ilk olarak kullanıcımıza "connect through" yetkisini vermemiz gerekir. Bu yetki bir dba tarafından verilebilinir.

Örnek:

 alter user berke grant connect through oracle;  

Buradaki örneğimizde oracle kullanıcısına "berke" kullanıcısı olarak bağlanmak için yetki veririz.  Bu yetki verildikten sonra da oracle kullanıcısı kendi şifresini vererekten berke kullanıcısı olarak bağlanabilir. Bunun için de aşağıdaki syntax'ı kullanmalıdır.

 sqlplus oracle[berke]/oracle  

Bu şekilde oracle kullanıcısı berke olarak oracle şifresinin vererekten bağlanmış olur.

Oracle Veritabanı:Oracle Net Listener - Oracle Servisler - Services

Servis Nedir?

Oracle veritabanı client'lara bir servis olarak tanıtılmaktadır. Bu servisler sayesınde veritabanına bağlanılır. Servisler listener'lara kaydedilir. Oradan da listenerlar tarafından uygun veritabanına bağlantısı sağlanır. Bir veritabanının birden fazla kayıtlı servisi olabilir. Veritabanındanki servis ismi spfile'da kayıtlıdır.

show parameter service_names 

Servisler sayesinde veritabanındaki yük bölümlenir. Her servisin ortak özellikleri bulunmaktadır. Örnek olarak Oracle E-Business Suite'de her sorumluluk için ayrı bir servis yaratılmaktadır. Bu servisler veritabanında kayıtlı olarak durmaktadırlar.

Servisler sayesinde iş yükleri tanımlayabiliriz, bunları yönetebiliriz, açıp kapatabiliriz. Buna göre ölçümler de yapabiliriz.

Veritabanındaki Servislerle İlgili Tablolar:

 DBA_SERVICES  
 ALL_SERVICES or V$SERVICES  
 V$ACTIVE_SERVICES  
 V$SERVICE_STATS  
 V$SERVICE_EVENT  
 V$SERVICE_WAIT_CLASSES  
 V$SERV_MOD_ACT_STATS  
 V$SERVICE_METRICS  
 V$SERVICE_METRICS_HISTORY  

Servis Yaratmak İçin: 

Servisleri DBCA ile,NetCA ile veya Oracle Enterprise Manager ile tanımlayabiliriz. Bundan başka service_names parametresini değiştirip DBMS_SERVICE.CREATE_SERVICE paketini çalıştıraraktan service'imizi yaratabiliriz.

Örnek :

 exec DBMS_SERVICE.CREATE_SERVICE('SERV1','SERV.oracle.com');

Yaratılan Servisin Başlatılması:

exec DBMS_SERVICE.START_SERVICE('SERV1');  

Servisin Durdurulması:

exec DBMS_SERVICE.STOP_SERVICE('SERV1');  

Servisin Silinmesi:

exec DBMS_SERVICE.DELETE_SERVICE('SERV1');  

Veritabanı Servisine Nasıl Bağlanılır?

Veritabanına bağlanmak için client'lar belirli bir bağlantı syntax'ı kullanmak zorundadırlar. Bu syntax'da veritabanının lokasyonu, bulunduğu makina, kullanılması gereken port  ve gerekli servis ismi yazılıdır. Bu bağlantı detayları tnsnames.ora dosyasında yazılıdır.

Aşağıdaki örnekte makina adı, servis adı, bağlantı portu, ve instance adı yer almaktadır.

sales=
 (DESCRIPTION= 
  (ADDRESS=(PROTOCOL=tcp)(HOST=sales-server)(PORT=1521)) 
  (CONNECT_DATA= 
     (SID=sales)
     (SERVICE_NAME=sales.us.example.com)
     (INSTANCE_NAME=sales)))

SID veritabanını, service adı  bağlanacağımız servisi, instance adı da veritabanı instance'ını belirtir. Rac veritabanı değilse tek instance vardır.

Tnsnames.ora dosyası client tarafına konaraktan uzaktaki makinanın veritabanı sunucusuna bağlanması sağlanır. Client tarafındaki makina bilgileri bu dosyadan alır.

Veritabanına bağlantı aşağıdaki gibi sağlanır:

Uygulama <=> Client(SQL*NET) <=>Network Bağlantısı TCP\IP <=>Listener <=>Veritabanı

Veritabanı kendisine gelen istekleri açık olan listenerdaki dinlenilen servislere göre veritabanına yönlendirir.

Network Konfigürasyon Dosyaları:

tnsnames.ora	Tnsnames.ora dosyası client ve server'larda bulunur. Burada bağlantı ifadeleri yer alır. Bu bağlantı ifadelerinde bağlanılacak servisler ve bağlantı adresleri bulunur.
sqlnet.ora	Bu dosya da client  ve server'da bulunur. Bu dosyada veritabanına erişimle ilgili çeşitli parametreler yer alır. Bu parametreler güvenlikle ilgili, loglamayla ilgili ya da bağlantıyla ilgili olabilir.
listener.ora	Bu dosya server'da bulunur. Bu dosyada dinlenilen veritabanı servisleri yer alır. Listener tarafından kullanılan çeşitli parametreler yer alır.

TNS Net Service Name Hatası:

TNS Net Service Name hatasını eğer environment dosyamızda Oracle SID'miz bulunmuyorsa alabilir. Service Name'imiz resimde görüldüğü üzere lsnrctl'den de kontrol etmemize rağmen SQLPlus'a bağlanamıyorduk.

Bunun için kullanıcımızın home directory'sine gideriz. Orada da .bash_profile dosyasının içerisine ORACLE_SID,ORACLE_HOME parametrelerini ekleyip PATH'de sqlplus'un bulunduğu klasörü ekleriz. ".bash_profile" gizli bir dosya olduğu için "ls -al .bash_profile" şeklinde dosyayı bulabiliriz. Sonra da "vi .bash_profile"ile dosyanın içeriğini değiştirebiliriz.

Özetle:
.bash profile dosyasını aşağıdaki gibi güncelleriz.

ORACLE_HOME="/u01/install/PROD/11.2.0"
export ORACLE_HOME

ORACLE_SID="DENEME"
export ORACLE_SID


export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$PATH


Yukarıdaki adımları attıktan sonra TNS Net Service Name is incorrectly specified hatasını düzeltmiş oluruz.

Oracle Veritabanı: Schema ile Kullanıcı Arasındaki Fark - Difference Between A Schema and User

Oracle veritabanında kullanıcılar ve kullanıcılarla aynı adla yaratılan schema'lar hakkında anlam karmaşası olabiliyor.

Schema'lar veritabanı objelerinin bir araya toplanmış haline denir. Bu schema'lar içerisinde tablolar, view'lar, sequence'lar, procedure'ler,synonyms'ler, index'ler ve database links'ler bulunur.

Kullanıcıların her birinin bir scheması bulunur. Her schema kullanıcı adıyla aynı isme sahiptir. Bir kullanıcı yaratıldığında aynı adla schema'da yaratılır.

Schema'ların tablespace'lerle ilgisi ise schema'lar kullanıcıların nesnelerini mantıksal olarak tutarken bunların fiziksel karşılıkları tablespace dediğimiz yerlerde tutulur. Birden fazla schema bir tane tablespace'de tutulabilir. Bununla birlikte her kullanıcının bir tane schema'sı olabileceğinden birden fazla kullancı aynı tablespace'i kullanabilir.

Bir kullanıcının objesine ulaşmak istediğinde tablespace adını değilde schema adını yazıp "." koyarız.

Örnek: select * from scott.emp  

Oracle Veritabanı: Trace Dosyaları - Trace Files

Trace dosyaları ADR(Automatic Diagnostic Repository)  bölgesinde tutulurlar. Burada dosyları tek tek bulmak için veritabanındaki tabloları sorgulayabiliriz. Aşağıdaki sorguları kullanırsak şu anki session'ımızla ilgili trace file'ımızı bulabiliriz. Bu session'la ilgil bilgiler aşağıdaki trace dosyasına yazılır.

SELECT VALUE FROM V$DIAG_INFO WHERE NAME = 'Default Trace File';

Şu an session'la ilgili bütün trace dosyaları için aşağıdaki sorguyu çalıştırabiliriz. Bu sorguyla trace dosyalarının bulunduğu klasöre gidebiliriz.

 SELECT VALUE FROM V$DIAG_INFO WHERE NAME = 'Diag Trace';  

Oracle Veritabanında trace dosyalarına yazan process'leri görmek için aşağıdaki sorguyu kullanabiliriz.

SELECT PID, PROGRAM, TRACEFILE FROM V$PROCESS;  

Trace dosyalarında karşılaşlan hataları görmek için aşağıdaki sorguyu çalıştırabiliriz.

select * from v$diag_vproblem;  

Oracle Veritabanı: (System Change Number) SCN Nedir - Ne İşe Yarar? - Nasıl Çalışır?

System Change Number(SCN) Nedir?

Scn data tutarlılığını sağlayan bir mekanizmadır. Scn database'in o anki durumu için verilen "unique" bir numaradır. Scn numarası her redo kaydı için bir tane verilir. Her transaction'dan sonra bir tane arttırılır. Ayrıca her "commit;" sorgusundan sonra verilir. Commit edilen her transaction log writer(LGWR) process'ini çağırır. O da SGA'daki redo log bufferındaki bilgileri online redo log dosyalarına yazar.

SCN Nasıl Bulunur?

10g ve sonrasında aşağıdaki sorgu kullanılabilinir.

 SELECT current_scn  
 FROM V$DATABASE;  

SCN Değişme Oranı Ne Kadardır?

v$log_history ile SCN değişim oranını kontrol edebiliriz. Ne kadar hızlı loglar switch ediyorsa ona göre database de büyük bir I/O  yoğunluğu vardır.

 select thread#, first_time, next_time, first_change# ,next_change#, sequence#,  
   next_change#-first_change# diff, round ((next_change#-first_change#)/(next_time-first_time)/24/60/60) rt  
 from (  
 select thread#, first_time, first_change#,next_time, next_change#, sequence#,dest_id from v$archived_log  
 where next_time > sysdate-30 and dest_id=1  
 order by next_time  
 )  
 order by first_time, thread#  

Yukarıdaki sorgu kullanıldığında bize log file switch'leri arasındaki farklar görülebilinir. Hangi zamanlarda ne kadar artış olduğu, ne zaman yoğunluk arttığını buradan görebiliriz.

Referanslar:
http://orainternals.wordpress.com/2012/01/19/scn-what-why-and-how/
http://oracledbascriptsfromajith.blogspot.com.tr/2009/05/understanding-scnsystem-change-number.html

Oracle Veritabanı: Oracle Error ORA-48913 Caught While Writing To Trace Files

ORA-48913 hatası trace file'ımız yeterince büyük olmadığı zaman karşılaştığımız bir hatadır. Alert loglarımızıda karşılaşabiliriz. Sistemimize yoğun bir yük geldiğinde bu yükle ilgili gerekli bilgiler loglanır ve trace file'a yazılır. Trace file'da yeterince yer olmadığında, bununla ilgili bir hata alert log'a yazılır. Bunu çözmemiz için Oracle'da bulunan bir parametreyi değiştirmemiz gerekir. Bu parametre max_dump_file_size'dir.

show parameter max_dump_file_size;  

Bu parametre kb cinsinden verilmektedir. Default değeri 10mb'dır. Bu parametreyi değiştirmek için, aşağıda tırnak içindeki kısma istediğimiz değeri yazabiliriz.

 alter system set max_dump_file_size='' scope=BOTH

Bu parametre dinamik bir parametredir. Hemen değiştirilebilinir.