Database thingies

Ovo je takodje jedna od izuzetno kvalitetnih knjiga na temu svakodnevnog rada sa Oracle bazom, u kojoj je Thomas Kyte ( http://asktom.oracle.com/) jos jednom pokazao svoje majstorske vjestine i dokazao da je istinski Oracle guru.

Ne samo da je u tehnickom pogledu odlicno prenio svoje znanje na papir, nego se i u knjizevnom pogledu nametnuo kao iznadprosjecan autor.

Knjiga je namijenjena i administratorima i programerima, jer svakodnevni rad sa Oracle bazom ukljucuje obe aktivnosti i usku povezanost oba tima, sto je Tom odlicno objasnio na samom pocetku knjige. Naveo je ceste situacije u kojima se administratori i programeri smatraju kao dva protivnicka tima, koji rade jedan protiv drugog, umjesto da kolegijalno saradjuju i medjusobno se pomazu.

Osim sto se u knjizi nalazi razno teoretsko polemisanje i savjetovanje po principu “To se ne treba tako raditi“/”To se treba ovako raditi“, knjiga sadrzi mnostvo primjera sa popratnim objasnjenjem.

Tako je npr. odlicno objasnjeno koristenje EXPLAIN PLAN izraza, kako ga ispravno koristiti i kako ga ispravno tumaciti. Pri tome je objasnio kako se koriste alati za “tracing” i analizu “tracing” podataka (StatsPack, TKProf, AUTOTRACE i td.).

Zanima vas objasnjenje kako upiti prolaze parsing, executing i fetching fazu? Ima i to.

Pitate se kako funkcionise Cost Based Optimizer i koje hintove mogu koristiti u upitu? Procitajte u poglavlju 6.

Imate dilemu kako dizajnirati odredjenu tabelu? Da li ju particionisati ili ne? Da li koristiti index na jednoj koloni ili da indexiram vise njih? Koje STORAGE opcije definisati - extent od 1M ili 10M? Da li omoguciti paralelne upite nad tabelom ili ne? Na ova i mnoga druga pitanja, Tom je prakticnim primjerima objasnio optimalno kreiranje tabela i indexa.

U poglavlju 8, “Effective SQL“, mozete procitati koje vrste joina postoje i kako tacno funkcionisu, koristenje Top-N upita, zatim fenomenalno objasnjeno koristenje analitickih funkcija (ROW_NUMBER, DENSE_RANK, RANK, NTILE i td.) i nazalost slabo koristeni pivoting (u zadnje vrijeme sam prepravio mnogo PL/SQL procedura da koriste pivoting upite za izvlacenje podataka iz tabela, a ne algoritme, koji u vise koraka uzimaju podatke i naknadno ih obradjuju; mozda cu uskoro napisati i par primjera o pivoting upitima).

Koristite li BULK processing za ETL operacije? Ne znate sta je to i kako se koristi? E, pa procitajte poglavlje 9 - “Effective PL/SQL Programming“.

I na kraju imate nekoliko savjeta za ponasanje u slucaju problema sa bazom, kao i par gotovih skripti, koje mozete prepisati i slobodno koristiti.

Ja licno nemam visegodisnje iskustvo sa Oracleom (tek sam se 2003. godine poceo baviti Oracleom), ali nakon citanja ove knjige, moje znanje je poraslo kao da se bavim 10 godina Oracle programiranjem (al’ sam skroman ).

Ocjena: 10/10

Sa mnom u firmi radi nekoliko Java programera, kojima katkad treba objasniti kako funkcionisu neke stvari u Oracle bazi. Nakon sto sam njima objasnio kako funkcionise klauzula “ON DELETE CASCADE“, odlucio sam i ovdje napisati nesto o tome.

Konkretno, pita mene kolega:”Ako ja iz jedne parent tabele obrisem jedan unos, sta ce biti sa unosima u child tabeli? Da li ja moram da vodim evidenciju o tim promjenama i onda u Javi isprogramiram da se obrisu i svi referencijalni unosi u child tabeli?”

Moj odgovor je bio kratak i jasan - “ON DELETE CASCADE“.

Pa da objasnim na jednom primjeru.

Imamo parent tabelu pod nazivom “KATEGORIJE_PROIZVODA” i jednu child tabelu pod nazivom “PROIZVODI“. Ukoliko iz tabele “KATEGORIJE_PROIZVODA” obrisemo neku kategoriju, svi referencijalni unosi u tabeli “PROIZVODI” trebaju takodje biti obrisani.

Ovdje mozete skinuti DDL i DML izraze potrebne za ovaj primjer (download DDL Create statements; download DML Insert statements), a ovako izgleda rezultat nakon izvrsavanja tih izraza:

C:>sqlplus /nolog 

SQL*Plus: Release 10.2.0.1.0 - Production on So März 18 16:35:58 2007 

Copyright (c) 1982, 2005, Oracle.  All rights reserved. 

SQL> conn scott/scottpass 

Connect durchgef³hrt.

SQL> CREATE TABLE KATEGORIJE_PROIZVODA
  2  (
  3    ID     NUMBER NOT NULL,
  4    NAZIV  VARCHAR2(32 BYTE),
  5    OPIS   VARCHAR2(64 BYTE),
  6    CONSTRAINT pk_kat_proizvod PRIMARY KEY (ID) USING INDEX
  7  ); 

Tabelle wurde erstellt. 

SQL> CREATE SEQUENCE SCOTT.SEQ_KAT_PROIZVOD_ID
  2    START WITH 1
  3    MAXVALUE 99999999999999
  4    MINVALUE 1
  5    NOCYCLE
  6    NOCACHE
  7    ORDER; 

Sequence wurde erstellt. 

SQL> CREATE OR REPLACE TRIGGER TR_KAT_PROIZVOD_ID
  2  BEFORE INSERT
  3  ON SCOTT.KATEGORIJE_PROIZVODA 

  4  REFERENCING NEW AS NEW OLD AS OLD
  5  FOR EACH ROW
  6  BEGIN
  7
  8     SELECT SEQ_KAT_PROIZVOD_ID.NEXTVAL INTO :NEW.ID FROM dual;
  9
 10  EXCEPTION
 11    WHEN OTHERS THEN
 12      NULL;
 13  END tr_kat_proizvod_id;
 14  / 

Trigger wurde erstellt. 

SQL> CREATE TABLE PROIZVODI
  2  (
  3    ID            NUMBER NOT NULL,
  4    NAZIV         VARCHAR2(32 BYTE),
  5    KAT_PROIZ_ID  NUMBER,
  6    CONSTRAINT pk_proizvod PRIMARY KEY(ID) USING INDEX,
  7    CONSTRAINT fk_kat_proizvod FOREIGN KEY (kat_proiz_id)
  8      REFERENCES KATEGORIJE_PROIZVODA(ID)
  9      ON DELETE CASCADE
 10  ); 

Tabelle wurde erstellt. 

SQL> CREATE SEQUENCE SCOTT.SEQ_PROIZVOD_ID
  2    START WITH 1
  3    MAXVALUE 99999999999999
  4    MINVALUE 1
  5    NOCYCLE
  6    NOCACHE
  7    ORDER;
Sequence wurde erstellt. 

SQL> CREATE OR REPLACE TRIGGER TR_PROIZVOD_ID
  2  BEFORE INSERT
  3  ON SCOTT.PROIZVODI
  4  REFERENCING NEW AS NEW OLD AS OLD 

  5  FOR EACH ROW
  6  BEGIN
  7
  8     SELECT SEQ_PROIZVOD_ID.NEXTVAL INTO :NEW.ID FROM dual;
  9
 10  EXCEPTION
 11    WHEN OTHERS THEN
 12      NULL;
 13  END tr_proizvod_id;
 14  / 

Trigger wurde erstellt. 

SQL> DECLARE
  2    lnKat1 NUMBER;
  3    lnKat2 NUMBER;
  4    lnKat3 NUMBER;
  5  BEGIN
  6     INSERT INTO KATEGORIJE_PROIZVODA (naziv, opis)
  7     VALUES('Cokoladice', 'Mljac njam')
  8     RETURNING ID INTO lnKat1;
  9
 10     INSERT INTO KATEGORIJE_PROIZVODA (naziv, opis)
 11     VALUES('Alkoholna pica', 'Bolje biti pijan, nego star')
 12     RETURNING ID INTO lnKat2;
 13
 14     INSERT INTO KATEGORIJE_PROIZVODA (naziv, opis)
 15     VALUES('Cigare', 'Pusenje ubija')
 16     RETURNING ID INTO lnKat3;
 17
 18     INSERT INTO PROIZVODI (naziv, KAT_PROIZ_ID)
 19     VALUES ('Snickers', lnKat1);
 20
 21     INSERT INTO PROIZVODI (naziv, KAT_PROIZ_ID)
 22     VALUES ('Snickers Cruncher', lnKat1);
 23
 24     INSERT INTO PROIZVODI (naziv, KAT_PROIZ_ID)
 25     VALUES ('Duplo', lnKat1);
 26
 27     INSERT INTO PROIZVODI (naziv, KAT_PROIZ_ID)
 28     VALUES ('Mars', lnKat1);
 29
 30     INSERT INTO PROIZVODI (naziv, KAT_PROIZ_ID)
 31     VALUES ('Zivotinjsko carstvo', lnKat1);
 32
 33     INSERT INTO PROIZVODI (naziv, KAT_PROIZ_ID)
 34     VALUES ('Milky Way', lnKat1);
 35
 36     INSERT INTO PROIZVODI (naziv, KAT_PROIZ_ID)
 37     VALUES ('Twix', lnKat1);
 38
 39     INSERT INTO PROIZVODI (naziv, KAT_PROIZ_ID)
 40     VALUES ('Heineken pivo', lnKat2);
 41
 42     INSERT INTO PROIZVODI (naziv, KAT_PROIZ_ID)
 43     VALUES ('Tuborg pivo', lnKat2);
 44
 45     INSERT INTO PROIZVODI (naziv, KAT_PROIZ_ID)
 46     VALUES ('Bavaria pivo', lnKat2);
 47
 48     INSERT INTO PROIZVODI (naziv, KAT_PROIZ_ID)
 49     VALUES ('Budweiser pivo', lnKat2);
 50
 51     INSERT INTO PROIZVODI (naziv, KAT_PROIZ_ID)
 52     VALUES ('Marlboro', lnKat3);
 53
 54     INSERT INTO PROIZVODI (naziv, KAT_PROIZ_ID)
 55     VALUES ('Morava', lnKat3);
 56
 57     INSERT INTO PROIZVODI (naziv, KAT_PROIZ_ID)
 58     VALUES ('Ronhill', lnKat3);
 59
 60     INSERT INTO PROIZVODI (naziv, KAT_PROIZ_ID)
 61     VALUES ('Davidoff', lnKat3);
 62  END;
 63  / 

PL/SQL-Prozedur erfolgreich abgeschlossen. 

SQL>

Nakon sto smo kreirali tabele i unijeli podatke, pogledajmo trenutni sadrzaj tih tabela. 

SQL> SELECT * FROM KATEGORIJE_PROIZVODA;

        ID NAZIV                            OPIS
---------- -------------------------------- --------------------------------
         1 Cokoladice                       Mljac njam
         2 Alkoholna pica                   Bolje biti pijan, nego star
         3 Cigare                           Pusenje ubija

SQL> SELECT * FROM proizvodi;

        ID NAZIV                            KAT_PROIZ_ID
---------- -------------------------------- ------------
         1 Snickers                                    1
         2 Snickers Cruncher                           1
         3 Duplo                                       1
         4 Mars                                        1
         5 Zivotinjsko carstvo                         1
         6 Milky Way                                   1
         7 Twix                                        1
         8 Heineken pivo                               2
         9 Tuborg pivo                                 2
        10 Bavaria pivo                                2
        11 Budweiser pivo                              2
        12 Marlboro                                    3
        13 Morava                                      3
        14 Ronhill                                     3
        15 Davidoff                                    3

15 Zeilen ausgewõhlt.

Ukoliko su uneseni podaci ispravni, obrisacemo kategoriju “Cigare“, jer je zakonom zabranjeno pusenje na javnim mjestima i nije vise isplativo prodavati cigare.

Posto imamo referencijalni integritet (Foreign key) izmedju parent i child tabele, u child tabeli “PROIZVODI” bi svi proizvodi iz kategorije “Cigare” trebali biti obrisani.

SQL> DELETE FROM KATEGORIJE_PROIZVODA
  2  WHERE ID = 3;

1 Zeile wurde gelöscht.

Provjerimo jos jednom sadrzaj obe tabele i uvjerimo se da su obrisani i kategorija i svi proizvodi iz te kategorije.

SQL> SELECT * FROM KATEGORIJE_PROIZVODA;

        ID NAZIV                  OPIS
---------- ---------------------- ---------------------------
         1 Cokoladice             Mljac njam
         2 Alkoholna pica         Bolje biti pijan, nego star

SQL> SELECT * FROM proizvodi;

        ID NAZIV                   KAT_PROIZ_ID
---------- ----------------------- ------------
         1 Snickers                           1
         2 Snickers Cruncher                  1
         3 Duplo                              1
         4 Mars                               1
         5 Zivotinjsko carstvo                1
         6 Milky Way                          1
         7 Twix                               1
         8 Heineken pivo                      2
         9 Tuborg pivo                        2
        10 Bavaria pivo                       2
        11 Budweiser pivo                     2

11 Zeilen ausgewõhlt.

Jednostavno i efikasno. Java programeri ne moraju vise da vode brigu o parent-child vezama prilikom brisanja podataka.

Ukoliko ne zelite da podaci iz child tabele budu obrisani, mozete umjesto klauzule “ON DELETE CASCADE” koristiti klauzulu “ON DELETE SET NULL“, pri cemu ce kat_proiz_id dobiti vrijednost NULL ukoliko bude obrisana pripadajuca kategorija iz parent tabele.

Nakon sto sam procitao ovu knjigu, red bi bio da napisem i kratku recenziju o njoj.

Knjiga je novije izdanje prethodne knjige “Oracle database 9i: RMAN Backup & Recovery“, s tim sto su dodane novosti i izmjene sadrzane u verziji 10g. Ko je procitao knjigu za verziju 9i, odusevice se i ovom.

U knjizi su obuhvacene mnoge stvari: od definicije i objasnjenja sta su to backup, recovery, struktura baze i sl., preko opisa trenutno najboljih backup rjesenja na trzistu, pa sve do “korak-po-korak” objasnjenja pojedinih scenarija backup i recovery strategije. Bas ova “korak-po-korak” objasnjenja su odlicno napisana i nude konkretno rjesenje za mnoge “live” slucajeve.

Navescu samo neke od njih:
- kloniranje baze pomocu RMAN-a
- podesavanje StandBy baze
- podesavanje pomocne (auxiliary) instance za “tablespace point in time recovery”
- recovery nakon gubitka baze, tablespacea ili pojedinih fajlova
- inkrementalni backup

i td.

Osim sto je tehnicki stvarno odlicno napisana, autori su se potrudili knjigu uciniti i duhovitom, kako citaoci ne bi bili smoreni suhoparnim informacijama. Evo par takvih primjera:
“Well, let’s get started with this RMAN thing, shall we? I’ll just reach down, pull on the handle… I said pull on the handle… and, it doesn’t start.”
“One of the other ways you can leverage your backups is to use them to make copies of the production database for testing and development purpose, in the database world, we refer to this as cloning (keep your sheep jokes to yourself).”
“Today is not Bill’s day. A large thunderstorm is raging outside, and Bill has forgotten that his car’s soft top is down. To make Bill’s day worse, a strike of lightning hits the data center and fries several disk drives that Bill’s database calls home.”

Tu su jos i objasnjenja kako koristiti backup u RAC okruzenjima, kako se dobro pripremiti za disaster recovery slucajeve, kako poboljsati performanse backupa (splitting, stripping, multiplexing i td.), a ima i dosta primjera sa koristenjem Enterprise Managera, tako da se mnoge stvari ne moraju raditi u RMAN command line clientu.

Meni knjiga ni na trenutak nije bila dosadna i mogu ju mirne duse preporuciti dalje.

Knjigu sam kupio preko Amazona za 59.99 dolara, ali je sad pojeftinila.

Ocjena: 10/10

Evo bas sam danas jednom kolegi programeru objasnjavao statuse :NEW i :OLD varijabli pri odredjenom trigger fire-eventu (mozebitni prevod: okidacki dogadjaj?) . Za tu svrhu sam koristio jedan najobicniji primjer, koji cu pokazati i ovdje.

Najprije moramo napraviti tabelu i unijeti testne podatke:

CREATE TABLE test_table(id NUMBER, tekst VARCHAR2(32));

INSERT INTO test_table VALUES(1, ‘tekst1′);
INSERT INTO test_table VALUES(2, ‘tekst2′);
INSERT INTO test_table VALUES(3, ‘tekst3′);
INSERT INTO test_table VALUES(4, ‘tekst4′);
INSERT INTO test_table VALUES(5, ‘tekst5′);

SELECT * FROM test_table;

ID TEKST
– ——
1 tekst1
2 tekst2
3 tekst3
4 tekst4
5 tekst5

Nakon toga kreiramo trigger:

CREATE OR REPLACE TRIGGER tr_old_new
BEFORE UPDATE OR INSERT OR DELETE ON test_table
FOR EACH ROW
BEGIN
  IF UPDATING THEN
    dbms_output.put_line('Updating: ');
    dbms_output.put_line('old id: '||:OLD.id);
    dbms_output.put_line('new id: '||:NEW.id);
    dbms_output.put_line('old tekst: '||:OLD.tekst);
    dbms_output.put_line('new tekst: '||:NEW.tekst);
  END IF;  IF INSERTING THEN
    dbms_output.put_line('Inserting: ');
    dbms_output.put_line('old id: '||:OLD.id);
    dbms_output.put_line('new id: '||:NEW.id);
    dbms_output.put_line('old tekst: '||:OLD.tekst);
    dbms_output.put_line('new tekst: '||:NEW.tekst);
  END IF;  IF DELETING THEN
    dbms_output.put_line('Deleting: ');
    dbms_output.put_line('old id: '||:OLD.id);
    dbms_output.put_line('new id: '||:NEW.id);
    dbms_output.put_line('old tekst: '||:OLD.tekst);
    dbms_output.put_line('new tekst: '||:NEW.tekst);
  END IF;EXCEPTION
  WHEN OTHERS THEN
  dbms_output.put_line(SQLERRM);
END tr_old_new;
/

Nakon sto smo uspjesno kreirali tabelu i trigger, mozemo napraviti test za svaki fire-event: UPDATE, INSERT i DELETE. Ne zaboravite aktivirati DBMS Output (u sqlplusu akvitirajte ovako “set serveroutput on“, a za alat, koji vec koristite znate vec i sami kako da aktivirate DBMS output. ).

INSERT INTO test_table VALUES(6, 'tekst6');
Trebalo bi se ispisati slijedece:

Inserting:
old id:
new id: 6
old tekst:
new tekst: tekst6

UPDATE test_table SET id = 7 WHERE id = 6;

Trebalo bi se ispisati slijedece:

Updating:
old id: 6
new id: 7
old tekst: tekst6
new tekst: tekst6

DELETE FROM test_table WHERE id = 7;

Trebalo bi se ispisati slijedece:

Deleting:
old id: 7
new id:
old tekst: tekst6
new tekst:

Mislim da je iz samog ispisa vidljivo koje vrijednosti sadrze :NEW i :OLD varijable prilikom okidanja triggera.

Nakon sto su prethodni put preuredjene web stranice od Oraclea, zapitao sam se - zasto su taj posao aljkavo uradili i zasto je sve nabacano bez imalo preglednosti. Izgleda da nisam bio jedini, koji se mucio sa preglednoscu Oracleovog web sajta, jer je nedavno Oracle homepage ponovno preuredjen - ovaj put daleko bolje, nego ranije!

 Smanjili su broj razbacanih linkova sa lijeve strane i iskoristili onaj ogromni lose iskoristeni prostor, koji je obuhvatao 70% index stranice. Na zalost, ni sada centralni prostor nije najbolje iskoristen, ali je barem pregledan. Sve podstranice nisu jos preuredjene, ali vremenom ce i one doci na red.

Pisao sam već kako sam u zadnje vrijeme jako nezadovoljan Oracleom i kako forsira nove verzije, iako ni aktualne verzije (trenutno 10g) nisu 100% završene i ispravljene od bugova. Na Metalinku često za neke bugove ili opcije viđam napomenu “Fixed in version: 11g“.

Verzija 10g je donijela dosta novih stvari u odnosu na verziju 8i i 9i, pa se isto tako od verzije 11g očekuje dosta novosti u odnosu na trenutnu verziju 10g. Ja ću navesti samo neke od tih novosti, do kojih sam saznao prateći razne blogove i forume.

 +++ Novosti u verziji 11g (Enhancements in Oracle 11g) +++

- DML triggeri će biti do 25% brži nego sad, što će se posebno primijetiti kod row-level triggera u kojima se vrši unos/izmjena podataka u drugim tabelama (korisno za npr. Audit-trigger).
- Fine Grained Dependancy Tracking (FGDT) - ukoliko dodate kolonu u neku tabelu ili izmijenite package specification (package header), zavisni objekti (dependant objects) neće biti označeni kao invalidni (nevažeći). Jako korisno za production 24×7 baze.
- Native Compilation - kompajliranje PL/SQL kôda ne zahtjeva više C kompajler, nego se interno smješta direktno u shared library. Navodno će isto važiti i za Java kôd u bazi.
- Novi tip podataka - simple_integer - Deklarisan uvijek kao NOT NULL i navodno će biti brži od PLS_INTEGER.
- Keširanje SQL i PL/SQL-a (SQL and PL/SQL result caching) - Biće dodan “result cache” u kojem će se nalaziti rezultati pojedinih SQL upita i PL/SQL procedura, što će višestruko ubrzati naknadna  izvršavanja dotičnih upita i procedura.
- Kombinovani triggeri (compound triggers “all-in-one”) - Zamislite trigger zajednički definisan da bude “okinut” na after, before, row i statement uslove!? Da, navodno će i ta sjajna mogućnost biti dostupna u verziji 11g. A osim toga, u običnim triggerima će biti moguće odrediti redoslijed okidanja pojedinih triggera nad tabelom.
- Dynamic SQL - DBMS_SQL je ubrzan i poboljšan. I Native Dynamic SQL (EXECUTE IMMEDIATE) i DBMS_SQL mogu sada prihvatati CLOB tipove podataka (nema više ograničenja na veličinu od 32k). REF CURSOR se može konvertovati u DBMS_SQL cursor i obrnuto. Pored toga, DBMS_SQL će podržavati BULK operacije i “user defined” tipove.
- Row-level security (RLS), tj. Fine grained access control (FGAC) - Biće npr. moguće odrediti sigurnost za portove i URL-ove koje koriste paketi UTL_TCP, UTL_HTTP i UTL_SMTP.
- Read only tables - Biće moguće podesiti tabelu dozvoljenu samo za čitanje.
- CONTINUE komanda - Može se koristiti za uslovni izlaz iz petlje.
- Poboljšana kompresija za setove podataka - za oko 2/3 smanjiće se potrebna količina prostora za spremanje tih podataka.
- Speed boost - navodno će Oracle mnogo brže čitati podatke sa “raw devices”, nego sa do sada dostupnim file systemima.
- Replay Workload - Mogućnost “hvatanja” (capturing) svih aktivnosti u bazi i kopiranja tih aktivnosti na neki drugi server (npr. na testni server) bez korištenja nekog posebnog alata za tu namjenu.
- Online Application Upgrades - Biće moguće instalirati neki patch bez gašenja baze (hot patching with no downtime).
- Quick Fault Resolution - Automatsko hvatanje svih potencijalno povezanih uzroka neke greške.
- Database Repair Advisor - Asistent (wizard), koji pomaže administratoru pri pronalaženju greške i njenom otklanjanju.
- Biće omogućeno particionisanje po logičkim objektima (partition by logical objects), kao i automatsko particionisanje radi olakšanja pri radu sa VLDB (Very Large Databases).
- Nova high-performance infrastruktura za LOB objekte.
- Re-engineered driver za PHP

Kaze kolega:”Cuo sam da je unos podataka sa INSERT /*+ APPEND */ veoma brz, ali kod mene se vuuuceeee kao penzioner na kraju mjeseca…”

Pa evo zgodne prilike da na primjeru pojasnimo ispravno koristenje INSERT /*+ APPEND */ procesa.

U ovom primjeru imamo dvije tabele:
- source tabelu (source_table) iz koje uzimamo podatke, sa vise polja, od kojih su nama zanimljivi: id i iznos.
- target tabelu (target_table) u koju unosimo podatke iz source tabele, sa poljima: id,  suma_iznosa (suma svih iznosa za pojedini id) i max_iznos (maksimalni iznos za pojedini id).

Da bi unos bio sto brzi, potrebno je deaktivirati sve indekse i triggere na target tabeli.

Ako imamo samo jedan ili par indeksa, onda ih mozemo i rucno deaktivirati:

ALTER INDEX target_table_index1 UNUSABLE;

Ako imate particionisani index, onda ga mozete ovako deaktivirati:

ALTER INDEX target_table_index1 MODIFY PARTITION part1 UNUSABLE;

U suprotnom, za vise indeksa mozemo generisati kod:

sqlplus> spool c:\set_indexes_unusable.sql
sqlplus> SELECT 'ALTER INDEX '|| index_name ||' UNUSABLE;'
  FROM user_indexes
  WHERE table_name = 'TARGET_TABLE';
sqlplus> @C:\set_indexes_unusable.sql

Medjutim, Oracle ne dopusta unos podataka, ukoliko na tabeli postoje deaktivirani indexi (unusable indexes). Stoga moramo implicitno reci Oracleu, da preskoci deaktivirane indexe:

ALTER SESSION SET skip_unusable_indexes = TRUE;

Potom deaktiviramo triggere:

ALTER TABLE target_table DISABLE ALL TRIGGERS;

I mozemo poceti sa unosom:

INSERT /*+ APPEND */
INTO target_table(id, suma_iznosa, max_iznos)
SELECT id, sum(iznos), max(iznos)
FROM source_table
GROUP BY id;

Potvrdimo unos:

COMMIT;

I aktiviramo ponovo indexe i triggere:

ALTER TABLE target_table ENABLE ALL TRIGGERS; 

sqlplus> spool C:\rebuild_indexes.sql
sqlplus> SELECT 'ALTER INDEX ' || index_name || ' REBUILD;'
  FROM user_indexes
  WHERE table_name = 'TARGET_TABLE'
  AND status = 'UNUSABLE';
sqlplus> @C:\rebuild_indexes.sql

To je to.

Ako vam se instanca Oracle baze skrsi sa greskom iz naslova i ukoliko u alert logu pronadjete ovo:

(Na njemackom)
Wed Feb 07 22:44:32 2007
Errors in file c:\ora10g\admin\db10g\bdump\db10g_ckpt_55.trc:
ORA-00221: Fehler beim Schreiben in Kontrolldatei
ORA-00206: Fehler beim Schreiben (Block 3, Anzahl Blöcke 1) der Kontrolldatei
ORA-00202: Kontrolldatei: 'C:\ORA10G\ORADATA\DB10G\CONTROL03.CTL'
ORA-27072: Datei-I/O-Fehler
OSD-04008: WriteFile()-Fehler, in Datei kann nicht geschrieben werden
O/S-Error: (OS 33) The process cannot access the file because another process has locked a portion of the file. Wed Feb 07 22:44:32 2007
CKPT: terminating instance due to error 221

(ili na engleskom)

Wed Feb 07 22:44:32 2007
Errors in file c:\ora10g\admin\db10g\bdump\db10g_ckpt_55.trc:
ORA-00221: error on write to controlfile
ORA-00206: error in writing (block 3, # blocks 1) of controlfile
ORA-00202: controlfile: 'C:\ORA10G\ORADATA\DB10G\CONTROL03.CTL'
ORA-27072: File I/O error
OSD-04008: WriteFile() failure, unable to write to file
O/S-Error: (OS 33) The process cannot access the file because another process has locked a portion of the file.Wed Feb 07 22:44:32 2007
CKPT: terminating instance due to error 221

onda je moguci uzrok tome anti-virusni software ili neki third-party software, koji vrsi odrzavanje sistemske particije (u ovom slucaju C: particija).
U mom slucaju je bilo oboje. Svako vece se vrsi skeniranje sistema anti-virusnim softwareom, a potom se vrsi automatski backup sistemske particije (neki alat za HP SAN storage), koji lockuje controlfile, nakon cega CKPT (Checkpoint process) terminira DB instancu.

Ako ne mozete izbjeci pokretanje tog anti-virusnog i backup softwarea, onda je rjesenje da controlfileove smjestite na neku nesistemsku particiju i necete imati ovakvih situacija.

Naletih na jednu zanimljivu proceduru, kojom mozete direktno zapisivati u alert log ili u trace file.

Radi se o proceduri KSDWRT u paketu DBMS_SYSTEM, a mozete ju koristiti na 3 nacina:

DBMS_SYSTEM.KSDWRT(1, ‘zapisivanje u trace file’);
DBMS_SYSTEM.KSDWRT(2, ‘zapisivanje direktno u alert log’);
DBMS_SYSTEM.KSDWRT(3, ‘zapisivanje i u alert log i u trace file’);

Evo jedan primjer:

- spojite se sa SQL*Plusom na bazu i otkucajte:

sqlplus> exec DBMS_SYSTEM.ksdwrt(2, ‘Sta bi rekli ovi iz Oracle Supporta, kad bi vidjeli ovo u alert logu? ;)’);

Potom otvorite alert log i pronadjite vas zapis na kraju, kao npr.:
Thu Feb 08 15:28:50 2007
Sta bi rekli ovi iz Oracle Supporta, kad bi vidjeli ovo u alert logu?

Posto smo KONACNO presli na verziju 10g (procitati u jednoj od prethodnih poruka, zasto je tek nedavno bilo moguce uraditi upgrade na verziju 10g), dobio sam zadatak da osmislim novi koncept osiguravanja podataka (backup & recovery concept), a radi se o bazi velikoj oko 400 GB (raste oko 40% godisnje). Odabrao sam RMAN (Recovery MANager) kao primarni alat za tu namjenu, a pri osmisljavanju, kreiranju i finalnoj realizaciji tog koncepta, mnogo ce mi pomoci i knjiga “Oracle Database 10g: RMAN Backup & Recovery“, koja mi je upravo danas stigla.

Porucena je i knjiga “Oracle Database 10g Real Application Clusters Handbook“, jer ce zatrebati pri buducoj ekspanziji korisnika i podataka.