II. RF KMF DokuWiki

Az SQL szabad alakú nyelv (Free-Form Language).
DDL (Data Definition Language)

• ALTER PROCEDURE
• ALTER TABLE
• ANALYZE (a teljesítmény statisztikáját gyűjti)
• ALTER TABLE ADD CONSTRAINT
• CREATE TABLE
• CREATE INDEX
• DROP INDEX
• DROP TABLE
• GRANT
• TRUNCATE
• REVOKE

DML (Data Manipulation Language)

• INSERT
• DELETE
• UPDATE
• SELECT
• COMMIT WORK
• ROLLBACK

A táblákat a CREATE TABLE paranccsal lehet létrehozni. Egy táblában ⇐1000 oszlop lehet. Több adattípust is lehet alkalmazni, de gyakorlatilag csak néhányra van szükség.

• NUMBER(h, t) – numerikus adatok, hossza⇐38, h a szám hosszát tartalmazza, t – a tizedes-jegyek számát;
• DATE – dátum és idő típus;
• VARCHAR2(méret) – karakter típus, a hossza váltózó (max hossza⇐4000);
• CHAR(méret) – karakter típus, fix hosszúságú (max hossza⇐2000);
• NCHAR(méret) – azonos a CHAR típussal, de a max méret függ a karakterkészletétől,
• LOB (large objects) – bináris v. szöveges formátum. Lehet kép-, hang-, vagy nagy méretű szöveges állomány.
• CLOB, NCLOB, BLOB – belső LOB típusok, mivel az AB-ban tárolódnak, max hossza⇐4 Gbyte;
• NCLOB – az adatokat tárolja a nemzeti karakter-készletben.

LOB típuson belül lehetnek:

• CLOB – az adatokat tárolja az AB karakter-készlete alapján;
  
• LONG – szöveges adatok, max hossza⇐2 Gbyte;
  
• LONG RAW – bináris adatok, max hossza⇐2 Gbyte;
  

A LONG és LONG RAW típusokat csak egyszerű adatfeldolgozásban alkalmazzák.

Az Oracle mindegyik táblába automatikusan helyez egy ROWID nevű pszeudooszlopot, amely a programozók ritkán szokták használni.
ROWID – pszeudooszlop, amely tartalmazza a sor logikai egyedi címét. A ROWID nem változható meg, de a SELECT parancsban lekérdezhető:
SELECT rowid FROM tabla_1;
A Sor sorszáma – olyan szám, amely akkor rendelődik a sorhoz, amikor az bekerül a táblába. Ez a szám része a ROWID-nek.

Csak az a felhasználó hozhat létre táblákat, amely CREATE TABLE vagy CREATE ANY TABLE privilégiummal rendelkezik.

A STORAGE paraméter alkalmazása a CREATE TABLE parancsban.
Egy táblát a STORAGE memória-paraméterek nélkül is lehet létrehozni. Ebben az esetben a tábla az alapértelmezési értékeket kapja, ami nem mindig jó megoldás. A memória egységeket (szegmenseket), melyekben a táblák tárolódnak, extentnek nevezzük. Az első extent neve INITIAL, a többi extentek pedig másodlagos extentek.

 CREATE TABLE testtab	(col1 VARCHAR2(10))
STORAGE (INITIAL 100K
	NEXT 50K 
	MINEXTENTS 1
	MAXEXTENTS 99
	PCINCREASE 20);

Az első extent 100K memóriát kap, a második – 50K. A PCINCREASE 20 érték alapján mindegyik következő extent mérete 20% növekszik az előzőhez képest. MINEXTENTS 1 azt jelenti, hogy a tábla először 1extentent kap, a MAXEXTENTS 99 – a tábla maximálisan 99 extentet kaphat.

A táblákat, mint az indextáblákat is, particionálhatjuk. A particionálás lehetőséget ad a nagyon nagy táblák és indextáblák kezelésére. A particionálás során a nagy táblát kisebb és könnyebben kezelhető részekre osztja a rendszer. Partíciónált tábla – olyan tábla, amelynek sorai kisebb azonos szerkezetű táblákba, partíciókra, vannak szétosztva. A partíciók fizikailag különböző helyeken tárolhatjuk.
A partíciónálás előnyei:

• Az I/O-terhelés jobb eloszlása
• Biztonsági mentés és visszaállítás egyszerűbb végrehajtása
• Az adatsérülés lehetőségének minimalizálása
• Az archiválási folyamat segítése.

Példa.

  CREATE TABLE nagy_tabla
(col1 VARCHAR2(10),
(col2 VARCHAR2(10))
           )
PARTITION BY RANGE (col1, col2)
	(PARTITION p1 VALUES LESS THEN (…)  TABLESPACE p1,
           	   PARTITION p2 VALUES LESS THEN (…)  TABLESPACE p2);

Hivatkozás egy partícióra:

  ALTER TABLE nagy_tabla DROP PARTITION p1;

ALTER TABLE parancs
Az oszlop hosszát lehet növelni még akkor is, ha a tábla adatokat tartalmaz. Az oszlop hosszát pedig csak akkor lehet csökkenteni, ha a tábla üres.

 ALTER TABLE testtab ADD col2 VARCHAR2(100);
 ALTER TABLE testtab MODIFY col2 VARCHAR2(150);
 ALTER TABLE testtab STORAGE ( NEXT 10K
   MAXEXTENTS 50);
 ALTER TABLE testtab DROP COLUMN  col1;

A táblákat átnevezhetjük:

	RENAME testtab TO testtab1;

és törölhetjük:

	DROP TABLE testtab; 

Példa.

 CREATE TABLE students(
      id                     NUMBER(5) PRIMARY KEY,
     first_name        VARCHAR2(20),
     last_name         VARCHAR2(20),
     major                VARCHAR2(30),                -- szak
     current_credits  NUMBER(3) );                    -- leadott vizsgák száma

Az ORACLE-rendszer tartalmazz egy bemutató sémát, amelynek Scott a tulajdonosa:

   CREATE TABLE scott.dept 
 	(deptno  NUMBER(2) NOT NULL,
 dname  VARCHAR2(14),
 loc  VARCHAR2(13),
CONSTRAINT pk_dept PRIMARY KEY (deptno));

   CREATE TABLE scott.emp 
 	(empno  NUMBER(4) NOT NULL,
 ename  VARCHAR2(10),
 job       VARCHAR2(9),
 	 mgr  NUMBER(4),
           hiredate  DATE,
 	 sal           NUMBER(7,2),
 	 comm           NUMBER(7,2),
 	 sal           NUMBER(7,2),
           deptno           NUMBER(2),
CONSTRAINT pk_emp PRIMARY KEY (empno));

Amikor egy olyan táblára (vagy más Oracle-objektumra) hivatkozunk, melynek nem mi vagyunk a tulajdonosa, akkor a tábla nevén kívül meg kell adni a tulajdonos nevét is

		tulajdonos.tábla_név 

Hivatkozási integritás biztosítja, hogy egy adott oszlop csak olyan értékeket vehessen fel, amelyek megjelennek egy másik oszlopban. Integritási megszorításként lehet előírni.
Megszorítás – egy szabály vagy korlátozás az adatok egy részén kerül ellenőrzésre.
Integritási megszorítás – olyan szabály, amely egy vagy több oszlop lehetséges értékeinek körét határolják be. A megszorítást a tábla létrehozásánál az oszlopokhoz lehet hozzárendelni.

A CONSTRAINT záradékot a CREATE TABLE vagy ALTER TABLE parancsokban használhatjuk. A legtöbb megszorítás a CONSTRAINT záradék nélkül is megadható. Léteznek:

• táblamegszorítások
• oszlopmegszorítások.

Táblamegszorítás – olyan integritási megszorítás, amelyet egy tábla több oszlopára alkalmazunk.
Oszlopmegszorítás pedig egy olyan integritási megszorítás, amelyet egy tábla oszlopához rendelünk.

A táblamegszorítások listája:

• UNIQUE
• PRIMARY KEY
• FOREIGN KEY
• REFERENCES
• ON DELETE CASCADE
• CHECK
• ENABLE VALIDATE
• DISABLE

Az oszlopmegszorítások:

• NULL
• NOT NULL

A többi oszlopmegszorítások a táblamegszorításokkal azonosak.

A DISABLE opciót a integritási megszorítás vagy trigger kikapcsolására (nem törlésére) alkalmazhatjuk. Leggyakrabban az ALTER TABLE parancsban alkalmazzák.

  ALTER TABLE tabla DISABLE UNIQUE oszlop, oszlop,..
  ALTER TABLE tabla DISABLE PRIMARY KEY
  ALTER TABLE tabla DISABLE CONSTRAINT megszorítás_név
  DISABLE ALL TRIGGERS

Típusok konvertálása

  TO_CHAR(<number>)   vagy
  TO_CHAR(<number>, <format>);
  TO_CHAR(<date>)  vagy
  TO_CHAR(<date>, <format>);
  TO_DATE(<string>)  vagy
  TO_DATE(<string>, <format>);
  TO_NUMBER(<string>)

Naptári függvények

 ADD_MONTHS(<date>, n), a dátumhoz hozzáad n hónapót 
ROUND(<date>)
    ROUND(<date>, <format>)

Numerikus függvények

ABS(n)
COS(n)
COSH(n)
SIN(n)
SINH(n)
TAN(n)
TANH(n)
LN(n)
LOG(<base>, n)   <base> - a logaritmus alapja
MOD(m, n)  - m/n az osztás maradéka (mod(7,5)     
POWER(x,y)  hatványozás           (power(3,2)     
ROUND(n [,decimal_degits])
SIGN(n)	 (értékek: -1, ha n<0; 0, ha n=0; 1, ha n>0;)
CEIL(n)                (ceil(10.7)          
FLOOR(n)            (floor(10.7)         
SQRT(n)                (sqtr(25) 

Karakteres függvények

ASCII(charakter)
CHR(n)
INITCAP(string)     (Initcap(’szabó józsef’):  Szabó József)
INSTR(input_string, sub_string [,n, [,m]])   Az n-ik karaktertől kezdve keresi az m-ik sub_string 
             előfordulását az input_string-ben. Ha talált, visszaadja annak a helyét, ha nem talált, akkor az 
             eredmény 0 lesz.
LENGTH (string)	(Length(’Oracle’):     6)
LOWER (string)  	(Lower(’ABC’):  abc)
UPPER (string)
LPAD (string, n)
LPAD (string, n[,pad_chars])
RPAD (string, n)
RPAD (string, n[,pad_chars])
LTRIM (string)
LTRIM (string [,set_of_chars])
RTRIM (string)
RTRIM (string [,set_of_chars])
SUBSTR (string, start [,length])  (Substr(’abcdef’, 3, 2):  cd)
TRANSLATE (string, search_set, replace_set)    A string-ben a search_set karaktereket becseréli a megfelelő 
        replace_set karakterekre.      TRANSLATE (’eger’, ’e’ , ’a’)=’agar’

Univerzális függvények

NVL(kifejezés, replace_value) - ha a kifejezés nem üres, akkor annak az értékét adja vissza. Ha a kifejezés 
            NULL értékű, akkor  a replace_value értéket adja vissza.
DECODE(expression, val_1, trans_1, val_2, trans_2,... val_n, trans_n, default)
           A expression  adatokban (pl. a Tabla oszlopában egy SELECT parancsban) a val_k értékeket helyetesíti a 
           trans_k értékekkel. (k= 1,2,...,n). default – a többi értékeket nem változtatja meg.

Példa.

  SELECT DECODE(job, ’clerk’, ’Ügyintéző’, ’president’ ’Elnök’, job)	FROM emp;

Az Analist értéke nem változott meg, mivel az nem volt megadva a DECODE paraméterei között.

Fa-struktúrák alkalmazása a SELECT parancsban

Példa.

  SELECT emp_no, e_name, job, fonok_no FROM emp;

Feladat. Egy olyan eredmény-táblát akarunk szerkeszteni, mely tartalmazza minden dolgozó és főnöke nevét. Lehet látni, hogy az eredmény struktúrája fa-szerkezetű. A hagyományos megoldás:

      SELECT  f1.e_name, job, fonok_no FROM emp f1, emp f2
      WHERE f1.fonok_no=f2.emp_no;

Az ORACLE9i-ben fa-szerkezetű lekérdezéseket lehet létrehozni. Ebben az esetben a SELECT parancs a következő opciókat tartalmazhatja:

• CONNECT BY – egy táblán belül megadja az oszlopok közti kapcsolatot;
• START WITH –meghatározza azt a sort, amelytől kezdődik a fa építése.
  

    SELECT emp_no, e_name, job, fonok_no FROM emp  CONNECT BY fonok_no= PRIOR emp_no START WITH fonok_no IS NULL;

Ez a parancs a tábla sorait így rendezi át

A parancsokban a NULL értékeket csak az IS NULL vagy IS NOT NULL operátorokkal lehet alkalmazni:

  SELECT emp_no, e_name FROM  emp  WHERE comm IS NULL;           

Ezért a következő parancs hibát tartalmaz:

  SELECT emp_no, e_name FROM  emp  WHERE comm=NULL;           

Példa.

  SELECT e_name, sal+comm FROM emp;

ha a comm értéke NULL, akkor a sal+comm kifejezés értéke is NULL lesz, még ha adott is a sal értéke. A helyes megoldás a NVL függvény alkalmazása:

  SELECT e_name, sal+NVL(comm, 0) FROM emp;

és ha a comm értéke üres (NULL), akkor a NVL(comm, 0) függvény 0 értéket ad vissza. Lehet látni, hogy ilyen esetekben nélkülözhetetlen a NVL függvény.

Összekapcsolás – két vagy több tábla adatainak együttes feldolgozása egy SELECT parancsban. Külső összekapcsolás – olyan összekapcsolás, amikor az eredményben azok a sorok is jelennek meg, amelyeknek nincs megfelelője a másik táblában. A jelölése: (+).

     SELECT d.dept_no, d.d_name, e.e_name
           FROM  dept d,  emp e
           WHERE d.dept_no (+) = e.dept_no;           

A (+) művelet alkalmazása azt eredményezi, hogy az eredménytáblába kerül az összes dolgozó neve (e_name), és a részleg neve (d_name), de azoknak a részlegeknek a nevei is ki lesznek írva, melyekben jelenleg nem dolgozik senki. Azonos feladat esetén, egy SELECT parancs gyorsabban hajtódik végre, ha külső összekapcsolást tartalmaz, mint a NOT IN-t tartalmazó konstrukció.
Például:

    SELECT e_name, dept_no   FROM  emp 
      	WHERE dept_no NOT IN (SELECT dept_no FROM dept);           

vagy

    SELECT e_name, dept_no FROM emp e, dept d
          WHERE e.dept_no = e.dept_no(+) AND d.dept_no IS NULL;           

• UNION (egyesítés) – két lekérdezés eredményeinek unióját képezi. Az eredmény-táblába olyan sorok kerülnek, melyeket legalább az egyik tábla tartalmaz, de ismétlődő sorokat nem tartalmaz az eredmény-tábla;
• INTERSECT (metszet) – két lekérdezés eredményeinek metszetét képezi. Olyan sorokat kapunk, melyek mindkét táblában előfordulnak;
• MINUS (kivonás) – két lekérdezés eredményeinek különbségét képezi. Azokat a sorokat kapjuk, melyeket az első tábla tartalmazza, a másik – pedig nem.

  SELECT d_name  FROM dept  WHERE d_name LIKE ’%s%’           
   UNION
  SELECT d_name  FROM dept  WHERE d_name LIKE ’%o%’;           

   SELECT d_name  FROM dept  WHERE d_name LIKE ’%s%’           
     INTERSECT
   SELECT d_name  FROM dept   WHERE d_name LIKE ’%o%’;           

  SELECT d_name  FROM dept  WHERE d_name LIKE ’%s%’           
    MINUS
   SELECT d_name FROM dept  WHERE d_name LIKE ’%o%’;           

• INSERT – új sor beszúrása
• UPDATE – adatok módosítása a már létező sorokban
• DELETE – sorok törlése
• TRUNCATE – a tábla összes sorainak gyors törlése

Sorok beszúrása a táblába (INSERT parancs)

Ha az adatokat a sor összes mezőjébe akarjuk beszúrni, akkor a mező-listát nem szükséges megadni. A mezők neveit akkor kell megadni, amikor csak egyes mezőkbe akarunk adatokat beszúrni. A NULL (üres értékeket) az adatok közt lehet megadni, de ha az adat-lista nem tartalmaz egy mezőt, akkor is az NULL értéket fog kapni.

Példa.

  INSERT INTO testtab(col1, col2) VALUES(’szöveg’, 123);

Ugyan ezt az eredményt kapjuk, ha az

	INSERT INTO testtab VALUES (’szöveg’, 123);

parancsot alkalmazzuk.
A NULL-értékeket beszúrása:

	INSERT INTO testtab (col1, col2) VALUES (’szöveg’, NULL);

vagy

	INSERT INTO testtab (col1) VALUES (’szöveg’);

Arra is van lehetőség, hogy a táblába az INSERT parancs egyszerre több sort szúrjon be (e célból alkalmazzuk a beágyazott SELECT parancsot):

INSERT INTO testtab (col1, col2)
          SELECT e_name, emp_no FROM emp;

Tábla létrehozása egy másik tábla alapján

  CREATE TABLE emp_copy AS
SELECT * FROM emp;

  CREATE TABLE emp_copy2 AS
SELECT emp_no, e_name FROM emp
      WHERE e_name LIKE ’%a%’;

Adatok módosítása (UPDATE parancs)

Az UPDATE parancs módosítja az összes olyan sorokat, amelyekre teljesül a WHERE-feltétel. Ha az UPDATE parancs WHERE- feltételt nem tartalmaz, akkor a módosítás minden sorban végrehajtódik. Ez nagyon ritkán fordul elő.
Példa.

    UPDATE emp_copy2  SET e_name=’Kovács’ WHERE emp_no=7499;
	 
    UPDATE emp_copy2  ec  SET (emp_no, e_name) =
  (SELECT emp_no, e_name FROM emp e WHERE e.emp_no=ec.emp_no)
              WHERE e_name LIKE ’I%’;

Az utolsó parancsban ec és e a táblák másodlagos nevei. Az utolsó WHERE-feltétel az egész parancshoz tartozik (az UPDATE-hoz), és azokat a sorokat adja meg, amelyeket módosítani akarunk. A beágyazott SELECT parancs azokat az értékeket szerkeszti, melyek bekerülnek az emp_copy2 táblába. A SELECT-ben a WHERE-feltétel összekapcsolja a két tábla megfelelő sorait.

Sorok törlése (DELETE parancs)

  DELETE FROM emp_copy2 WHERE emp_no=7876;
	
  DELETE FROM emp_copy2 WHERE emp_no IN (SELECT emp_no FROM emp_copy);

Mindegyik SQL parancsban függvényeket lehet alkalmazni.
Példa.

 UPDATE emp_copy  SET e_name=UPPER( SUBSTR(e_name, 1, 1)) || LOWER(SUBSTR(e_name, 2, LENGTH(e_name)-1));

Ugyan ezt az eredményt kapjuk, ha az INITCAP függvényt alkalmazzuk.

Adatok gyors törlése
A tábla összes sorait gyorsan lehet törölni a TRANCATE paranccsal:

   TRANCATE TABLE emp_copy;

A parancs végrehajtása után a tábla struktúrája megmarad, de az egyetlen sort sem fog tartalmazni.

Tranzakció SQL parancsoknak olyan sorozata, amelyet az Oracle egyetlen egységként kezel. A tranzakció által okozott változásokat az AB-ban a COMMIT parancs véglegesíti.
Véglegesítés – az adatok változásainak átvezetése az AB-ba. A változások érvénytelenítését a ROLLBACK parancs hajtja végre. Ez alapján az AB-ban történt változtatások vagy ténylegesen végrehajtódnak, vagy valami okból – nem.
Az elosztott tranzakció olyan adatokkal hajtódik végre, amelyek az elosztott AB-ban, hálózaton, több csomópontban tárolódnak.
Kétfázisú véglegesítés – az Oracle az elosztott tranzakciók kezelését a kétfázisú véglegesítéssel végzi. Ez biztosítja, hogy egy tranzakció a hálózat minden csomópontjában vagy véglegesítődik, vagy visszagörgetésre kerül.

Bankautomata működésének folyamata, mint egy tranzakció példája.
Lekérdezzük a számlaegyenlegünket:

  SELECT account_balance FROM bank_table WHERE account_number=1111111
      AND account_type= ’SAVINGS’;

Az SQL-parancs az SGA-n keresztül a kiszolgáló-folyamathoz kerül, amely ellenőrzi, hogy a az SQL-terület tartalmazza-e parancs futtatható változatát. Ha a parancs nincs ott, akkor elemzi a parancsot, és azt a végrehajtható formában az SQL-területre helyezi és utána végrehajtja.
A kliens gyorsító-tárból leolvassa az egyenleget és közli azt a felhasználóval. Például, legyen az 250000 Ft.
Utána kérünk 20000 forintot. A kliens-folyamat fogadja a kérésünket, és az alapján szerkeszt egy SQL-parancsot:

    UPDATE bank_table SET account_balance=230000  WHERE WHERE account_number=1111111 AND Account_type= ’SAVINGS’;

Ezt a folyamatot bővebben lépésenként így lehet leírni:
1. A kliens-folyamat a parancsot az SGA-n keresztül eljuttatja a kiszolgáló-folyamathoz.
2. A kiszolgáló-folyamat ellenőrzi, hogy rendelkezésére áll-e a parancs végrehajtható formája.
Ha Igen, akkor a 4 lépésben, ha Nem – akkor a 3 lépésben folytatódik a feldolgozás.
3. Létrehozza a parancs végrehajtható formáját és az SQL-területre helyezi.
4. Végrehajtja a parancsot.
5. A manipulált adatok az adatgyorsítóban vannak? Ha Igen - 7, ha Nem – 6.
6. Az adatokat az Oracle beolvassa az adatállományból az adatgyorsítóba.
7. Oracle rögzíti az adat régi értékét a visszagörgető szegmensbe (25000).
8. Oracle másolatokat készít a tranzakcióról a változásnaplóban.
9. Az adatgyorsító-tárban lévő adatot megváltoztatja az új értékre (23000).
10. A bankautomata jelzi, hogy befejezte a tranzakciót.
11. Az az esemény, hogy befejeződött a tranzakció, rögzítésre kerül a változásnaplóban.
12. Az Oracle törli a visszagörgető szegmensben tárolt visszaállítási információt.
13. Az automata kiadja a pénzt.

Az első tranzakció akkor kezdődik, amikor a felhasználó belép a rendszerbe és elindít egy műveletek sorozatát az AB-ban. A következő tranzakció a COMMIT vagy a ROLLBACK paranccsal kezdődik.
A COMMIT parancs a tranzakció által végzett módosítások véglegesítésére használható.
A COMMIT explicit használata nélkül a program egészének végrehajtása egyetlen tranzakcióként tekintendő és nem kerül véglegesítésre a program befejeződéséig. Ennek az a hátránya, hogy ez idő alatt a többi felhasználó hozzáférését gátolja. A COMMIT parancsot ajánlatos használni minél gyakrabban.
A tranzakció végét a COMMIT vagy a ROLLBACK parancsok jelzik. A tranzakció alatt végrehajtott módosítások véglegesen a táblákban a csak a COMMIT parancs által történnek.
A ROLLBACK parancs esetén ezek a változtatások nem lesznek rögzítve a táblákban, ami azt jelenti, hogy a ROLLBACK parancs után a táblák tartalma a tranzakció kezdeti állapotában kerül.
Például, ha egy UPDATE parancs több mint egy sort módosít, és hiba csak az egyik sorban történt, akkor is a ROLLBACK parancs után a tábla egyetlen sora sem kerül módosításra.
A tranzakció végrehajtása alatt az Oracle zárolhatja a tábla sorait. Az Oracle csak azokat a sorokat zárolja, amelyekkel az INSERT, UPDATE, DELETE parancsok hajtják végre a műveleteket.
A zárolás automatikusan történik, és a felhasználónak nem kell azzal foglalkozni. Mivel a zárolás csak egyes sorokat érint (és nem az egész táblát), ez idő alatt a táblával a többi felhasználó párhuzamosan dolgozhat.
Az Oracle-ban lehetőség van a visszalépést a tranzakció bár-melyik pontjára végrehajtani. E célból a SAVEPOINT parancsot alkalmazhatjuk. Ha létrehoztunk egy SAVEPOINT pontot, akkor a visszalépést a ROLLBACK TO SAVEPOINT paranccsal lehet végrehajtani.

Példa.

  SAVEPOINT MyPoimt;
   Savepoint created.

  SELECT emp_no, e_name FROM emp;

5 rows selected.

   DELETE FROM emp WHERE e_name LIKE ’%a%’;
   2 rows deleted.

   SELECT emp_no, e_name FROM emp;

3 rows selected.

ROLLBACK TO SAVEPOINT MyPoint;
                      Rollback complete.

      SELECT emp_no, e_name FROM emp;

5 rows selected.

A Privilégium (jogosultság) a felhasználó számára lehetővé teszi, hogy az AB-ban bizonyos műveleteket hajthasson végre.
A privilégium kéttípusú lehet:

• rendszer-privilégium;
• objektum-privilégium.

A rendszer-privilégiumok lehetővé teszik adatdefiníciós és adatvezérlő parancsok végrehajtását, és az AB-ba való belépést. Az objektum-privilégiumok az AB objektumokkal való műveletekhez adnak jogosultságot. A felhasználó a létrehozása után egyetlen privilégiummal sem rendelkezik, és a privilégiumokat az AB adminisztrátortól később kapja. Amikor a felhasználó kap egy privilégiumot, utána az engedélyezett műveteket végrehajtatja a felsorolt AB-objektumaival.
Egy AB-hoz általános esetben több felhasználó is kapcsolódhat. Az Oracle-ban minden objektumnak van felhasználó-tulajdonosa. A nem tulajdonos-felhasználó csak akkor végezhet bizonyos műveleteket az objektumokkal, ha megkapta a megfelelő privilégiumokat. Létezik több mint nyolcvan rendszer-privilégium:

• ALTER
• DELETE
• EXECUTE
• INDEX
• INSERT
• REFERENCES
• SELECT
• UPDATE

A következő táblázat tartalmazza egyes privilégiumok és AB-objektumok közti lehetséges kapcsolatokat.

A INSERT, UPDATE és REFERENCES privilégiumokat lehet a tábla egyes oszlopaihoz kötni. Ha megakarjuk tudni, hogy milyen rendszer-privilégiumokat lehet alkalmazni, akkor végre kell hajtani a következő parancsot:

SELECT UNIQUE privilege FROM dba_sys_priv;

Privilégiumok megadása
Az AB adminisztrátor a privilégiumokat a GRANT paranccsal adhatja meg a felhasználóknak:

   GRANT  privilégium_lista ON objektum_lista TO felhasználó_lista 
      [WITH GRANT OPTION];

   GRANT  privilégium_lista ON objektum_lista TO szerepkör_lista 
      [WITH GRANT OPTION];

   GRANT privilégium_lista ON objektum_lista TO PUBLIC 
      [WITH GRANT OPTION];

A WITH GRANT OPTION esetén a felhasználó az adott privilégiumot más felhasználónak is tovább adhatja.

Példa.

GRANT SELECT, UPDATE ON emp_copy TO test_user;

A test_user felhasználó az emp_copy táblával végrehajthatja a SELECT és UPDATE parancsokat. Ezekben a parancsokban nem elég a tábla nevét emp_copy megadni, mivel a tábla tulajdonosát is meg kell adni. Például, ha a scott felhasználó az emp_copy tábla tulajdonosa, akkor a helyes hivatkozás:

    SELECT * FROM scott.emp_copy;

Bizonyos esetekben ez a követelmény problémát is okozhat. Például, ha a tábla tulajdonosa a program létrehozása és futtatási ideje között megváltozott, akkor ezt figyelembe kell venni az összes táblára való hivatkozásban. Ilyen esetekben célszerű alkalmazni a tábla szinonimáját:

   CREATE SYNONIM test FOR  scott.emp_copy;

A scott.emp_copy tábla megkapta a test szinonimát. Ha ezek után megváltozik a tábla tulajdonosa, akkor továbbra is a táblára lehet hivatkozni a szinonima segítségével

   SELECT * FROM test;

A PUBLIC opció esetén az adott privilégiumokat az összes felhasználóra érvényes, ami azt jelenti, hogy az objektum nyilvános. A Nyilvános objektum elérhető, látható az összes felhasználó számára.

GRANT SELECT, UPDATE ON emp_copy TO PUBLIC;

Ha a privilégium tartalmazza az ANY opciót, akkor az az AB összes táblájára érvényes. A következő privilégiumok esetén:

  DELETE  ANY TABLE
  UPDATE ANY TABLE
  INSERT   ANY TABLE,

a felhasználó az AB összes tábláit módosíthatja, még akkor is, ha nem tulajdonosa a táblának. Ezt a legnagyobb lehetséges jogosultság, és csak az adminisztrátor szintű felhasználó kaphatja azt.
A SESSION_PRIVS nézetben megtalálhatók az aktuális privilégiumok. Az ALL_TAB_PRIVS és ALL_COL_PRIVS nézetekből pedig megtudhatjuk, hogy milyen privilégiumokkal rendelkeznek a felhasználók.

Szerepkörök (ROLE)
Szerepkör – a privilégiumok együttese, és lehetőség a felhasználók csoportosítására. A felhasználókat egy csoportba lehet beosztani, és a csoporton belül mindegyik felhasználó azonos jogosultsággal rendelkezik. Például, létrehozunk egy szerepkört

  CREATE ROLE kozos;

utána a szerepkörhöz privilégiumokat kapcsolunk

  GRANT INSERT ON table_a TO kozos; 
  GRANT INSERT ON table_b TO kozos;
  GRANT INSERT, DELETE  ON table_c  TO kozos;
  GRANT UPDATE ON table_d TO kozos;
  GRANT DELETE ON  table_e TO kozos;
  GRANT SELECT  ON table_f TO kozos;

Ha a felh_1 és felh_2 felhasználók megkapják a kozos szerepkört

  GRANT kozos  TO x;
  GRANT kozos  TO y;

akkor rendelkezni fognak az összes közös szerepkörhöz tartozó jogosultságokkal.

Beépített szerepkörök
Az Oracle-ban léteznek beépített szerepkörök:
CONNECT-

• ALTER SESSION
• CREATE CLUSTER
• CREATE DATABASE LINK
• CREATE SEQUENCE
• CREATE SESSION
• CREATE SYNONIM
• CREATE TABLE
• CREATE VIEW

RESOURSE-

• CREATE CLUSTER
• CREATE PROCEDURE
• CREATE SEQUENCE
• CREATE TABLE

Jogosultság a programok végrehajtására
Az AB-ban tárolódhathak:

• Eljárások (PROCEDURE)
• Csomagok (PACKAGE)
• Függvények (FUNCTION).

Ezek az objektumok csak akkor érhetők el, ha a felhasználó EXECUTE privilégiummal rendelkezik.

Példa.

  GRANT EXECUTE  ON my_package TO PUBLIC;
  GRANT EXECUTE  ON my_func TO felh_2;
  GRANT EXECUTE  ON my_proc TO felh_1;

Privilégiumok visszavonása
Az AB adminisztrátor a privilégiumot a felhasználótól a REVOKE parancs által visszavonhat:

  REVOKE privilégium ON objektum FROM felhasználó 
   [CASCADE CONSTRAINTS];

A CASCADE CONSTRAINTS a REFERENCES privilégium megvonása esetén törli az összes hivatkozási integritási megszorítást, amelyet a felhasználó hozott létre (CASCADE – lépcsőzetes).

REVOKE UPDATE ON emp_copy FROM test_user;

Ez a parancs után a test_user felhasználó nem módosíthatja a emp_copy táblát, de a SELECT parancsot továbbra is alkalmazhatja.

   REVOKE  SELECT ON classes FROM user_1;
   REVOKE ALTER TABLE, EXECUTE ANY PROCEDURE FROM user_2;
   REVOKE  kozos FROM felh_1;

Indexeket azzal a céllal hozzák létre, hogy a tábla bizonyos oszlopainak értékei ne ismétlődjenek, vagy (és) hogy gyorsabban történjen az adatok keresése. Az index létrehozása után azok a parancsok, melyek módosítják a táblát (UPDATE, DELETE, INSERT), az indexek szerinti sorrendjét továbbra is aktuális állapotban tartják. Ez időt igényel, és ezért minél több indexe van egy táblának, annál lassabban hajtódnak végre az UPDATE, DELETE, INSERT parancsok. A SELECT parancs a WHERE része alapján határozza meg a szükséges és felhasználandó indexeket, és ez biztosítja a parancs leggyorsabb végrehajtását.
Az indexeket létre lehet hozni:

• direkt módon, a CREATE INDEX paranccsal,
• automatikusan, a CREATE TABLE parancsban,
• ALTER TABLE paranccsal.

Használhatók:

• egyedi (nem ismétlődő) indexek, az értékei különbözőek, amikor a CREATE TABLE parancs a PRIMARY KEY vagy UNIQUE megszorításokat tartalmazza;
• ismétlődő értékeket tartalmazó indexek.

Példa.

   CREATE INDEX Test_ind ON Test(col1)
     TABLESPACE users
     STORAGE (INITIAL 20K
         NEXT 20K
         PCTINCREASE 75);

Példa.

   CREATE TABLE sample_3
     (col_a    VARCHAR(30),
     col_b    VARCHAR(30),
     col_c    VARCHAR(30));
   CREATE INDEX col_c_ind ON sample_3(col_c) ; 
    CREATE UNIQUE INDEX col_a_ind ON sample_3(col_a) ; 

A cola_ind index nem tartalmazhat ismétlődő értékeket, a colc_ind index pedig tartalmazhat.
A táblák indexeit a user_indexes nézetből lehet lekérdezni.

Példa.

SELECT index_name FROM user_indexes
	WHERE table_name=’SAMLE_3’;

    ALTER TABLE semple_3 DESABLE CONSTRAINT COL_A_IND;

     SELECT index_name FROM user_indexes
	WHERE table_name=’SAMPLE_3’;

Most a semple_3 táblához csak egy COL_B_IND index tartozik. A következő parancs visszaállítja (ENABLE) a COL_A_IND indexet:

     ALTER TABLE semple_3 ENABLE CONSTRAINT COL_A_IND;

A CREATE, ALTER parancsokban meg lehet adni, hogy az Oracle az indexek tárolására menyi memóriát adjon: ALTER TABLE emp ADD CONSTRAINT pk_emp PRIMARY KEY (emp_no)

USING INDEX
	  INITRANS 5
	  MAXTRANS 10
	  TABLESPACE user_date
	  STORAGE ( INITIAL 20K
			NEXT 20K
			MINEXTENTS 1
			MAXEXTENTS 99
			PCTINCREASE 0)
		PCTFREE 10; 

Ha egy táblában a FOREIGN KEY vagy REFERENCES megszorításokkal külső kulcsokat hozunk létre, akkor azokat is kell indexelni, hogy a táblák közti kapcsolatot létrehozása után gyorsabban lehessen végrehajtani az adatok keresését: ALTER TABLE emp ADD CONSTRAINT fk_demt FOREIGN KEY (dept_no)

REFERENCES dept(dept_no);
      CREATE INDEX fk_dept_ind ON emp(dept_no) ; 

A fk_dept_ind index ismétlődő értékeket tartalmazhat. Ezek után a következő parancs gyorsabban hajtódik végre:

            SELECT e.e_name, d.name FROM emp e, dept d
	 WHERE e.dept_no=d.dept_no;

Nagy táblák esetén az indexek megsemmisítése és visszaállítása sok időt igényelhet. Az indexet ideiglenesen is kikapcsolhatjuk (KEEP opcióval), úgy hogy az index továbbra is létezzen. Például,

        ALTER TABLE emp KEEP INDEX  pk_emp;

A DROP INDEX parancs az indexet véglegesen megsemmisíti, ha az nem PRIMARY KEY vagy UNIQUE index.

	DROP INDEX Test_ind;

Sok esetben a programok indexelés nélkül is alkalmazhatók, de a végrehajtásuk lassabb lesz (legalább is elméletileg). Ne felejtsük, hogy gyakorlati szempontból mindegyik táblában kell, hogy legyen legalább egy PRIMARY KEY vagy UNIQUE index. Az indexeket az indexszegmens tartalmazza.

A UNIQUE és PRIMARY KEY indexeket az Oracle automatikusan hoz létre, amikor a CREATE TABLE parancsot végrehajtja

   CREATE TABLE test (test_no NUMBER(5), …,
     PRIMARY KEY(test_no), …);

A már létező táblában is létrehozhatunk elsődleges kulcsot:

    ALTER TABLE test ADD
        PRIMARY KEY(test_no);

Figyelem! Ha a tábla már tartalmaz sorokat, és azoknak az adatai a létrehozandó indexel konfliktusban vannak, akkor az ALTER parancs hibát jelez.
Az indexeket lehet törölni, vagy kikapcsolni törlés nélkül. Ezeket a műveleteket nem lehet végrehajtani, ha egy másik tábla idegen kulcsa hivatkozik az adott kulcsra.
Az ENABLE opció által egy indexet bekapcsolhatunk, az index kikapcsolására a DISABLE opció szükséges. Az alapértelmezés alapján (ha nincs megadva sem az ENABLE sem a DISABLE opció) az Oracle az ENABLE opciót fogja értelmezni.

     ALTER TABLE test DISABLE PRIMARY KEY;

Ha ki akarjuk kapcsolni egy kapcsolatban levő idegen kulcsot, akkor ezt így lehet megvalósítani

    ALTER TABLE test DISABLE PRIMARY KEY CASCADE;

Az index törlését hasonló képen lehet végrehajtani:

    ALTER TABLE test DROP PRIMARY KEY;

Ha az index az ENABLE állapotban van, akkor a táblák közti integritási kapcsolatok minden módosítási művelet után is érvényesek maradnak. A DISABLE állapotban az integritási kapcsolatok megsérülhetnek. Ha az integritási kapcsolat egyszer megsérül, akkor az Oracle az indexet az ENABLE állapotába már nem képes visszaállítani.

Ha egy Oracle alkalmazásban szükség van egy numerikus adatsorozatra (például, elsődleges kulcsra), melynek az értékei egyediek legyenek, akkor szekvenciákat lehet alkalmazni.
Szekvencia – egy olyan Oracle objektum, amely képes számokat generálni, és elérhetővé téve őket a sorszámozott elsődleges kulcsok számára.
Szekvenciák létrehozása:

      CREATE SEQUENCE sor  START WITH n

A START WITH n – megadja szekvencia kezdő értékét. A szekvencia aktuális értékét a

sor.CURRVAL 

attribútum adja vissza. A szekvencia következő értékét (elemét) a

      sor. NEXTVAL 

attribútummal lehet megkapni.

Példa. Ha a student tábla id mezője elsődleges kulcs, akkor annak az értékeit a student_sequence szekvencia által kapjuk meg:

    CREATE SEQUENCE student_sequence  START WITH 10000;
    INSERT INTO students (id, first_name, last_name, major,  current_credits)
           VALUES (student_sequence.NEXTVAL, 'Scott', 'Smith', 'Computer Science', 0);
    INSERT INTO students (id, first_name, last_name, major, current_credits)
           VALUES (student_sequence.NEXTVAL, 'Margaret', 'Mason', 'History', 0);
   SELECT id FROM students;

A szekvenciát általánosabb formában is létre lehet hozni:

  CREATE SEQUENCE test_seq  
    INCREMENT BY 4
    START WITH 50
    MAXVALUE   70
  MINVALUE    50
  CYCLE
  CACHE 3; 

A test_seq szekvencia a következő sorozatot fogja generálni 50, 54, 58, 62, 66, 70, 50, 54,…

Az INCREMENT BY 4 az jelenti, hogy a szekvencia értékei néggyel fognak növekedni, hanem néggyel, a MAXVALUE értékének elérése után a CYRCLE újból visszaállítja a MINVALUE értéket. A CACHE 3 egyszerre három generált számot fog elhelyezni a SGA-kesbe. Figyelembe kell venni, hogy a ROLLBACK parancs a szekvencia kezdő állapotát nem állítja vissza, mely a tranzakció elején volt aktuális. A szekvencia módosítása:

    ALTER SEQUENCE test_seq  
    INCREMENT BY 10
    MAXVALUE   1000
	MINVALUE    50
	NOCYCLE
	CACHE 20;

és törlése:

  DROP SEQUENCE test_seq;  

Klaszter – olyan táblák adatainak együttes tárolása, amelyek közös adatokat tartalmaznak, és egy időben kerülnek feldolgozásra.
Klaszterkulcs – a klaszterizált táblák közös oszlopai közül azok az oszlopok, amelyek a tárolás vagy elérés kulcsául szolgálnak.
Ha a lekérdezések gyakran hivatkoznak két táblából álló összekapcsolt tábla-együttesre, akkor az AB adminisztrátor úgy dönthet, hogy ezeket a táblákat közösen egy klaszterben tárolja. A klasztert a CREATE CLASTER paranccsal lehet létrehozni.
Példa.

   CREATE CLASTER Diak_Tanar (tantargy NUMBER(3))
      TABLESPACE users
      STORAGE( INITIAL 200K
	NEXT 300K
	MINEXTENTS 2
	MAXEXTENTS 20);

A tantargy oszlop a klaszter kulcsa, és általa valósul meg a két tábla közti kapcsolat. A már létező klaszterben táblákat lehet létrehozni:

    CREATE TABLE Diak(tantargy NUMBER(3) PRIMARY KEY, …)
       CLUSTER Diak_Tanar (tantargy);

   CREATE TABLE Tanar(nev VARCHAR2(15) NOT NULL, …
        tantargy NUMBER(3) REFERENCES Diak)
        CLUSTER Diak_Tanar (tantargy);

A klaszter indexét még akkor kell létrehozni, amikor a táblák üresek, egyetlen sort sem tartalmazhat.

CREATE INDEX Diak_Tanar_Index
ON CLUSTER Diak_Tanar
TABLESPACE users
              STORAGE( INITIAL 50K
	NEXT 50K
	MINEXTENTS 2
	MAXEXTENTS 20);

A klasztert és az indexét különböző táblaterületben lehet tárolni. Ha töröljük a klaszter indexét, akkor a klaszter tábláit már nem lehet használni. A klaszter módosítása az ALTER CLUSTER paranccsal történik:

ALTER CLUSTER Diak_Tanar
PRTFREE 30;

A nézet tartalmazza egy SQL-lekérdezés eredményét. A programokban ugyanúgy alkalmazhatjuk a nézeteket, mint a táblákat. Mivel a nézetek az AB-ban nem tárolódnak, úgy is lehet rájuk tekinteni, mint virtuális táblákra. Egy nézetet úgy is értelmezhetjük, mint egy tárolt SELECT parancs eredménytábláját, mely a nézet mindegyik megnyitásakor újból létrejön.
A nézet létrehozása:

     CREATE VIEW v_customer_sales_rgn AS 
         SELECT surname, sales_region  FROM customer;

A nézeteket a parancsokban úgy alkalmazhatjuk, mint a táblákat:

SELECT * FROM v_customer_sales_rgn;

A nézetet akkor nevezzünk egyszerűnek, ha az nem tartalmaz:

• agregáló függvényt;
• felhasználó által létrehozott függvényt;
• beágyazott lekérdezést;
• lekérdezések unióját.

A nézet nem tartalmazhat:

• ORDER BY
• FOR UPDATE

záradékokat.

Az egyszerű nézet INSERT, DELETE, UPDATE parancsokkal módosítható. A módosítások azokban az táblákban rögzítődnek, amelyek alapján a nézetet szerkesztették.
A nézeteket kényelmes alkalmazni az összetett lekérdezések helyet:

       CREATE OR REPLACE VIEW emp_dept_view AS
	SELECT e.emp_no, e.e_name, d.dept_no, d.d_name
		FROM emp e, dept d
		WHERE e.dept_no=d.dept_no;

és

 SELECT * FROM emp_dept_view;

Előfordulhat, hogy a módosítások után a táblában olyan változások történnek, hogy a tábla egyes sorai már nem kerülnek be a következő VIEW-lekérdezésbe, mivel nem felelnek meg a WHERE-feltételnek.
Ilyen módosításokat meg lehet akadályozni, ha a nézet létrehozásának parancsa a WITH CHECK OPTION megszorítást tartalmazza.
A nézetet az ALTER paranccsal nem lehet módosítani. A módosítást csak úgy lehet végrehajtani, hogy először töröljük a nézetet

       DROP VIEW test;

és utána újból létrehozzuk a nézetet, de már a módosított változatában.
A nézeteket biztonsági okokból is szokták alkalmazni. Például, ha egy felhasználónak egy tábla csak bizonyos oszlopait vagy sorait engedélyezzük elérni, akkor egy olyan nézetet hozunk létre, mely csak a megengedett adatokat tartalmazza, a felhasználó, pedig megkapja a megfelelő privilégiumokat erre a nézetre.

Szinonima – egy olyan név, melyet táblához vagy nézethez lehet hozzárendelni, és az eredeti név helyett használni.
A szinonima létrehozása után a parancsokban az objektum eredeti neve helyett a szinonimával lehet rá hivatkozni. Léteznek személyes és nyilvános (PUBLIC) szinonimák. A személyes szinonimákat egy-egy felhasználó hozza létre, és a továbbiakban csak ő használhatja.

      CONNECT test/test
    CREATE SYNONIM emp FOR scott.emp

A Test nevű felhasználó az emp táblára, melynek a tulajdonosa scott, létrehozott emp nevű szinonimát. A nyilvános szinonimákat csak az AB adminisztrátor hozhat létre, és az ilyen szinonimát mindegyik olyan felhasználó alkalmazhatja, aki megfelelő privilégiummal rendelkezik

CREATE PUBLIC SYNONIM emp FOR scott.emp

A szinonima törlése: DROP SYNONIM emp;

A távoli AB objektumára is lehet szinonimát létrehozni

CREATE PUBLIC SYNONIM synon FOR felh.aru@AB_1

A synon nevű nyilvános szinonimát hoztunk létre a távoli AB_1 AB-ban a felh.aru táblára.