Aller au contenu
Encyclopédie informatique Édition 2026

Les SGBD : MySQL, PostgreSQL, Oracle

Partie 3 sur 4

Niveau de lecture :

6Oracle : histoire et architecture

6.1Une histoire en trois temps

La société Oracle est fondée en 1977 par Lawrence Ellison, Bob Miner et Ed Oates sous le nom de Software Development Laboratories ; elle devient ensuite Relational Software Inc. (RSI) puis Oracle Corporation en 1983. Les dates suivantes restent un bon résumé :

  • 1979 : premier produit commercial (appelé version 2, il n’y a jamais eu de version 1 publique, ce qui faisait « plus mûr »), écrit en langage d’assemblage ; la séparation des espaces entre programmes utilisateurs et noyau y est déjà présente ;
  • 1983 : version 3, entièrement réécrite en langage C, ce qui la rend portable ;
  • 1984-1985 : version 4 (sur les machines IBM), puis version 5 qui permet l’usage client-serveur grâce à un logiciel d’intermédiaire, SQL*Net ;
  • 1988 : version 6, disponible sur de nombreuses plates-formes ; 1991 : option de serveur parallèle ;
  • 1992 : version 7 sur Unix (Windows en 1995), meilleure gestion de la mémoire ; 1997-1998 : version 8, qui ajoute des capacités objet ; version 8.0.5 sur Linux.

Depuis une quinzaine d’années, les versions successives ont ajouté un suffixe qui donne leur ère : i comme Internet (8i, 9i), g comme grid, informatique en grille (10g, 11g), c comme cloud (12c, 18c, 19c, 21c), puis ai comme intelligence artificielle (23ai, devenue 26ai). La version 19c est la version « à long terme » la plus déployée. Oracle a aussi racheté Sun (et donc MySQL, voir partie 4) et de nombreux éditeurs de logiciels d’entreprise.

6.2Les versions et les fonctions

Oracle se décline en plusieurs éditions : une édition « Standard » avec les outils courants (elle n’est « pas pour autant une version bridée »), une édition « Enterprise » avec toutes les options (partitionnement des tables, chiffrement avancé, haute disponibilité), et une édition gratuite limitée en taille et en ressources. Le prix des licences, calculé selon le nombre de processeurs ou d’utilisateurs, est un facteur de choix important, ainsi que la rigueur des contrats d’audit.

Oracle assure « la définition et la manipulation des données, la cohérence, la confidentialité, l’intégrité, la sauvegarde et la restauration, la gestion des accès concurrents » : ce sont les fonctions de n’importe quel SGBD sérieux (voir les bases de données). Son langage procédural interne, PL/SQL, permet d’écrire des procédures dans la base elle-même.

6.3Instance et base : la distinction centrale

UNE INSTANCE ORACLE ET SES FICHIERS INSTANCE = mémoire partagée (SGA) + processus Zone partagée requêtes, dictionnaire Cache de blocs données lues Cache du journal modifications DBWR écrit données LGWR écrit journal SMON vérifie, répare PMON nettoie les verrous La PGA, mémoire privée, sert chaque processus serveur. Fichiers de données extension .dbf Fichiers de journal (redo) extension .rdo ou .log Fichiers de contrôle extension .ctl La base = les fichiers sur disque ; l’instance = ce qui tourne en mémoire. Les deux sont nécessaires.
Figure 3. Une instance Oracle (mémoire et processus) et les fichiers qu’elle gère.

Une notion s’applique à Oracle plus clairement qu’aux autres : la différence entre la base et l’instance.

  • La base de données est l’ensemble des fichiers physiques stockés sur le serveur : ce qui persiste quand on éteint la machine.
  • L’instance est ce qui tourne : les processus chargés en mémoire plus une zone de mémoire commune appelée SGA. L’instance démarre, ouvre la base, la sert, puis s’arrête ; la base, elle, continue d’exister sur disque.

On le dit en une phrase : « on appelle instance Oracle les processus et la SGA d’une base de données Oracle ». Il faut ajouter que, sur les machines très puissantes, plusieurs instances peuvent servir une même base (c’était l’option de « serveur parallèle » de 1991, devenue la grappe actuelle), et que l’inverse n’a pas de sens : une instance qui s’arrête ne perd pas les données, qui sont dans les fichiers.

6.4Les fichiers

On distingue deux sortes de fichiers : ceux qui contiennent les informations de la base et ceux qui servent à la configuration. Une base Oracle comporte au minimum un fichier de données, deux fichiers de journal et un fichier de contrôle. Tous sont dans un format binaire propre à Oracle : on ne les ouvre pas avec un éditeur de texte, et surtout on ne les modifie jamais à la main ; on passe par SQL.

  • Les fichiers de données (extension .dbf) contiennent les données des utilisateurs (tables, index) mais aussi le dictionnaire (partie 6.7). Leur taille va de quelques mégaoctets à plusieurs gigaoctets. La lecture est faite par les processus qui servent les utilisateurs ; l’écriture est assurée par un processus spécialisé, DBWR.
  • Les fichiers journaux (en anglais redo log, « journal à rejouer » ; extension .rdo ou .log) enregistrent la suite des modifications faites dans la base, pour pouvoir retrouver la base après la panne d’un disque ou d’un serveur : Oracle rejoue les modifications qui n’avaient pas encore atteint les fichiers de données. L’écriture est confiée au processus LGWR. En mode archivage, les journaux pleins sont copiés dans un espace d’archivage avant d’être réutilisés : on peut alors restaurer la base à n’importe quel instant (restauration « à un point dans le temps ») ; sans ce mode, le contenu est écrasé. Les journaux peuvent être multiplexés (copiés en plusieurs exemplaires sur des disques différents) pour plus de sûreté.
  • Les fichiers de contrôle (extension .ctl) mémorisent l’état de la base : nom, date de création, emplacement de tous les autres fichiers, informations de synchronisation. Au démarrage, Oracle les consulte pour savoir si la base a été arrêtée correctement. Ils sont eux-mêmes repérés par le fichier d’initialisation.
  • Le fichier d’initialisation (initSID.ora, où SID est le nom de l’instance) est un fichier texte qui contient les paramètres de démarrage (taille des mémoires, emplacement des fichiers de contrôle). On le dit « facilement reconstruit », puisqu’un fichier par défaut abondamment commenté est créé avec la base. Depuis la version 9i (2001), Oracle utilise plutôt un fichier de paramètres binaire (SPFILE) qui permet de modifier des paramètres pendant que l’instance tourne et de les conserver.

Oracle a défini une méthode d’organisation standard de ces fichiers, l’OFA (Optimal Flexible Architecture, « architecture optimale et flexible »), qui recommande par exemple de répartir fichiers de données, journaux et fichiers de contrôle sur des disques différents pour que la panne d’un disque ne détruise pas tout.

6.5Les espaces logiques : tablespaces, segments, extents, blocs

Le stockage s’organise en couches, de la plus grosse à la plus petite :

  • un tablespace (« espace de tables ») est un espace logique de stockage auquel correspond au moins un fichier physique ; on range par exemple les données d’une application dans un tablespace, ses index dans un autre ;
  • un tablespace est divisé en segments, chacun alloué à une structure précise : segment de table, d’index, d’annulation (rollback) ;
  • un segment est composé d’un ou plusieurs extents, ensembles de blocs contigus ;
  • un bloc Oracle est la plus petite unité de lecture et d’écriture, composé d’un ou plusieurs blocs du système de fichiers.

Grâce à cette organisation, le gestionnaire peut décider logiquement où mettre quoi (par exemple placer les tables très sollicitées sur des disques rapides), sans se soucier des fichiers un par un. Une évolution à noter : les « segments d’annulation » (rollback segments) gérés à la main sont remplacés depuis la version 9i par un espace d’annulation géré automatiquement.

6.6La mémoire : SGA et PGA

Oracle fait un usage intensif de la mémoire vive du serveur (voir la mémoire) : garder en mémoire les données souvent lues, c’est éviter d’aller les chercher sur disque, des milliers de fois plus lent. On le résume ainsi : la taille de la mémoire est « primordiale pour la performance ». Quand la mémoire physique est épuisée, le système d’exploitation utilise un fichier d’échange sur disque (en anglais swap), et la base ralentit nettement. Il y a deux grandes zones :

  • la SGA (System Global Area, « zone globale du système »), mémoire partagée entre tous les utilisateurs, qui contient :
    • la zone partagée (shared pool) : les requêtes SQL et programmes PL/SQL analysés (pour ne pas refaire l’analyse quand deux utilisateurs posent la même question), le dictionnaire de données, les curseurs ;
    • le cache de blocs de données (database buffer cache, ou Db block buffer) : copies en mémoire des blocs de données, d’index et d’annulation qui transitent entre la base et les utilisateurs ;
    • le cache du journal (redo log buffer) : les modifications à enregistrer dans les journaux avant leur écriture sur disque ;
  • la PGA (Program Global Area, « zone globale du programme »), mémoire privée de chaque processus, pour ce qui n’a pas besoin d’être partagé : tris, variables de session.

La SGA et les processus d’arrière-plan forment l’instance : à chaque démarrage, la mémoire est allouée ; à l’arrêt, elle est rendue. Les tailles se règlent dans les paramètres d’initialisation, ou, depuis la version 10g, sont ajustées automatiquement par Oracle dans une limite totale fixée par l’administrateur.

6.7Les processus

Une instance Oracle fait tourner plusieurs processus (voir les processus). On distingue des processus utilisateurs (le programme qui se connecte, par exemple l’outil en ligne de commande SQL*Plus) et des processus Oracle, eux-mêmes de deux sortes : les processus serveurs, qui exécutent les requêtes d’un utilisateur et lisent les blocs dans la SGA, et les processus d’arrière-plan, chargés du fonctionnement interne. Chaque processus a un nom du genre ora_nomduprocessus_SID. Les principaux :

  • DBWR (Database Writer, « écrivain de la base ») : écrit dans les fichiers de données les blocs modifiés en mémoire, qu’on appelle blocs sales (dirty blocks). Il ne le fait pas à chaque modification, mais en groupe, pour aller vite.
  • LGWR (Log Writer) : écrit le cache du journal dans les fichiers journaux, à chaque COMMIT, c’est-à-dire à chaque validation d’une transaction. C’est ce qui garantit la durabilité : une fois la validation confirmée, la modification est dans le journal, donc récupérable même si les fichiers de données n’ont pas encore été écrits.
  • SMON (System Monitor) : vérifie la cohérence au démarrage suivant un arrêt anormal, annule les transactions non validées en utilisant les informations d’annulation, libère les espaces inutilisés et regroupe les espaces libres des fichiers (ce qu’on appelle parfois la défragmentation).
  • PMON (Process Monitor) : récupère les ressources des processus utilisateurs qui ont échoué ; supprime les processus en erreur, annule leurs transactions en cours (par exemple si un client est coupé brutalement), libère les verrous et la mémoire.
  • Les processus secondaires : CKPT (checkpoint, « point de contrôle » : signale que tout ce qui précède est écrit sur disque, ce qui raccourcit la reprise après panne), RECO (résolution des transactions interrompues dans les bases réparties), ARCH (copie des journaux pleins vers l’archivage, en mode archivage), Dnnnn et Snnnn (répartiteurs et serveurs, dans la configuration dite « MTS », multi-threaded server, renommée « serveur partagé » depuis 9i), LCKn (verrous, dans l’option de serveur parallèle).

Une précision à propos de SMON : on lit parfois qu’il annule les informations en attente « appelées deadlocks ». Ce terme est une erreur d’emploi : un deadlock (« interblocage ») est la situation où deux transactions s’attendent mutuellement et ne peuvent plus avancer ; les modifications non validées d’une transaction interrompue n’en sont pas. Un interblocage est détecté par Oracle, qui annule l’une des deux instructions.

6.8Le dictionnaire de données

Le dictionnaire est le « cœur » de la base : un ensemble de tables système qui décrivent la structure de la base : les utilisateurs (avec leurs privilèges et leurs rôles), les objets (tables, vues, index, déclencheurs, paquets de code), les contraintes d’intégrité, les ressources physiques allouées. Il est créé en même temps que la base, mis à jour automatiquement par Oracle à chaque opération de définition (créer une table, ajouter un utilisateur) et stocké dans le tablespace nommé SYSTEM. Il appartient à l’utilisateur SYS, et l’administrateur peut le lire par des vues. Il sert surtout à vérifier la syntaxe et les droits de chaque requête, et à renseigner sur la structure de la base. On remarquera que c’est l’une des règles de Codd : le catalogue d’une base relationnelle est lui-même fait de tables qu’on interroge avec SQL (voir le modèle relationnel).

Les vues du dictionnaire sont classées en familles, reconnaissables à leur préfixe :

  • USER_ : ce qui appartient à l’utilisateur connecté (ses tables, ses index, ses vues) ;
  • ALL_ : ce que l’utilisateur a le droit d’utiliser, y compris les objets d’autres comptes ;
  • DBA_ : tout, réservé à l’administrateur de la base (DBA, DataBase Administrator) ; ces vues contiennent des informations sensibles ;
  • V$ : des vues dynamiques sur l’état courant de l’instance, de son démarrage à son arrêt : fichiers actuellement utilisés, sessions connectées, mémoire.

Une requête comme SELECT table_name FROM user_tables; liste les tables de l’utilisateur, par exemple. PostgreSQL et MySQL ont leurs équivalents, nommés information_schema (défini par la norme SQL) et des catalogues propres.

6.9Les outils d’Oracle, hier et aujourd’hui

On classe les outils en catégories : administration, développement, communication, génie logiciel, aide à la décision. On cite Oracle Manager (SQL*DBA), Network Manager, Enterprise Manager, l’outil d’import-export, SQL*Plus (interface de commande qui envoie du SQL et du PL/SQL), la suite Oracle Developer (Forms pour les écrans, Reports pour les états, Graphics pour les graphiques, Procedure Builder), Designer, et les précompilateurs Pro*C, Pro*Cobol, Pro*Fortran, Pro*Pascal, Pro*PLI, qui permettent d’insérer du SQL dans un programme dans ces langages. Ce panorama a vieilli : SQL*DBA a disparu depuis longtemps, Forms et Reports ont été remplacés ou conservés pour des applications existantes, et l’outil d’import-export exp/imp est remplacé depuis la version 10g par Data Pump (partie 8). Aujourd’hui, on utilise surtout SQL*Plus et l’outil graphique SQL Developer, un outil de ligne de commande évolué (SQLcl), Enterprise Manager et l’outil de sauvegarde RMAN. Les précompilateurs existent encore pour les très anciens programmes.

6.10L’architecture multi-locataire

Depuis la version 12c (2013), Oracle propose une architecture « multi-locataire » (multitenant) : une base conteneur qui héberge plusieurs bases enfichables (pluggable databases), chacune ayant l’apparence d’une base complète. L’instance, la mémoire et les processus sont partagés ; chaque base enfichable a ses données et ses utilisateurs, et peut être déplacée, copiée ou « débranchée » comme un module. C’est un moyen de regrouper de nombreuses petites bases sur un seul serveur. Le même vocabulaire (instance, fichiers, mémoire, processus) reste valable pour comprendre le tout.

Pour s’exercer

Exercice 3 : instance ou base ?

Pour chaque élément, dites s’il appartient à l’instance ou à la base d’Oracle : (a) un fichier .dbf ; (b) le processus DBWR ; (c) la SGA ; (d) le fichier de contrôle ; (e) le cache de blocs de données.

Voir la réponse

(a) base ; (b) instance ; (c) instance ; (d) base ; (e) instance, puisque c’est un morceau de la SGA. La règle : ce qui est sur disque et survit à l’arrêt appartient à la base ; ce qui est en mémoire ou en cours d’exécution appartient à l’instance.

Exercice 4 : que se passe-t-il à la validation ?

Un utilisateur modifie une ligne puis valide (COMMIT). Le serveur tombe en panne une seconde plus tard, avant que DBWR n’ait écrit le bloc dans le fichier de données. La modification est-elle perdue ? Pourquoi ?

Voir la réponse

Non. À la validation, LGWR a écrit la modification dans le fichier journal (redo) avant de confirmer à l’utilisateur. Au redémarrage, SMON constate l’arrêt anormal et Oracle rejoue le journal pour appliquer aux fichiers de données la modification manquante. C’est la propriété de durabilité des transactions. En revanche, si l’utilisateur n’avait pas encore validé, la modification est annulée.

7Deux fonctions d’Oracle : séquences et requêtes hiérarchiques

7.1Les séquences

Une séquence est un objet de la base, comme une table ou une vue : il appartient à un utilisateur, qui peut le manipuler et le modifier. Elle fournit une suite de nombres entiers, et sert à deux choses : fabriquer des clés uniques pour les tables (le numéro d’une commande, l’identifiant d’un client), et tenir un compteur. Plusieurs utilisateurs peuvent prélever des valeurs en même temps sans jamais obtenir la même (voir le modèle relationnel pour le rôle des clés).

La plus simple s’écrit CREATE SEQUENCE ma_sequence;. Pour s’en servir, on utilise deux « pseudo-colonnes » (elles s’emploient comme des colonnes mais n’existent dans aucune table) :

  • ma_sequence.NEXTVAL fait avancer la séquence et renvoie la nouvelle valeur ;
  • ma_sequence.CURRVAL renvoie la valeur courante, c’est-à-dire celle que votre session a obtenue en dernier.

Une subtilité qui surprend : au début, il faut appeler NEXTVAL avant CURRVAL, sinon Oracle répond par une erreur (« séquence pas encore définie dans cette session »). Et, dans une même instruction, plusieurs appels de NEXTVAL donnent la même valeur : SELECT s.NEXTVAL + s.NEXTVAL FROM dual additionne deux fois la même valeur. (La table dual d’Oracle est une table spéciale d’une seule ligne, utilisée quand on veut évaluer une expression sans table.)

Voici les paramètres de la séquence :

  • START WITH : la première valeur (par défaut 1) ;
  • INCREMENT BY : le pas (par défaut 1, peut être négatif pour une suite descendante) ; START WITH 5 INCREMENT BY 3 donne 5, 8, 11, 14…
  • MAXVALUE et MINVALUE : un plafond ou un plancher (par défaut, il n’y a pas de limite pratique : NOMAXVALUE, NOMINVALUE) ;
  • CYCLE ou NOCYCLE : à la limite, soit la séquence repart de l’autre bout, soit elle s’arrête avec une erreur (comportement par défaut) ;
  • CACHE : nombre de valeurs préparées en mémoire (20 par défaut), pour éviter une écriture dans le dictionnaire à chaque appel ; l’effet sur les performances est considérable, mais, en cas d’arrêt brutal, les valeurs préparées et non utilisées sont perdues : il y a des « trous » dans la suite, ce qui est normal ; une séquence ne garantit que l’unicité, pas l’absence de trous ;
  • ORDER : impose le respect de l’ordre de demande, utile seulement dans une grappe de plusieurs instances.

On les modifie ensuite par ALTER SEQUENCE et on les supprime par DROP SEQUENCE. Les paramètres sont modifiables après création, sauf la valeur de départ.

Voici un exemple complet, avec une table et son identifiant fourni par une séquence :

CREATE SEQUENCE seq_client START WITH 1 INCREMENT BY 1 NOCYCLE;

INSERT INTO client (id, nom)
VALUES (seq_client.NEXTVAL, 'Ada');

Une question revenait souvent sur les forums : « comment faire une colonne auto-incrémentée ? » La réponse était qu’Oracle ne dispose pas de l’option auto_increment que l’on rencontre dans MySQL, et qu’il fallait créer une séquence, plus un déclencheur (trigger) exécuté à chaque insertion pour remplir la colonne. Ce n’est plus vrai : depuis la version 12c (2013), Oracle accepte des colonnes d’identité (GENERATED ALWAYS AS IDENTITY), conformes à la norme SQL, qui fabriquent la séquence et le lien automatiquement. PostgreSQL offre les mêmes (et le type serial historique), MySQL garde AUTO_INCREMENT, SQL Server a IDENTITY. Les séquences indépendantes sont aujourd’hui prises en charge par PostgreSQL, MariaDB, SQL Server et d’autres, et par la norme SQL.

7.2Les requêtes hiérarchiques

Beaucoup de données forment des arbres : un forum de discussion (un message et ses réponses, elles-mêmes avec leurs réponses), un organigramme (chef et subordonnés), une nomenclature (un produit et ses composants), des catégories de boutique. On les stocke dans une seule table où chaque ligne indique son parent : prenons l’exemple d’une table de messages avec deux colonnes id et id_parent, où id_parent est vide pour le premier message d’un fil et contient l’id du message auquel on répond sinon. Le but : afficher les messages dans l’ordre de la discussion, chaque réponse sous son message, avec une indentation selon la profondeur.

Avec les opérations ordinaires, ce parcours est impossible à écrire proprement, parce qu’on ne sait pas combien de niveaux il y a. Oracle fournit depuis longtemps une syntaxe dédiée :

SELECT LEVEL, LPAD(' ', 2 * (LEVEL - 1)) || titre AS titre
FROM messages
START WITH id_parent IS NULL
CONNECT BY PRIOR id = id_parent;
  • START WITH désigne les lignes de départ, celles du niveau 1 (sans parent au-dessus) ; avec la condition auteur = 'John' on ne retient que les fils lancés par John, avec toutes leurs réponses ;
  • CONNECT BY PRIOR id = id_parent dit comment passer d’une ligne à ses enfants : « l’id du parent est égal à l’id_parent de l’enfant » ; le mot PRIOR désigne la valeur de la ligne parente ;
  • la pseudo-colonne LEVEL donne la profondeur (1 pour la racine, 2 pour ses enfants, etc.), qu’on utilise pour décaler le titre par des espaces.

Une requête hiérarchique sert donc à la fois à trier les lignes selon l’arborescence et à sélectionner un sous-arbre, sans clause WHERE. Autres usages : calculer tous les subordonnés d’un chef, tous les composants d’un produit, tous les ancêtres d’un élément. Cette syntaxe, déjà présente dans la version 8.1.5 d’Oracle, existe toujours.

Depuis, la norme SQL a défini une autre écriture, plus générale, les requêtes récursives (WITH RECURSIVE), reconnues par PostgreSQL, MySQL (depuis la version 8.0), MariaDB, SQL Server, SQLite et Oracle (sous une forme voisine depuis la version 11g). Elle exprime « les lignes de départ, puis, de proche en proche, les enfants des lignes déjà trouvées, jusqu’à ce qu’il n’y en ait plus ». Elle est portable d’un produit à l’autre, ce qui n’est pas le cas de CONNECT BY. Attention aux données qui forment une boucle (A est le parent de B qui est le parent de A) : sans protection, la requête tourne sans fin ; les deux écritures prévoient une détection de cycles.

Pour s’exercer

Exercice 5 : une séquence

On crée CREATE SEQUENCE s START WITH 10 INCREMENT BY 5 MAXVALUE 25 NOCYCLE;. Quelles valeurs obtient-on par appels successifs de s.NEXTVAL ? Que se passe-t-il ensuite ?

Voir la réponse

10, 15, 20, 25. Le cinquième appel dépasserait le plafond de 25 : comme la séquence est sans cycle, Oracle renvoie une erreur. Avec CYCLE, elle serait repartie de la valeur minimale (par défaut 1). Il y aurait en outre des trous si une partie des valeurs préparées en mémoire était perdue lors d’un arrêt brutal, ce qui est sans gravité tant que l’on ne demande à la séquence que l’unicité.

Exercice 6 : une requête hiérarchique

La table personne(id, nom, id_chef) contient : (1, Ada, vide), (2, Alan, 1), (3, Grace, 1), (4, Linus, 2). Quels résultats donne SELECT nom, LEVEL FROM personne START WITH id_chef IS NULL CONNECT BY PRIOR id = id_chef; ?

Voir la réponse

La racine est Ada (niveau 1). Ses subordonnés directs, Alan et Grace, sont au niveau 2. Le subordonné d’Alan, Linus, est au niveau 3. Les résultats, dans l’ordre du parcours : (Ada, 1), (Alan, 2), (Linus, 3), (Grace, 2). L’ordre est celui d’un parcours en profondeur : on descend dans chaque branche avant de passer à la suivante.