oracle 12.1.0.1 to 12.1.0.2 upgrade for Non-CDB

1. NEW DB 12.1.0.2 INSTALL (Farkli bir location’a)

-bash-4.1$ cd /griddata/oracle/app
-bash-4.1$ mv oraInventory oraInventory_12101

–kurulum sonrası
SQL> create pfile from spfile;
-bash-4.1$ cp initdwgrid.ora /griddata/oracle/product/dbhome_2/dbs

2. PREREQUEST
–yeni home’dan 2 sql paketi tmp altina atilir
cd /griddata/oracle/product/dbhome_2/rdbms/admin
-bash-4.1$ cp preupgrd.sql /tmp
-bash-4.1$ cp utluppkg.sql /tmp

cd /tmp
SQL>preupgrd.sql

çıkan sonuçlara göre aşağıdaki yapıldı.
SQL> EXECUTE dbms_stats.gather_dictionary_stats;
SQL> EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
[Component List]
**********************************************************************
–> Oracle Catalog Views [upgrade] VALID
–> Oracle Packages and Types [upgrade] INVALID -> valid yapmak icin asagidaki yapildi
–> JServer JAVA Virtual Machine [upgrade] VALID
–> Oracle XDK for Java [upgrade] VALID
–> Real Application Clusters [upgrade] OPTION OFF
–> Oracle Workspace Manager [upgrade] VALID
–> OLAP Analytic Workspace [upgrade] VALID
–> Oracle Label Security [upgrade] VALID
–> Oracle Database Vault [upgrade] VALID
–> Oracle Text [upgrade] VALID
–> Oracle XML Database [upgrade] VALID
–> Oracle Java Packages [upgrade] VALID
–> Oracle Multimedia [upgrade] VALID
–> Oracle Spatial [upgrade] VALID
–> Oracle Application Express [upgrade] VALID
–> Oracle OLAP API [upgrade] VALID

–“Oracle Packages and Types” VALID yapmak icin
SQL>shutdown immediate;
SQL>startup upgrade;
SQL> @?/rdbms/admin/catalog.sql
SQL> @?/rdbms/admin/catproc.sql
catalog.sql will recreate Oracle database Catalog Views component and catproc.sql will recreate Oracle database Packages and Types component
SQL> @?/rdbms/admin/utlrp.sql
3. UPGRADE

-> ESKI HOME’dan
SQL> SHUTDOWN IMMEDIATE

-> YENI HOMEDAN
SQL> startup upgrade pfile=/griddata/oracle/product/dbhome_2/dbs/initdwgrid.ora

-> YENI HOMEDAN
cd $ORACLE_HOME/rdbms/admin
SQL>$ORACLE_HOME/perl/bin/perl catctl.pl catupgrd.sql

SQL> STARTUP

a. log’da “Started: catuppst.sql” gormedigim icin bunu calistirdim
$ORACLE_HOME/perl/bin/perl catcon.pl -n 1 -e -b catuppst -d ”’.”’ catuppst.sql

b.
$ORACLE_HOME/perl/bin/perl catcon.pl -n 1 -e -b utlrp -d ”’.”’ utlrp.sql

c.
cd $ORACLE_BASE/cfgtoollogs/dwgrid/preupgrade
SQL> @postupgrade_fixups.sql

d.
-bash-4.1$ cd $ORACLE_HOME
SQL> @rdbms/admin/utlu121s.sql

source : http://docs.oracle.com/database/121/UPGRD/upgrade.htm#CHDBHAHE

-bash-4.1$ uname -a
Linux xyz 2.6.32-279.el6.x86_64 #1 SMP Wed Jun 13 18:24:36 EDT 2012 x86_64 x86_64 x86_64 GNU/Linux

Oracle Long / Lob Types

Oracle uzun yıllar (yapısal olmayan text, grafik, video, ses gibi) large objeleri LONG ve LONG RAW data tipi ile destekler idi. Oracle 8 ile birlikte Large Object (LOB)’lar geldi. Oracle artık LOB
tip’leri kullanmamizi tavsiye etmektedir. LONG tipler geçmişe dönük olarak dekteklenmektedir.

LONG ( 2 cesittir )
* Max Size = 2GB(2GB-1)
* VARCHAR2’nin karakteristik özelliklerine sahiptir
* Bir tabloda bir adet bulunabilir
* Dataya erişmek için “sequential read” yani baştan sonra tarama yapar.
* Data insert direkt row’da olur
* Sorgulama yapıldığında direkt kolondaki data’yi döner
* “Group by”, “Order by”, “Select Distinct”, “joins” “aggregate functions”‘da kullanılamazlar
* user-defined data type’inde kullanilamaz

LONG : string data
LONG RAW : binary data

LOB (4 cesittir)
* Max Size = (4GB-1)*DB_BLOCK_SIZE (2KB to 32KB) = 8TB to 128 TB (oracle 10.2’de max size = 4GB-1)
* Bir tabloda birden fazla bulunabilir
* Dataya erişmek için “random access” yapar.
* Data insert sirasinda “LOB locator” kaydedilir, data kaydedilmez. Amaç query/depolama’da verimlilik
* Sorgulama yapıldığında “LOB locator” döner (in-line LOBS haricinde), içeriği dönmez
* “Group by”, “Order by”, “Select Distinct”, “joins” “aggregate functions”‘da kullanılamazlar
* DBMS_LOB paketi manipulating islemlerinde kullanilir
* user-defined data type’inde kullanilabilir

internal lob -> database’de tabloda durur, Db transaction modeline (commit, rollback …) uyar.
CLOB : Character Large Object, db karakter set formatinda string data depolanir. DB karakter set tipindeki string ve dokumanlar icin kullanilir. Sabit uzunluktaki karaketer set’lerini destekler.
BLOB : Binary Large Object, binary formattaki data depolanir. image, audio, video…
NCLOB : National Character Set Large Object, National Character Set Formattaki data depolanir. National Karakter Set tipindeki string ve dokumanlar icin kullanilir. Degisken uzunluktaki karaketer
setlerini destekler.

external lob -> database disinda OS’da durur, DB transaction model kurallarina uymaz, değişiklikler OS tarafinda yapilir. Sadece read access vardir.
BFILE: binary data stored in the file, the data in this file can only be read-only access. But the file is not included in the database.

Not : Eger LOB boyutu (data + locator) > 4000 byte ise, data in-line olarak tutulur. Eğer 4000 byte’dan büyük ise out-of-line tutulur. “STORE AS (disable storage in row)” ile bu disable
edilebilir. Bu durumda row’da sadece locator tutulur, data LOB segement’te tutulur.

BEST PRACTICE :
* image, test file, word dokumanlari icin en iyi tip BLOB’dur. LONG RAW’da kullanilabilir fakat oracle her durumda LOB tip’leri kullanmayi tavsiye eder.
* lob type icin her zaman storage bilgisi girilmelidir. default tbs system olan bir db’de 4K’dan büyük lob data buraya yazilacagindan problem olusabilir.

CREATE TABLE DBUSERS(
USERNAME NVARCHAR2(20),
PASSWORD BLOB )
LOB (PASSWORD) STORE AS (TABLESPACE RAW_TBS)
TABLESPACE DATATBS;

Referans : http://docs.oracle.com/cd/E11882_01/appdev.112/e18294.pdf

vbox uuid does not match the value

Eger Vbox makina farkli bir pc’ye tasindiysa asagidaki gibi bir hata gelebilir.

UUID {b6e58671-d821-4dba-b723-f12defc2650a} of the medium ‘D:\VM\vbox-rac\disks\asm1.vdi’ does not match the value {1317e761-4532-40c2-8640-19f8a511bb25} stored in the media registry

Çözüm : disk bilgisini al ve uuid set et

VBoxManage showhdinfo D:\VM\vbox-rac\disks\asm1.vdi
VBoxManage showhdinfo D:\VM\vbox-rac\disks\asm2.vdi
VBoxManage showhdinfo D:\VM\vbox-rac\disks\asm3.vdi
VBoxManage showhdinfo D:\VM\vbox-rac\disks\asm4.vdi

VBoxManage internalcommands sethduuid D:\VM\vbox-rac\disks\asm1.vdi 1317e761-4532-40c2-8640-19f8a511bb25
VBoxManage internalcommands sethduuid D:\VM\vbox-rac\disks\asm2.vdi 4ce3f461-0145-42e0-b497-76c7e46857e2
VBoxManage internalcommands sethduuid D:\VM\vbox-rac\disks\asm3.vdi 58f24229-c6d2-45f7-8a23-8efcc568d244
VBoxManage internalcommands sethduuid D:\VM\vbox-rac\disks\asm4.vdi 1088de8c-40a3-412f-aff5-c7837b624ea6

rman register database RMAN-03008 ORA-02291

-bash-4.1$ rman

RMAN> connect target rman/***@emdb;
RMAN> connect rcvcat rman/***@CATDB;
RMAN> register database;
database registered in recovery catalog
starting full resync of recovery catalog
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03008: error while performing automatic resync of recovery catalog
ORA-02291: integrity constraint (RMAN.DF_F5) violated – parent key not found

ÇÖZÜM : apply patch 13727644

Oracle Enterprise Manager 12c 12.1.0.3 Db vault problem

Oracle EM 12c Grid Control (12.1.0.3) ‘e Db Vault kurulu bir database’i eklerseniz aşağıdaki san sıkıcı uyarı ile karşlaşabilirsiniz. Bug 17482019

You have been logged in to a Database with Database Vault installed on it. As Database Vault enforces separation of duty you may not be able to use all the areas in EM. Please click the link below which will forward you to the area which you have access to.

Çözüm :  DV role’leri revoke et

revoke DV_SECANALYST from master;
revoke DV_ADMIN from master;
revoke DV_OWNER from master;
revoke DV_ACCTMGR from master;

Sql Server Row and Cell Level Security

1 ) ROW LEVEL SECURITY

SQL Server’da row seviyesinde güvenlik, ara tablolarla ve row bazlı gizleme yapılcak tabloda bu amaç için bir alan ile sağlanmaktadır. Bu alan genellikle belirli kişilerden oluşan grubun id’sini içermektedir. Örnek verecek olursak bir kullanıcı sadece kendi

departmanındaki çalışanları görecekse bu şekilde bir sorgulama yapılmalıdır. Bunu daha otomatize hale getirmek için SQL Server “View” veya “Table-Valued Function” yapılmasını önermektedir.

SELECT * FROM dbo.Employee where Department = @DepartmentID;

2 ) CELL LEVEL SECURTY ( Encrypt / Decrypt )

http://msdn.microsoft.com/en-us/library/ms179331.aspx

–If there is no master key, create one now.
IF NOT EXISTS (SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 101)
CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘123456789’
GO

CREATE CERTIFICATE INKA358 WITH SUBJECT = ‘CreditID Securty’;
GO

CREATE SYMMETRIC KEY SSN_INKA_Key WITH ALGORITHM = AES_256 ENCRYPTION BY CERTIFICATE INKA358;
GO

— Open the symmetric key with which to encrypt the data.
OPEN SYMMETRIC KEY SSN_INKA_Key DECRYPTION BY CERTIFICATE INKA358;

— Encrypt the value in column CreditID with symmetric
UPDATE dbo.Employee SET CreditIDEncrypted = EncryptByKey(Key_GUID(‘SSN_INKA_Key’), CreditID);
GO

— Verify the encryption. First, open the symmetric key with which to decrypt the data.
OPEN SYMMETRIC KEY SSN_INKA_Key DECRYPTION BY CERTIFICATE INKA358;
GO

— Decrypted CreditID
SELECT CONVERT(nvarchar, DecryptByKey(CreditIDEncrypted)) AS ‘Decrypted CreditID’
FROM dbo.Employee;
GO

3) COLUMN LEVEL SECURTY
Belirli rollere istediğimiz kolonları gösterebiliriz. Kullanıcılar Rollere dahil edilip kullanıcı bazlı Select Yetkisi vermiş oluruz. Bunu SSMS’de görsek olarak da yapabiliriz.

–Grant permissions all columns to “HR_Employee” Role
GRANT SELECT ON dbo.Employee TO HR_Employee;
–Grant permissions spesific columns to “HR_Intern” Role
GRANT SELECT ON dbo.Employee (EmployeeID, FirstName, MiddleName, SurName) TO HR_Intern;
–DENY command if you had to
DENY SELECT ON dbo.Employee (SSN, Salary) TO HR_Intern;

Whitepaper: Implementing Row and Cell Level Security in Classified Databases

oracle – first step in dba

yaklaşık 2 sene yazılımcı olarak çalıştığım şirketimde, v$instance tablosunu sorgulamak istediğimde “ORA-00942: table or view does not exist” diyen oracle, 01.06.2011 tarihi itibariyle dba olduğumda el peçe diyan “tabi abi al hayrını gör” demez mi 🙂

oracle – analitik fonksiyonlar

örnek yapabileceğimiz ortamı hazırlayalım.

CREATE TABLE TTEST
(
IDX     CHAR(15 BYTE),
AD      VARCHAR2(20 BYTE),
TUTAR   NUMBER(18,2),
STATUS  CHAR(1 BYTE),
TARIH   DATE
);

Insert into TTEST (IDX, AD, TUTAR, STATUS, TARIH) Values (‘200902261503189’, ‘SAD’, 14, ‘W’, TO_DATE(’02/26/2009 00:00:00′, ‘MM/DD/YYYY HH24:MI:SS’));
Insert into TTEST (IDX, AD, TUTAR, STATUS, TARIH) Values (‘200901191507200’, ‘AHM’, 42, ‘W’, TO_DATE(’01/19/2009 00:00:00′, ‘MM/DD/YYYY HH24:MI:SS’));
Insert into TTEST (IDX, AD, TUTAR, TARIH) Values (‘200901191505633’, ‘SAL’, 32, TO_DATE(’01/19/2009 00:00:00′, ‘MM/DD/YYYY HH24:MI:SS’));
Insert into TTEST (IDX, AD, TUTAR, STATUS, TARIH) Values (‘200902261506519’, ‘AAT’, 20, ‘N’, TO_DATE(’02/26/2009 00:00:00′, ‘MM/DD/YYYY HH24:MI:SS’));
Insert into TTEST (IDX, AD, TUTAR, STATUS, TARIH) Values (‘200902261508282’, ‘SUL’, 10, ‘N’, TO_DATE(’02/26/2009 00:00:00′, ‘MM/DD/YYYY HH24:MI:SS’));
Insert into TTEST (IDX, AD, TUTAR, STATUS, TARIH) Values (‘200903031534449’, ‘CAN’, 16, ‘N’, TO_DATE(’03/03/2009 00:00:00′, ‘MM/DD/YYYY HH24:MI:SS’));
Insert into TTEST (IDX, AD, TUTAR, STATUS, TARIH) Values (‘200903031534950’, ‘KEM’, 35, ‘N’, TO_DATE(’03/03/2009 00:00:00′, ‘MM/DD/YYYY HH24:MI:SS’));
Insert into TTEST (IDX, AD, TUTAR, STATUS, TARIH) Values (‘200903031534721’, ‘ERM’, 12, ‘N’, TO_DATE(’03/03/2009 00:00:00′, ‘MM/DD/YYYY HH24:MI:SS’));
Insert into TTEST (IDX, AD, TUTAR, STATUS, TARIH) Values (‘200901191509347’, ‘PET’, 15, ‘N’, TO_DATE(’01/19/2009 00:00:00′, ‘MM/DD/YYYY HH24:MI:SS’));
Insert into TTEST (IDX, AD, TUTAR, STATUS, TARIH) Values (‘200901191507345’, ‘ONU’, 30, ‘N’, TO_DATE(’01/19/2009 00:00:00′, ‘MM/DD/YYYY HH24:MI:SS’));
COMMIT;

***************************************************************************

NTILE belirtilen sayıya göre kayıt grublaması yapar. mesela 100 kayıt varsa 10ar 10ar işaretleme yapar

Select IDX,TARIH TARIH,TUTAR,NTILE(5) OVER (ORDER BY TUTAR) AS TILE4  FROM ttest;

IDX             TARIH          TUTAR      TILE4
–
200902261508282 26-ŞUB-09         10          1
200903031534721 03-MAR-09         12          1
200902261503189 26-ŞUB-09         14          2
200901191509347 19-OCA-09         15          2
200903031534449 03-MAR-09         16          3
200902261506519 26-ŞUB-09         20          3
200901191507345 19-OCA-09         30          4
200901191505633 19-OCA-09         32          4
200903031534950 03-MAR-09         35          5
200901191507200 19-OCA-09         42          5

***************************************************************************

ROW_NUMBER sıralı bi şekilde numara verir. Ornekte gozuktugu uzere tarih kayit kümesine gore her satira ardisik numara verdi.

Select IDX,TARIH,TUTAR,ROW_NUMBER() OVER (PARTITION BY TARIH ORDER BY TUTAR DESC) NUM  FROM ttest;

IDX             TARIH          TUTAR        NUM
–
200901191507200 19-OCA-09         42          1
200901191505633 19-OCA-09         32          2
200901191507345 19-OCA-09         30          3
200901191509347 19-OCA-09         15          4
200902261506519 26-ŞUB-09         20          1
200902261503189 26-ŞUB-09         14          2
200902261508282 26-ŞUB-09         10          3
200903031534950 03-MAR-09         35          1
200903031534449 03-MAR-09         16          2
200903031534721 03-MAR-09         12          3

***************************************************************************

RANK() belirtilen kurallara göre sıralama yapar ve numaralandırır, aynı degerlere aynı dank’i verdigi icin ardisik olmayabilir.
Örnekte görüldüğü gibi aynı degerde 3 satıra 1, 4. satira 4 deyip devam ediyor.

select IDX, TARIH, TUTAR, RANK() OVER(ORDER BY TARIH DESC) NUM from ttest;

IDX             TARIH          TUTAR        NUM
–
200903031534449 03-MAR-09         16          1
200903031534721 03-MAR-09         12          1
200903031534950 03-MAR-09         35          1
200902261506519 26-ŞUB-09         20          4
200902261503189 26-ŞUB-09         14          4
200902261508282 26-ŞUB-09         10          4
200901191505633 19-OCA-09         32          7
200901191507200 19-OCA-09         42          7
200901191509347 19-OCA-09         15          7
200901191507345 19-OCA-09         30          7

***************************************************************************

DENSE_RANK() belirtilen kurallara göre sıralama yapar ve numaralandırır. RANK’a cok benzer, ardisik siralama vardir

select IDX, TARIH, TUTAR, DENSE_RANK() OVER(PARTITION BY TARIH ORDER BY TUTAR DESC) NUM from ttest;

IDX             TARIH          TUTAR        NUM
–
200901191507200 19-OCA-09         42          1
200901191505633 19-OCA-09         32          2
200901191507345 19-OCA-09         30          3
200901191509347 19-OCA-09         15          4
200902261506519 26-ŞUB-09         20          1
200902261503189 26-ŞUB-09         14          2
200902261508282 26-ŞUB-09         10          3
200903031534950 03-MAR-09         35          1
200903031534449 03-MAR-09         16          2
200903031534721 03-MAR-09         12          3

***************************************************************************

NULLS LAST/FIRST ile nullları alta veya basa alabiliriz. Ornekte de goruldugu gibi tek null satisi sona alindi

select IDX, TARIH, TUTAR, STATUS, RANK() OVER(ORDER BY STATUS DESC NULLS LAST) NUM from ttest;

IDX             TARIH          TUTAR S        NUM
–  –
200902261503189 26-ŞUB-09         14 W          1
200901191507200 19-OCA-09         42 W          1
200902261508282 26-ŞUB-09         10 N          3
200902261506519 26-ŞUB-09         20 N          3
200901191507345 19-OCA-09         30 N          3
200903031534449 03-MAR-09         16 N          3
200903031534950 03-MAR-09         35 N          3
200903031534721 03-MAR-09         12 N          3
200901191509347 19-OCA-09         15 N          3
200901191505633 19-OCA-09         32           10

***************************************************************************

ROWS UNBOUNDED PRECEDING belirtilen kayıt kümesi içinde başlangıç satırından o anki satira kadar işlem yapar. Bu kural defaulttur, Yani ayrıca belirtilmesine gerek yok
select IDX, TARIH, TUTAR, SUM(TUTAR) OVER (PARTITION BY TARIH ORDER BY IDX) SSUM from ttest;  da ayni sonucu verir
Ornekte de goruldugu gibi 32, 32+42, 32+42+30, … seklinde ilerliyor
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING = baslangic satirindan son satira kadar islem yaparar.
Fakat bu amac için bu kullanisli degildir. Cunklu ROWS komutunu kullanmazsak ayni islemi yapar. Bu amaç için RANGE BETWEEN komutunu kullanmak daha mantiklidir.

select IDX, TARIH, TUTAR, SUM(TUTAR) OVER (PARTITION BY TARIH ORDER BY IDX ROWS UNBOUNDED PRECEDING) SSUM from ttest;

IDX             TARIH          TUTAR       SSUM
–
200901191505633 19-OCA-09         32         32
200901191507200 19-OCA-09         42         74
200901191507345 19-OCA-09         30        104
200901191509347 19-OCA-09         15        119
200902261503189 26-ŞUB-09         14         14
200902261506519 26-ŞUB-09         20         34
200902261508282 26-ŞUB-09         10         44
200903031534449 03-MAR-09         16         16
200903031534721 03-MAR-09         12         28
200903031534950 03-MAR-09         35         63

ayni ozellikle SUM yerine MAX MIN AVG COUNT de yazilabilirdi
burada dikkat edilmesi gereken ORDER BY kullanmazsak kayit kumesinin tamaminin goze alinması, kullanirsak current row’a kadar kayit kumesinin goze alinmasi
MAX -> select IDX, TARIH, TUTAR, MAX(TUTAR) OVER (PARTITION BY TARIH ORDER BY IDX) x from ttest;
COUNT -> select IDX, TARIH, TUTAR, COUNT(1) over (partition by TARIH) CCNT from ttest;

***************************************************************************

RANGE BETWEEN belirli araliktaki satirlara gore islem yapilabilir
Ornekte tam anlasilmasa da ilk kayit grubu icin 20091119 tarihinden bi onceki ve sonraki gun’e sahip kayitlar degerlendiriliyor. 20091118 ve 20091120 olsaydi onlar da AVG ye katilacakti.
Select IDX, TARIH, TUTAR, AVG(TUTAR) OVER (ORDER BY to_date(TARIH,’YYYYmmdd’) RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING) ORT from ttest; Asagidaki ornek interval kullanilmadan yazilabilirdi.

Select IDX, TARIH, TUTAR, AVG(TUTAR) OVER (ORDER BY TARIH RANGE BETWEEN INTERVAL ‘1’ DAY PRECEDING AND INTERVAL ‘1’ DAY FOLLOWING) ORT from ttest;

IDX             TARIH          TUTAR        ORT
–
200901191509347 19-OCA-09         15      29.75
200901191507345 19-OCA-09         30      29.75
200901191505633 19-OCA-09         32      29.75
200901191507200 19-OCA-09         42      29.75
200902261506519 26-ŞUB-09         20 14.6666667
200902261503189 26-ŞUB-09         14 14.6666667
200902261508282 26-ŞUB-09         10 14.6666667
200903031534721 03-MAR-09         12         21
200903031534449 03-MAR-09         16         21
200903031534950 03-MAR-09         35         21

Select IDX, TARIH, TUTAR, SUM(TUTAR) OVER (ORDER BY TARIH RANGE BETWEEN INTERVAL ’10’ DAY PRECEDING AND CURRENT ROW) SSUM from ttest; CURRENT ROW bulunulan satira kadar demektir

***************************************************************************

ROWS x PRECEDING , x satir geriye giderek islem yapar
Ornekte goruzludugu gibi, ilk kayit grubuna gore 32, 32 +42, 32+42+30, 42+30+15, daha sonra diger katir grubuna geciyor.

select IDX, TARIH, TUTAR, SUM(TUTAR) OVER (PARTITION BY TARIH ORDER BY IDX ROWS 2 PRECEDING) SSUM from ttest;

IDX             TARIH          TUTAR       SSUM
–
200901191505633 19-OCA-09         32         32
200901191507200 19-OCA-09         42         74
200901191507345 19-OCA-09         30        104
200901191509347 19-OCA-09         15         87
200902261503189 26-ŞUB-09         14         14
200902261506519 26-ŞUB-09         20         34
200902261508282 26-ŞUB-09         10         44
200903031534449 03-MAR-09         16         16
200903031534721 03-MAR-09         12         28
200903031534950 03-MAR-09         35         63
***************************************************************************

RATIO_TO_REPORT belirtilen degerin belirtilen satir kümesindeki orani verir

select IDX, TARIH, TUTAR, RATIO_TO_REPORT(TUTAR) OVER (PARTITION BY TARIH) ORAN from ttest;

IDX             TARIH          TUTAR       ORAN
–
200901191509347 19-OCA-09         15 .126050420
200901191507345 19-OCA-09         30 .252100840
200901191505633 19-OCA-09         32 .268907563
200901191507200 19-OCA-09         42 .352941176
200902261506519 26-ŞUB-09         20 .454545455
200902261503189 26-ŞUB-09         14 .318181818
200902261508282 26-ŞUB-09         10 .227272727
200903031534721 03-MAR-09         12 .190476190
200903031534449 03-MAR-09         16 .253968254
200903031534950 03-MAR-09         35 .555555556

***************************************************************************

LAG belirtilen alanin onceki satirdaki degerini verir

select IDX, TARIH, TUTAR, LAG(TUTAR,1) OVER (ORDER BY IDX) PREVIOUS_VALUE from ttest;

IDX             TARIH          TUTAR PREVIOUS_VALUE
–
200901191505633 19-OCA-09         32
200901191507200 19-OCA-09         42             32
200901191507345 19-OCA-09         30             42
200901191509347 19-OCA-09         15             30
200902261503189 26-ŞUB-09         14             15
200902261506519 26-ŞUB-09         20             14
200902261508282 26-ŞUB-09         10             20
200903031534449 03-MAR-09         16             10
200903031534721 03-MAR-09         12             16
200903031534950 03-MAR-09         35             12

***************************************************************************

LEAD belirtilen alanin sonraki satirdaki degerini verir

select IDX, TARIH, TUTAR, LEAD(TUTAR,1) OVER (ORDER BY IDX) NEXT_VALUE from ttest;

IDX             TARIH          TUTAR NEXT_VALUE
–
200901191505633 19-OCA-09         32         42
200901191507200 19-OCA-09         42         30
200901191507345 19-OCA-09         30         15
200901191509347 19-OCA-09         15         14
200902261503189 26-ŞUB-09         14         20
200902261506519 26-ŞUB-09         20         10
200902261508282 26-ŞUB-09         10         16
200903031534449 03-MAR-09         16         12
200903031534721 03-MAR-09         12         35
200903031534950 03-MAR-09         35

***************************************************************************

KEEP FIRST/LAST -> DENSE_RANK ile sıralanmış satırların FIRST varsa ilk, LAST varsa son satırı alınır, MAX yerine MIN,SUM,AVG yazılabilir
2. sorguda görüldüğü gibi 20090119 için first değer 15 last deger 42 dür.

select IDX, TARIH, TUTAR, MAX(TUTAR) KEEP (DENSE_RANK FIRST ORDER BY TUTAR ) OVER (PARTITION BY TARIH) KF,
MAX(TUTAR) KEEP (DENSE_RANK LAST ORDER BY TUTAR ) OVER (PARTITION BY TARIH) KL from ttest

IDX             TARIH          TUTAR         KF         KL
–
200901191509347 19-OCA-09         15         15         42
200901191507345 19-OCA-09         30         15         42
200901191505633 19-OCA-09         32         15         42
200901191507200 19-OCA-09         42         15         42
200902261506519 26-ŞUB-09         20         10         20
200902261503189 26-ŞUB-09         14         10         20
200902261508282 26-ŞUB-09         10         10         20
200903031534721 03-MAR-09         12         12         35
200903031534449 03-MAR-09         16         12         35
200903031534950 03-MAR-09         35         12         35

select IDX, TARIH, TUTAR, DENSE_RANK() OVER(PARTITION BY TARIH ORDER BY TUTAR ) NUM from ttest;

IDX             TARIH          TUTAR        NUM
–
200901191509347 19-OCA-09         15          1
200901191507345 19-OCA-09         30          2
200901191505633 19-OCA-09         32          3
200901191507200 19-OCA-09         42          4
200902261508282 26-ŞUB-09         10          1
200902261503189 26-ŞUB-09         14          2
200902261506519 26-ŞUB-09         20          3
200903031534721 03-MAR-09         12          1
200903031534449 03-MAR-09         16          2
200903031534950 03-MAR-09         35          3

Bu şekilde de aynı sonuca ulaşabiliriz
select IDX, TARIH, TUTAR, MAX(TUTAR) OVER (PARTITION BY TARIH) Mn, MIN(TUTAR) OVER (PARTITION BY TARIH) MX from ttest;

***************************************************************************

CUME_DIST satırdaki değerin diğer satırlardaki değere göre pozisyonunu verir, belirtilen_degere_esit_veya_daha_kucuk_degerlerin_sayisi / belirtilenbolumdeki_toplam_kayit_sayisi
mesela 3. satıra bakalım. 20090119’a uygun 4 satır var. konum olarak 3. sırada 3/4 = .75

select IDX, TARIH, TUTAR, CUME_DIST() OVER (PARTITION BY TARIH ORDER BY TUTAR NULLS LAST) ORAN from ttest;

IDX             TARIH          TUTAR       ORAN
–
200901191509347 19-OCA-09         15        .25
200901191507345 19-OCA-09         30         .5
200901191505633 19-OCA-09         32        .75
200901191507200 19-OCA-09         42          1
200902261508282 26-ŞUB-09         10 .333333333
200902261503189 26-ŞUB-09         14 .666666667
200902261506519 26-ŞUB-09         20          1
200903031534721 03-MAR-09         12 .333333333
200903031534449 03-MAR-09         16 .666666667
200903031534950 03-MAR-09         35          1

***************************************************************************

PERCENT_RANK satırdaki degerin diğer satırlardaki değere göre konumunun yuzde degeri, CUME_DIST’a benzer
mesle 3. satıra bakalım. (konumu-1)/(satir_satisi-1) 20090119’a uygun 4 satır var. konum olarak 3. sırada (3-1)/(4-1) = 2/3 = .666666667

select IDX, TARIH, TUTAR, PERCENT_RANK() OVER (PARTITION BY TARIH ORDER BY TUTAR NULLS LAST) ORAN from ttest;

IDX             TARIH          TUTAR       ORAN
–
200901191509347 19-OCA-09         15          0
200901191507345 19-OCA-09         30 .333333333
200901191505633 19-OCA-09         32 .666666667
200901191507200 19-OCA-09         42          1
200902261508282 26-ŞUB-09         10          0
200902261503189 26-ŞUB-09         14         .5
200902261506519 26-ŞUB-09         20          1
200903031534721 03-MAR-09         12          0
200903031534449 03-MAR-09         16         .5
200903031534950 03-MAR-09         35          1

***************************************************************************

PERCENTILE_DISC = CUME_DIST fonksiyonunun verdigi degere esit veya buyuk ilk degeri almaya calisir. mesela ornekte 0.6ya esit veya buyuk ilk deger 1. sorguda gozuktugu gibi 20dir
PERCENTILE_CONT = Verilen yüzdelik değere göre ara değer üretir. formulü vardır. 0.6 yüzdelik değerine göre hesaplama yapalım
PERCENTILE_CONT(X) = ( eger (CRN = FRN = RN) ise (RN satirindaki deger) degilse (CRN – RN) ) * (FRN satirindaki deger ) + (RN -FRN) * (CRN satirindaki deger).
RN= (1+x*(n-1))= 1+0.6*(10-1) = 6.4
CRN = ceil(RN) = 7
FRN = FLOOR(RN) = 6
PERCENTILE_CONT(0.6) = (7-6.4)*20 + (6.4-6) * 30 = 24

select IDX, TARIH, TUTAR, CUME_DIST() OVER (ORDER BY TUTAR) CDist from ttest order by TUTAR;

IDX             TARIH          TUTAR      CDIST
–
200902261508282 26-ŞUB-09         10         .1
200903031534721 03-MAR-09         12         .2
200902261503189 26-ŞUB-09         14         .3
200901191509347 19-OCA-09         15         .4
200903031534449 03-MAR-09         16         .5
200902261506519 26-ŞUB-09         20         .6
200901191507345 19-OCA-09         30         .7
200901191505633 19-OCA-09         32         .8
200903031534950 03-MAR-09         35         .9
200901191507200 19-OCA-09         42          1
SELECT PERCENTILE_DISC(0.6) WITHIN GROUP (ORDER BY TUTAR) AS perc_disc,
PERCENTILE_CONT(0.6) WITHIN GROUP (ORDER BY TUTAR) AS perc_cont
FROM ttest;

PERC_DISC  PERC_CONT

24

***************************************************************************

Yaklasik degerlerin bulunmasi
ornekte gozuktugu uzere 23 tutarin yaklasik rank degeri 5dir. yani yaklasik olarak 5.siradadir

SELECT   RANK (23) WITHIN GROUP (ORDER BY TUTAR DESC) AS hrank,  yaklasik olarak 23, 5.sirada
PERCENT_RANK (23) WITHIN GROUP (ORDER BY TUTAR) AS hperc_rank, yaklasik olarak 23’un yuzdelik degeri 0.6
CUME_DIST (23) WITHIN GROUP (ORDER BY TUTAR) AS hcume_dist yaklasik olarak 23’un cum_dist degeri 0.6363
FROM ttest;

HRANK HPERC_RANK HCUME_DIST

.6 .636363636

select  * from ttest t order by tutar desc;

IDX             AD                        TUTAR S TARIH
– –
200901191507200 AHM                          42 W 19-OCA-09
200903031534950 KEM                          35 N 03-MAR-09
200901191505633 SAL                          32   19-OCA-09
200901191507345 ONU                          30 N 19-OCA-09
200902261506519 AAT                          20 N 26-ŞUB-09
200903031534449 CAN                          16 N 03-MAR-09
200901191509347 PET                          15 N 19-OCA-09
200902261503189 SAD                          14 W 26-ŞUB-09
200903031534721 ERM                          12 N 03-MAR-09
200902261508282 SUL                          10 N 26-ŞUB-09
************************************************************************

WIDTH_BUCKET, belirtilen aralıkta belirtilen kayıt kümesini degere uygun olarak parçalara ayırır
ornekte gozuktugu gibi, 0 ve 50 bizim min ve max araligimiz, buradaki degerleri 10 parcaya bolcez
42 max!a yakin yani 9 degeri almis,  20 ortalarda oldugu icin 5, 10 3 almis. 10 dan daha kucuk degerler olsaydı onlar da 1 ve 2 alacakti

WIDTH_BUCKET(alan, dusukdeger, yuksekdeger, parca_sayisi)

SELECT TUTAR,
WIDTH_BUCKET(TUTAR,0,50,10) AS WB_UP,
WIDTH_BUCKET(TUTAR,50, 0, 10) AS WB_DOWN
FROM ttest order by tutar desc;

TUTAR      WB_UP    WB_DOWN

42          9          2
35          8          4
32          7          4
30          7          5
20          5          7
16          4          7
15          4          8
14          3          8
12          3          8
10          3          9

***************************************************************************

FIRST_VALUE / LAST_VALUE  kayit kumesindeki ilk  degeri / son degeri verir.
ornekte goruldugu uzere, tarih’e gore kayit kumelerinde ilk kayiti ve son kayitin degerini almis. ilk kume icin ild deger 15 son deger 42 dir.
ornekte order by da kullanilabilirdi. FIRST_VALUE(TUTAR) OVER (PARTITION BY TARIH ORDER BY TUTAR) FV gibi yani. Fakat o zaman son satir o anki satir olarak goze alinirdi.

SELECT IDX, TARIH, TUTAR,
FIRST_VALUE(TUTAR) OVER (PARTITION BY TARIH) FV,
LAST_VALUE(TUTAR) OVER (PARTITION BY TARIH) LV
FROM ttest;

IDX             TARIH          TUTAR         FV         LV
–
200901191509347 19-OCA-09         15         15         42
200901191507345 19-OCA-09         30         15         42
200901191505633 19-OCA-09         32         15         42
200901191507200 19-OCA-09         42         15         42
200902261506519 26-ŞUB-09         20         20         10
200902261503189 26-ŞUB-09         14         20         10
200902261508282 26-ŞUB-09         10         20         10
200903031534721 03-MAR-09         12         12         35
200903031534449 03-MAR-09         16         12         35
200903031534950 03-MAR-09         35         12         35

***************************************************************************

bazen belirli sayide satir uretmek isteriz. kayit iceren bi tabloya ihtiyac vardir. kucuk bi tablo alsam da icinden gereksiz bilgi alsam deriz.
bu extra bilgi ama lazım olabilir. dual ve CONNECT BY ile bunu halledebiliriz.

SELECT trim(to_char(rownum-1,’00’)) SAAT FROM dual CONNECT BY LEVEL <= 5;

SAAT
–
00
01
02
03
04

incelenebilir : http://download.oracle.com/docs/cd/E11882_01/server.112/e10810/analysis.htm

join metodları

A. Hash join :
1. Tablolardan daha küçük olan alınır.
2. Bu tablonun join key’i alınır, hash algoritması uygulanarak bellekte bir hash tablosu yaratılır.
3. Daha büyük olan tablonun join key’inin hash değeri alınır ve bellekteki hash tablosu ile karşılaştırılarak join işlemi yapılır.
Ne zaman tercih edilir?
a. Tablolar çok fazla satırdan oluşuyorsa
b. Küçük tablonun büyük kesimi join yapılacaksa
Not: Hash join’in uygulanması için iki tablo arasındaki  join  eşitlik koşulu (=) içermeli. USE_HASH hint’i kullanılarak bu join’e  zorlanabilir.

B. Merge Join  (Sort merge join):
     1. Birinci tablo join key’e göre sıralanır.
2. İkinci tablo join key’e göre sıralanır.
3. Join Key’lere göre join işlemi yapılır.
Ne zaman tercih edilir?
a. Tablolar zaten sıralı gelmişse
b. Diğer operasyonlar için daha sonra sıralama gerekiyorsa
c. İki tablo arasında join koşulunda equi-join yoksa (… from a,b where a.id =b.id gibi bi şart yoksa) , yani  join koşulu eşitsizlik  koşulu (<, <=, >, veya >=) içeriyorsa
d. Her iki tabloda da index yoksa
Not: Bu join metodunda index kullanılmaz, her iki tablo full-table scan yapılır. USE_MERGE hint’i kullanılarak bu join’e  zorlanabilir.

C. Nested loops :
1. Optimizer, outher (driving) ve inner tabloyu belirler.
2. Outher tablonun her join key’i için inner tablonun join key’leri karşılaştırılarak join işlemi yapılır.
Ne zaman tercih edilir?
a. Tablolar az sayıda satırdan oluyor ve etkili bir join koşulu varsa
Not: Outher tablonun her join key’i için inner join üzerinde full scan yapılacağı için inner tablonun join key’inin index içermesi çok önemlidir. USE_NL hint’i kullanılarak bu join’e  zorlanabilir.

oracle – cube / rollup / grouping / grouping sets

–ROLLUP()       = group by ile belirtilen alan veya ifadelerde tek yönde, sağdan sola alt-toplamları verir
–CUBE()         = group by ile belirtilen alan veya ifadelerde bütün mümkün yönlerde alt-toplamları verir
–GROUPING()     = 1 veya 0 değerin döner. Belirtilen alan ile alt-toplam yapıldıysa 1, yapılmadıysa 0 döner.
–GROUPING SETS  = Sadece istedilen alan veya alan gruplarını alt-toplamını verir. (Hatırlarma : Cube() fonksiyonu tüm mümkün alt-toplamları veriyordu)
—
–örnekler için zemin hazırlanıyor. tablolar oluşturuluyor ve kayıtlar ekleniyor.

CREATE TABLE O_EMP
(
USER_ID  NUMBER                               NOT NULL,
DEPT_ID  NUMBER                               NOT NULL,
CITY_ID  NUMBER,
NAME     VARCHAR2(30BYTE)
);
CREATE UNIQUE INDEX O_EMP_PK ON O_EMP(USER_ID);
ALTER TABLE O_EMP ADD(CONSTRAINT O_EMP_PK PRIMARY KEY(USER_ID)USING INDEX);

CREATE TABLE O_SALARY
(
USER_ID  NUMBER                               NOT NULL,
SALARY   NUMBER                               NOT NULL
);

CREATE UNIQUE INDEX O_SALARY_PK ON O_SALARY(USER_ID);
ALTER TABLE O_SALARY ADD(CONSTRAINT O_SALARY_PK PRIMARY KEY(USER_ID)USING INDEX);

insert into O_EMP values(1,10,34,’OMER’);
insert into O_EMP values(2,10,34,’CİHAN’);
insert into O_EMP values(3,18,34,’MELİKE’);
insert into O_EMP values(4,10,34,’OGÜN’);
insert into O_EMP values(5,10,34,’GÜLİN’);
insert into O_EMP values(6,8,16,’NOYAN’);
insert into O_EMP values(7,8,16,’KEMAL’);
insert into O_EMP values(8,8,16,’İZZET’);
insert into O_EMP values(9,18,34,’İLKNUR’);
insert into O_EMP values(10,10,34,’MEHMET’);
insert into O_SALARY values(1,100);
insert into O_SALARY values(2,90);
insert into O_SALARY values(3,120);
insert into O_SALARY values(4,140);
insert into O_SALARY values(5,120);
insert into O_SALARY values(6,80);
insert into O_SALARY values(7,92);
insert into O_SALARY values(8,100);
insert into O_SALARY values(9,190);
insert into O_SALARY values(10,140);

SELECT  * from o_emp e,o_salary s WHERE e.user_id = s.user_id;

–ÖRNEKLER

–Sadece 3 farklı şekilde alt-toplam alıyor.

SELECT dept_id, city_id,SUM(salary) salary,COUNT(1) numemps
from o_emp e,o_salary s
WHERE e.user_id = s.user_id
GROUP BY GROUPING SETS(dept_id, city_id,(dept_id,city_id));

–Sağdan sola doğru tek yönde alt-toplam alıyor, ilk önce city_id ve dept_id yi grupluyor, sonra sadece dept_id ye göre sonra da hepsine göre

SELECT dept_id, city_id,SUM(salary) salary,COUNT(1) numemps
from o_emp e,o_salary s
WHERE e.user_id = s.user_id
GROUP BY ROLLUP(dept_id, city_id)
ORDER by GROUPING(dept_id),GROUPING(city_id);

–Mümkün tüm yönlerde alt-toplamları alıyor. Kartezyan çarpımı gibi.

SELECT dept_id, city_id,SUM(salary) salary,COUNT(1) numemps
from o_emp e,o_salary s
WHERE e.user_id = s.user_id
GROUP BY CUBE(dept_id, city_id)
ORDER by GROUPING(dept_id),GROUPING(city_id);