La feuille recto-verso

1. CM1 — SPARC · niveau externe · encre bleue

• Architecture SPARC : 3 niveaux — externe (vues utilisateur) · conceptuel (modèle global) · physique (stockage)
• Le niveau externe Oracle utilise : utilisateurs · rôles · vues
• Créer un user : CREATE USER nom IDENTIFIED BY pwd DEFAULT TABLESPACE users;
• À sa création, un user n'a aucun rôle ni privilège — pas même CREATE SESSION
• Rôles d'administration créés à la BD : SYSTEM, SYS
• Donner / retirer : GRANT priv|role TO user|role; · REVOKE priv|role FROM user|role;
• Créer un rôle : CREATE ROLE nomRole [NOT IDENTIFIED | IDENTIFIED {BY pwd}]
• Activer un rôle protégé : SET ROLE nomRole IDENTIFIED BY pwd;
• Rôle d'application : associé à un package — activé automatiquement à l'exécution d'un élément du package. Unique façon de l'activer.
• Rôle utilisateur : tous les autres rôles. Peuvent être protégés par mot de passe.
• Rôles prédéfinis Oracle : CONNECT, RESOURCE, DBA, EXP_FULL_DATABASE, IMP_FULL_DATABASE
• Privilèges Système : combinent ALTER, CREATE, DROP sur des objets (TABLE, INDEX, PROCEDURE, ROLE, SEQUENCE, TRIGGER, VIEW, SYNONYM, DATABASE LINK, …)
• Privilèges Objet : ALTER, DELETE, EXECUTE, INDEX, INSERT, REFERENCES, SELECT, UPDATE
• Privilège objet pour proc/fct/package : EXECUTE
• Privilège SELECT s'applique à : table · vue · séquence · vue matérialisée
• Vue logique : requête SQL ré-exécutée à chaque appel. Aucun stockage physique.
• Vue matérialisée (VM) : requête dont le contenu est dupliqué à la création et rafraîchi régulièrement. Stockage physique présent.
• Privilèges pour créer une VM : Système CREATE MATERIALIZED + Objet GRANT SELECT ON sur les tables sources
• Création basique : CREATE MATERIALIZED VIEW vm AS SELECT * FROM Client;
• Création différée : ajouter BUILD DEFERRED
• Conseil : la VM doit contenir la clé primaire des tables sources
• 3 modes de rafraîchissement :
  • REFRESH ON DEMAND — défaut · DBMS_MVIEW.REFRESH('vm')
  • REFRESH ON COMMIT — à chaque commit sur les sources
  • REFRESH START WITH date NEXT temps_jour — à intervalle
• 3 méthodes de rafraîchissement :
  • FAST — incrémental, nécessite un MATERIALIZED VIEW LOG sur les sources
  • COMPLETE — Oracle réexécute toute la requête
  • FORCE — défaut · essaie FAST, sinon COMPLETE
• BD bien construite = normalisée. VM = dénormalisation pour la performance.

2. CM2 — Tablespaces · indexes · clusters · encre verte

• Niveau physique : Oracle redéfinit le système de gestion de fichiers sur l'OS
• Hiérarchie de stockage : Base → Tablespace → Fichier → Segment → Extension → Bloc logique → bloc physique
• 5 segments :
  • Données — tables utilisateurs/système et clusters · 1 segment par table, créé auto
  • Index — données d'index · peuvent vivre dans un tablespace distinct
  • Temporaire — pour les requêtes nécessitant du disque temporaire · défini sur l'user (sinon SYSTEM)
  • Amorçage — dans tablespace SYSTEM · contient le dictionnaire de données
  • Rollback (annulation) — données avant modification pour annuler une transaction
• 3 types de fichiers Oracle :
  • .dbf — fichiers de données (tables, vues, procédures stockées…)
  • .rdo / .log — Redo Log (historique des modifications)
  • .ctl — fichiers de contrôle (état de la base)
• Minimum requis : 1 fichier de données + 2 Redo Log + 1 contrôle, référencés dans init.ora
• Définir tablespace temporaire : CREATE USER … TEMPORARY TABLESPACE …
• Indexes : recherche plus rapide via WHERE sur la colonne · MAJ de la table plus longue (l'index doit suivre)
• Sélectivité = nb valeurs distinctes / nb lignes
  • Forte (proche de 1) : clé primaire
  • Faible : colonnes comme « sexe », « statut »
• B-tree (défaut) : arbre balancé · forte sélectivité · ne gère pas NULL
• Bitmap : bitmap par valeur de l'index · faible sélectivité · beaucoup de lignes · très peu de MAJ · gère NULL · MAJ d'une ligne ⇒ reconstruction totale
• Création : CREATE [UNIQUE] INDEX nom ON tbl(col [ASC/DESC]) TABLESPACE ts;
• Index inversé : CREATE INDEX nom ON tbl(col) REVERSE · annulation : ALTER INDEX nom REBUILD NOREVERSE
• Index par fonction : CREATE INDEX nom ON tbl(f(col)) · la fonction doit être DETERMINISTIC · SYSDATE n'est pas déterministe
• Index bitmap : CREATE BITMAP INDEX nom ON tbl(col) — Enterprise Edition uniquement
• Cluster : optimise les jointures Maître/Détails sur colonnes communes — remplace le tablespace
• Les lignes Maître+Détails de même clé sont stockées côte à côte sur le disque. Colonnes communes non dupliquées.
• Création : CREATE CLUSTER Nom(col1 type1) SIZE n TABLESPACE ts; puis CREATE TABLE … CLUSTER nomCluster(colT)
• Index obligatoire : CREATE INDEX nom ON CLUSTER nomCluster; — sans index, aucune opération possible
• Mise en cluster d'une table existante (4 étapes) : ① nouvelle table CLUSTER · ② copier le contenu · ③ drop ancienne · ④ renommer

3. CM3 — Transactions · encre bordeaux

• Transaction = ensemble ordonné d'opérations LMD qu'un SGBD effectuera parfaitement et complètement, ou pas du tout
• Opérations LMD : INSERT, UPDATE, DELETE
• ACID : Atomicité (tout ou rien) · Cohérence · Isolation · Durabilité — natif tous SGBD-R
• SGBD = logiciel · Instance = ressources mémoires/logicielles · Session = connexion d'un user à une instance
• Une transaction commence : au début de session OU à la fin de la précédente
• Une transaction se termine par COMMIT (validation) ou ROLLBACK (annulation)
• Cohérence garantie même en cas de panne serveur : reprise au démarrage
• Isolation lors d'un SELECT :
  • Voit : données validées avant · MAJ de la transaction courante · destructions en cours d'autres transactions
  • Ne voit pas : destructions définitives validées · destructions courantes · créations/MAJ en cours d'autres transactions
• Lecture seule consistante : SET TRANSACTION READ ONLY; — instantané au moment où la transaction commence
• Découpage : SAVEPOINT nom; + ROLLBACK TO SAVEPOINT nom;
• Un jalon disparaît à la fin de la transaction (commit ou rollback total)
• Verrouillage : posé à chaque MAJ d'une ligne · libéré à la fin de la transaction qui l'a posé
• Toute MAJ sur ligne verrouillée → blocage temporaire de la transaction qui tente
• Interblocage (deadlock) : T1 attend L2 (détenue par T2), T2 attend L1 (détenue par T1) — étreinte fatale
• Oracle résout en annulant une opération d'une transaction (la plus jeune), débloquant l'autre
• OLTP = Online Transaction Processing : architecture transactions temps réel (SNCF, bourse, e-commerce) — natif Oracle
• Synonyme : CREATE PUBLIC SYNONYM nom FOR objet_complet;
• Schéma : associé à un utilisateur · même nom que lui · CREATE SCHEMA AUTHORIZATION nom_schema …

4. CM4 — Bases de données réparties · encre prune

• BDDR = ensemble de données stockées sur plusieurs sites connectés par un réseau
• Site = machine avec BDD locale · Réseau = communication entre sites · SGBD Réparti nécessaire
• Critères : coût installation, communication, sûreté, disponibilité
• Méthodes de conception : top-down (global → local + fragmentation) OU bottom-up (intégration de locaux en global)
• Intérêts : fiabilité (pannes), performances, croissance · Inconvénients : coûts, synchro, sécurité, interblocage distribué
• 3 fragmentations :
  • Horizontale = σ sélection · recomposer par UNION
  • Verticale = π projection · recomposer par JOINTURE · la clé doit être dans chaque fragment
  • Mixte = projection puis sélection
• Fragmentation horizontale dérivée : fragments définis par (semi) jointure avec une autre table déjà fragmentée H
• Sur le site i (V) : π(R; Att_i,1, …, Att_i,k_i) — chaque ensemble contient une clé · réunion = tous les attributs
• Couche Oracle Net : Listener.ora (connexions distantes) · tnsnames.ora (alias) · Net Configuration Assistant · programme listener
• Database link : CREATE [PUBLIC] DATABASE LINK nom CONNECT TO user IDENTIFIED BY pwd USING 'nomService';
• Accès distant : SELECT * FROM table@dblink;
• Opérations : ALTER SESSION CLOSE DATABASE LINK lien · DROP DATABASE LINK lien
• Recomposition vue H : CREATE VIEW V AS SELECT … FROM T1@s1 UNION SELECT … FROM T2@s2;
• Recomposition vue V : CREATE VIEW V AS SELECT T1.cle, T1.a, T2.b FROM T1@s1, T2@s2 WHERE T1.cle = T2.cle;
• Réplication : relation stockée sur un site, recopiée sur un ou plusieurs sites
  • Avantages : disponibilité, parallélisme lecture, coût transmissions
  • Inconvénients : cohérence des copies, propagation des MAJ
• Oracle réplication : CREATE MATERIALIZED VIEW image REFRESH FAST AS SELECT … FROM T@lien;
• Validation à 2 phases (2PC) : coordinateur C + sites S_i · règle d'unanimité
  • Phase 1 préparation : C écrit <T préparée> · envoie « préparation » à tous · chaque S_i vote « T prête » ou « abandon T »
  • Phase 2 décision : si tous OK → C écrit <T validée> + envoie « valider T » · sinon un seul « abandon » → C écrit <T abandonnée> + envoie « abandon T »
  • Chaque S_i inscrit le message et envoie un accusé · puis C écrit <T finie>
• Reprise après panne :
  • Panne coordinateur en phase préparatoire (avant diffusion) → abandonner
  • Panne coordinateur après décision → ne rien faire
  • Panne exécutant en phase préparatoire → abandonner
  • Panne exécutant en état de décision connue → ne rien faire

5. CM5 — PL/SQL · triggers & séquences · encre sienne

• PL/SQL = Procedural Language for SQL — code stocké dans la base · partagé par tous les applicatifs · garde-fous au niveau base
• Stockage : triggers · fonctions/procédures · packages
• Bloc PL/SQL :

  DECLARE
     variables, types, curseurs
  BEGIN
     instructions SQL/PL
  EXCEPTION
     WHEN err THEN …
  END;

• Sortie debug : DBMS_OUTPUT.PUT_LINE('msg' || TO_CHAR(n)) + SET SERVEROUTPUT ON côté session
• Variables : scalaires (nombres, chaînes, date) · composées (RECORD, TABLE, VARRAY) · curseurs
• %TYPE = même type qu'une colonne · %ROWTYPE = enregistrement complet d'une ligne
• 3 formes d'affectation : := classique · SELECT … INTO · FETCH cur INTO
• RECORD : TYPE t IS RECORD (a t1, b t2, …); v t;
• TABLE associative : TYPE t IS TABLE OF type INDEX BY PLS_INTEGER; · index sparse
• VARRAY : TYPE v IS VARRAY(n) OF type; · indices contigus 1..COUNT
• Conditionnelle : IF cond THEN … ELSIF cond THEN … ELSE … END IF;
• 3 boucles :
  • LOOP … EXIT WHEN cond; END LOOP;
  • FOR i IN deb..fin LOOP … END LOOP;
  • WHILE cond LOOP … END LOOP;
• Parcours TABLE assoc : i := t.FIRST; WHILE i IS NOT NULL LOOP … i := t.NEXT(i); END LOOP;
• Parcours VARRAY : FOR i IN 1..v.COUNT LOOP … END LOOP;
• Trigger : code PL/SQL stocké qui se déclenche lors d'un événement sur TABLE, VUE ou SCHÉMA/DATABASE
• Événements : LDD (CREATE, ALTER, DROP) · LMD (INSERT, UPDATE, DELETE) · DATABASE (STARTUP, SHUTDOWN, LOGON)
• Moment : BEFORE (modifier :new, bloquer) · AFTER (propager, logger)
• Syntaxe : CREATE [OR REPLACE] TRIGGER nom BEFORE|AFTER INSERT|DELETE|UPDATE [OF cols] ON tbl [FOR EACH ROW] [WHEN cond] DECLARE … BEGIN … END;
• :new (nouvelle valeur) et :old (ancienne) → uniquement avec FOR EACH ROW
• Pas de requête sur la table en cours de mutation pendant l'exécution du trigger
• Plusieurs événements : BEFORE INSERT OR DELETE ON tbl + IF INSERTING THEN … ELSIF DELETING THEN … END IF;
• Sur colonnes : BEFORE UPDATE OF col1, col2 ON tbl
• Gestion : ALTER TRIGGER nom ENABLE | DISABLE | COMPILE · DROP TRIGGER nom
• Garde-fou : RAISE_APPLICATION_ERROR(-20012, 'message'); — code strictement < -20000, plage [-20999, -20000]
• Vue non modifiable (jointure, GROUP BY, sous-requête) → trigger INSTEAD OF INSERT|UPDATE|DELETE ON vue
• Pattern INSTEAD OF : insérer dans table maître si absent (WHEN NO_DATA_FOUND THEN INSERT … RETURNING …), puis insérer dans table détail
• Séquence : génère automatiquement des entiers (clés primaires typiquement)

  CREATE SEQUENCE seq
  [INCREMENT BY n] [START WITH m]
  [MINVALUE k] [MAXVALUE p]
  [CYCLE | NOCYCLE] [CACHE l | NOCACHE];

• Accesseurs : seq.NEXTVAL (génère + incrémente) · seq.CURRVAL (lit la valeur en cours)
• Auto-incrément (pattern) : trigger BEFORE INSERT FOR EACH ROW + :new.id := seq.NEXTVAL;

6. CM6 — PL/SQL · procédures, curseurs, exceptions · encre charbon

• Procédure : CREATE [OR REPLACE] PROCEDURE nom(p IN type, p OUT type, p IN OUT type) IS … BEGIN … END;
• Fonction : ajoute RETURN type avant IS + instruction RETURN val;
• Types SANS taille dans la signature : NUMBER, VARCHAR2, pas NUMBER(6,2)
• 3 modes paramètres : IN (entrée) · OUT (sortie) · IN OUT (entrée+sortie)
• Valeur par défaut : p_1 IN type DEFAULT valeur
• Variables internes : NUMBER · tbl.col%TYPE · tbl%ROWTYPE · types RECORD locaux
• Appel dans bloc PL/SQL : v_res := f_nom(args); ou proc_nom(args);
• Appel SQL*Plus : SELECT f_nom(args) FROM DUAL; · EXEC proc_nom(args);
• Fonctions natives utiles :
  • Chaînes : || (concat), trim, length, substr
  • Numériques : ceil, floor, round, trunc, sqrt, avg, max
  • Date : to_date, to_char, day, month, year, next_day
  • Transformation : decode, coalesce, nvl
• Curseur : objet manipulant l'ensemble des résultats d'un SELECT
• Curseur implicite : SELECT … INTO v FROM …; (1 ligne uniquement) ou EXECUTE IMMEDIATE r_req INTO v USING …;
• Curseur explicite :

  CURSOR cur IS SELECT * FROM tbl;
  v cur%ROWTYPE;
  BEGIN
     OPEN cur;
     LOOP
        FETCH cur INTO v;
        EXIT WHEN cur%NOTFOUND;
        …
     END LOOP;
     CLOSE cur;
  END;

• Attributs : cur%FOUND · cur%NOTFOUND · cur%ISOPEN · cur%ROWCOUNT
• Curseur FOR LOOP (auto open/fetch/close) : FOR v IN cur LOOP … END LOOP; ou FOR v IN (SELECT … FROM …) LOOP …
• Curseur paramétré : CURSOR cur(p_num NUMBER) IS SELECT … WHERE no = p_num; · OPEN cur(12);
• Transactions PL/SQL : COMMIT; · ROLLBACK [TO sp]; · SAVEPOINT sp; · SET TRANSACTION [READ ONLY|READ WRITE] [NAME 'tr_nom']
• Transaction autonome : PRAGMA AUTONOMOUS_TRANSACTION; + COMMIT; en fin — utile pour audit/log indépendant de la transaction appelante
• Une procédure stockée ne devrait pas commiter sauf si autonome — laisser au code appelant
• Bloc EXCEPTION : EXCEPTION WHEN ex1 THEN … WHEN ex2 THEN … WHEN OTHERS THEN …
• Exceptions classiques :
  • DUP_VAL_ON_INDEX (ORA-00001) — doublon clé
  • NO_DATA_FOUND (ORA-01403) — SELECT INTO sans résultat
  • TOO_MANY_ROWS (ORA-01422) — SELECT INTO multi-résultats → utiliser un curseur
  • ZERO_DIVIDE · INVALID_NUMBER
• Codes : SQLCODE (numéro ORA-xxxx) · SQLERRM (message)
• Créer une exception perso : e EXCEPTION; PRAGMA EXCEPTION_INIT(e, -20001); · puis RAISE e; · attraper avec WHEN e THEN …
• Lever ponctuellement : RAISE_APPLICATION_ERROR(code, message); · code ∈ [-20999, -20000]
• Une exception levée puis re-lancée vers l'appelant via RAISE_APPLICATION_ERROR peut être gérée par l'appelant via une PRAGMA EXCEPTION_INIT avec le même code
• Package : regroupe procédures/fonctions, exceptions, curseurs, variables/constantes
  • Déclaration (header) : signatures publiques · CREATE [OR REPLACE] PACKAGE pkg IS … END pkg;
  • Body (description) : implémentation + déclarations privées · CREATE [OR REPLACE] PACKAGE BODY pkg IS … END pkg;
  • Tout le package est chargé en mémoire à la première utilisation
  • Variables globales du package partagées sur la session

★ Patterns d'examen incontournables

Les exercices canoniques qui reviennent sur les trois dernières années — à savoir écrire les yeux fermés.

① Trigger d'auto-incrément avec séquence

CREATE OR REPLACE TRIGGER trig_id_tbl
BEFORE INSERT ON tbl
FOR EACH ROW
BEGIN
   :new.id := seq_tbl.NEXTVAL;
END;

② Trigger garde-fou avec RAISE_APPLICATION_ERROR

CREATE OR REPLACE TRIGGER trig_check_stock
BEFORE UPDATE OF qte ON tbl
FOR EACH ROW
BEGIN
   IF :new.qte < 0 THEN
      RAISE_APPLICATION_ERROR(-20100, 'qte négative interdite');
   END IF;
END;

③ Trigger de cumul (Cumul_Depenses_Client, qtetot)

CREATE OR REPLACE TRIGGER trig_maj_ca
AFTER INSERT ON commande
FOR EACH ROW
BEGIN
   UPDATE client
   SET ca_cumule = ca_cumule + :new.total
   WHERE id_client = :new.id_client;
END;

④ Procédure paramétrée avec curseur + exception

CREATE OR REPLACE PROCEDURE liste_commandes_client(p_id IN NUMBER)
IS
   CURSOR c IS
      SELECT id_cmde, total, date_cmde
      FROM Commande
      WHERE id_client = p_id;
   v_nom Client.nom%TYPE;
BEGIN
   SELECT nom INTO v_nom FROM Client WHERE id_client = p_id;
   DBMS_OUTPUT.PUT_LINE('Commandes de ' || v_nom);

   FOR r IN c LOOP
      DBMS_OUTPUT.PUT_LINE(r.id_cmde || ' · ' || r.total || ' € · ' || r.date_cmde);
   END LOOP;
EXCEPTION
   WHEN NO_DATA_FOUND THEN
      DBMS_OUTPUT.PUT_LINE('Client introuvable');
   WHEN TOO_MANY_ROWS THEN
      DBMS_OUTPUT.PUT_LINE('Plusieurs clients avec cet id');
END;

⑤ Vue de recomposition BDDR (fragmentation H)

CREATE VIEW Client_complet AS
   SELECT * FROM Client@lille
   UNION
   SELECT * FROM Client@lyon
   UNION
   SELECT * FROM Client@toulouse;

-- ou pour une fragmentation V :
CREATE VIEW V AS
   SELECT T1.cle, T1.attr1, T2.attr2
   FROM T1@s1, T2@s2
   WHERE T1.cle = T2.cle;

⑥ Trigger INSTEAD OF sur vue de jointure

CREATE OR REPLACE TRIGGER trig_ins_vue
INSTEAD OF INSERT ON vue_jointe
DECLARE
   v_id parent.id%TYPE;
BEGIN
   BEGIN
      SELECT id INTO v_id FROM parent WHERE nom = :new.nom_parent;
   EXCEPTION
      WHEN NO_DATA_FOUND THEN
         INSERT INTO parent VALUES (seq_p.NEXTVAL, :new.nom_parent)
            RETURNING id INTO v_id;
   END;
   INSERT INTO enfant VALUES (seq_e.NEXTVAL, :new.nom_enfant, v_id);
END;