Oracle C'est un SGBD (système de gestion de bases de données) édité par la société du même nom (Oracle Corporation - www.oracle.com), leader mondial des bases de données.
La société Oracle Corporation a été créée en 1977 par Lawrence Ellison, Bob Miner, et Ed Oates. Elle s'appelle alors Relational Software Incorporated (RSI) et commercialise un Système de Gestion de Bases de données relationnelles (SGBDR ou RDBMS pour Relational Database Management System) nommé Oracle.

En 1979, le premier prototype (RDBMS - RSI1) intégrant la séparation des espaces d'adressage entre les programmes utilisateurs et le noyau Oracle est commercialisé. Cette version est entièrement développée en langage assembleur. La seconde version (RDBMS - RSI2) est un portage de l'application sur d'autres plates-formes.
En 1983 la troisième version apporte des améliorations au niveau des performances et une meilleure prise en charge du SQL. Cette version est entièrement codée en langage C. A la même époque RSI change de raison sociale et devient Oracle. En 1984 la première version d'Oracle (Oracle 4) est commercialisée sur les machines IBM. En 1985 Oracle 5 permet une utilisation client-serveur grâce au middleware SQL*Net. En 1986 Oracle a été porté sur la plateforme 8086. En 1988 Oracle 6 est disponible sur un grand nombre de plates-formes et apporte de nombreuses nouvelles fonctionnalités ainsi qu'une amélioration notable des performances. En 1991, Oracle 6.1 propose une option Parallel Server (dans un premier temps sur la DEC VAX, puis rapidement sur de nombreuses autres plates-formes). En 1992, Oracle 7 sort sur les plates-formes UNIX (elle ne sortira sur les plates-formes Windows qu'à partir de 1995). Cette version permet une meilleure gestion de la mémoire, du CPU et des entrées-sorties. La base de données est accompagnée d'outils d'administration (SQL*DBA) permettant une exploitation plus aisée de la base. En 1997, la version Oracle 7.3 (baptisée Oracle Universal Server) apparaît, suivie de la version 8 offrant des capacités objet à la base de données Oracle est écrit en langage C et est disponible sur de nombreuses plates-formes matérielles (plus d'une centaine) dont : " AIX (IBM) " Solaris (Sun) " HP/UX (Hewlett Packard) " Windows NT (Microsoft) Oracle depuis la version 8.0.5 est disponible sous Linux Les versions d'Oracle Oracle se décline en plusieurs versions " Oracle Server Standard, une version comprenant les outils les plus courants de la solution Oracle. Il ne s'agit pas pour autant d'une version bridée...

" Oracle Server Enterprise Edition
Les fonctionnalités d'Oracle Oracle est un SGBD permettant d'assurer : " La définition et la manipulation des données " La cohérence des données " La confidentialité des données " L'intégrité des données " La sauvegarde et la restauration des données " La gestion des accès concurrents Les composants d'Oracle Outre la base de données, la solution Oracle est un véritable environnement de travail constitué de nombreux logiciels permettant notamment une administration graphique d'Oracle, de s'interfacer avec des produits divers et d'assistants de création de bases de données et de configuration de celles-ci.

On peut classer les outils d'Oracle selon diverses catégories : " Les outils d'administration " Les outils de développement " Les outils de communication " Les outils de génie logiciel " Les outils d'aide à la décision Les outils d'administration d'Oracle Oracle est fourni avec de nombreux outils permettant de simplifier l'administration de la base de données. Parmi ces outils, les plus connus sont : " Oracle Manager (SQL*DBA) " NetWork Manager " Oracle Enterprise Manager " Import/Export : un outil permettant d'échanger des données entre deux bases Oracle Outils de développement d'Oracle Oracle propose également de nombreux outils de développement permettant d'automatiser la création d'applications s'interfaçant avec la base de données. Ces outils de développement sont : " Oracle Designer " Oracle Developer " SQL*Plus : une interface interactive permettant d'envoyer des requêtes SQL et PL/SQL à la base de données. SQL*Plus permet notamment de paramétrer l'environnement de travail (formatage des résultats, longueur d'une ligne, nombre de lignes par page, ...) " Oracle Developper : il s'agit d'une suite de produits destinés à la conception et à la création d'applications client-serveur. Il est composé de 4 applications : o Oracle Forms (anciennement SQL*Forms) : un outil permettant d'interroger la base de données de façon graphique sans connaissances préalables du langage SQL. SQL*Forms permet ainsi de développer des applications graphiques (fenêtres, formulaires, ...) permettant de sélectionner, modifier et supprimer des données dans la base. o Oracle Reports (SQL*ReportWriter) : un outil permettant de réaliser des états o Oracle Graphics : un outil de génération automatique de graphiques dynamiques pour présenter graphiquement des statistiques réalisées à partir des données de la base o Procedure Builder : un outil permettant de développer des procédures, des fonctions et des packages Outils de programmation Oracle dispose d'un grand nombre d'interfaces (API) permettant à des programmes écrits dans divers langages de s'interfacer avec la base de données en envoyant des requêtes SQL. Ces interfaces (appelées précompilateurs) forment une famille dont le nom commence par PRO* : " Pro*C " Pro*Cobol " Pro*Fortran " Pro*Pascal " Pro*PLI " ...

Architecture du SGBD Oracle
Une base de données Oracle est constituée de plusieurs éléments : " Des processus chargés en mémoire sur le serveur " Des fichiers physiques stockés sur le serveur " D'un espace mémoire sur le serveur appelé SGA (System Global Area) On appelle instance Oracle les processus et la SGA d'une base de données Oracle. Les fichiers physiques d'une base Oracle Les fichiers physiques d'une base Oracle permettent de stocker de manière persistante les données manipulées par Oracle, tandis que la mémoire sert à optimiser la vitesse de fonctionnement de la base de donné. On distingue généralement deux types de fichiers : " Les fichiers servant à stocker les informations de la base. Tous ces fichiers sont des fichiers binaires, ce qui signifie qu'ils sont inexploitables avec un éditeur de texte. " Les fichiers destinés à la configuration et au fonctionnement de la base Oracle Oracle a défini une architecture permettant de définir une méthode d'organisation standard des fichiers de la base Oracle. Cette architecture est nommée OFA (Optimal Flexible Architecture). Les fichiers d'une base de données Oracle sont les suivants : " Les fichiers de données (dont l'extension est .dbf). Ces fichiers contiennent l'ensemble des données de la base (les tables, les vues, les procédures stockées, ...). " Les fichiers Redo Log (dont l'extension est .rdo ou .log). Ces fichiers contiennent l'historique des modifications effectuées sur la base de données " Les fichiers de contrôle (dont l'extension est .ctl). Ces fichiers permettent de stocker les informations sur l'état de la base de données (emplacement des fichiers, dates de création, ...) Une base de données Oracle nécessite au minimum un fichier de données, deux fichiers redo Log et un fichier de contrôle. Les fichiers de données Les fichiers de données sont les fichiers occupant la majeure partie de la base de données, leur taille peut osciller entre quelques Mégaoctets et plusieurs gigaoctets. Ceux-ci contiennent en effet toutes les données relatives à la base Oracle dans un format propriétaire. Ainsi pour modifier les informations contenues dans la base de données il est impossible d'intervenir directement sur ces fichiers; la bonne procédure à adopter consiste à modifier le contenu de la base de données par l'intermédiaire d'ordres SQL. Les fichiers de données contiennent des informations de deux types : " Le dictionnaire de données et de travail " Les données des utilisateurs La lecture de ces fichiers de données est faire à l'aide des processus utilisateurs tandis que l'écriture est assuré par le processus DBWR (Database Writer) Les fichiers Redo-log Les fichiers Redo-log contiennent l'historique des modifications apportées à la base de données Oracle. Ces fichiers de journalisation enregistrent les modifications successives de la base de données afin de pouvoir restaurer la base de données en cas de défaillance d'un disque dur. Ainsi le cas échéant, la base de données Oracle est à même de simuler l'ensemble des commandes n'ayant pas été sauvegardées pour rétablir le contenu de la base de données. Au même titre que les fichiers de données, les fichiers Redo-log sont dans un format propriétaire Oracle et l'écriture dans ces fichiers est assurée par le processus LGWR (Log Writer). Oracle propose également un mode archivage permettant la sauvegarde du fichier Redo-log avant sa réutilisation pour restaurer la base. Si ce mode n'a pas été activé, le contenu du fichier Redo Log est supprimé après utilisation. Enfin ces fichiers peuvent être multiplexés (comprenez dupliqués dans des répertoires de groupe) afin de fournir un maximum de sécurité.

Les fichiers de contrôle Les fichiers de contrôle permettent de stocker l'état de la base de données. Ils sont créés lors de la création de la base. Ces fichiers permettent, lors de l'initialisation de la base, de savoir si la base de données a été arrêtée correctement, ainsi que de connaître l'emplacement des fichiers de données et des fichiers Redo Log. Les fichiers de contrôle sont eux-même repérés par le fichier d'initialisation. Le fichier de contrôle contient les informations suivantes : " Nom de la base de données " Date et heure de création de la base " L'emplacement des fichiers journaux (Redo-Log) " Des informations de synchronisation Le fichier d'initialisation Ce fichier est un fichier au format texte contenant l'ensemble des paramètres de démarrage de la base (il est généralement nommé initSID.ora, où SID représente le nom donné à l'instance). Son existence n'est toutefois pas majeure car il peut être facilement reconstruit. Un fichier d'initialisation par défaut est créé lors de la création d'une base. Celui-ci est largement documenté et des exemples de valeurs sont donnés pour chaque paramètre. Toutefois parmi ces paramètres, seul un nombre limité d'entre-eux est réellement utile. Les processus Le fonctionnement de la base Oracle est régi par un certain nombre de processus chargés en mémoire permettant d'assurer la gestion de la base de données. On distingue généralement deux types de processus : " les processus utilisateurs (appelés aussi user process ou noyau oracle) On distingue deux types de processus utilisateurs : o Oracle Server Code, aussi appelé noyau d'Oracle, est chargé d'interpréter et d'exécuter les requêtes SQL, ainsi que de gérer la mémoire et les fichiers de données o Code spécifique de l'outil, l'implémentation qui exécute réellement les commandes SQL. " les processus systèmes (oracle process). Les processus Oracle (processus système) se classent en deux catégories : o Les processus serveurs (process server) gérant les requêtes des utilisateurs provenant des connections à la base de données générées par des outils tels que SQL*Plus. Le processus serveur est chargé de la communication entre la SGA et le processus utilisateur. Il permet ainsi d'analyser et d'exécuter les requêtes SQL des utilisateurs, de lire les fichiers de données et de placer les blocs de données correspondants dans la SGA. o Les processus d'arrière-plan (background process) chargés d'assurer le fonctionnement interne du SGBD Oracle (gestion de la mémoire, écriture dans les fichiers, ...). Un processus utilisateur est créé pour chaque programme exécuté par un utilisateur (par exemple Oracle Forms ou Server Manager) afin de fournir l'environnement nécessaire à l'exécution de celui-ci. Le processus utilisateur ainsi créé communique avec les processus systèmes à travers le programme interface. Chaque processus a pour nom ora_nomduprocessus_SID où SID représente le nom de l'instance à laquelle le processus est associé. Les 4 principaux processus systèmes sont : " DBWR (DataBase Writer ou Dirty Buffer Writer), le processus chargé d'écrire le contenu des buffers dans les fichiers de données " LGWR (Log Writer), le processus chargé d'écrire le contenu des buffers dans les fichiers Redo Log " PMON (Process Monitor), le processus chargé de nettoyer les ressources, les verrous et les processus utilisateurs non utilisés " SMON (System Monitor), le processus chargé de vérifier la cohérence de la base de données et éventuellement sa restauration lors du démarrage si besoin Il existe également d'autres processus d'importance secondaire : " CKPT (CheckPoint), le processus chargé d'écrire le contenu des buffers dans les fichiers de données " RECO (Recoverer), il s'agit d'un processus optionnel permettant de résoudre les transactions interrompues brutalement dans un système de bases de données distribuées (par exemple un système de réplication de bases de données) " ARCH (Archiver). Ce processus est optionnel et n'existe qu'en mode ARCHIVELOG. Il permet de dupliquer les fichiers Redo-Log dans un espace d'archivage. " Dnnnn (Dispatcher, nnnn représente une suite de nombre entiers) : Ce processus est optionnel et n'est présent que dans les configurations MTS (multi-threaded server). Il permet de router les requêtes des postes clients-serveurs distants vers les autres serveurs. Il existe au moins un processus Dnnnn pour chaque protocole de communication " Snnnn (Server, nnnn représente une suite de nombre entiers) : Ce processus est n'est également présent que dans les configurations MTS. Il permet de recevoir les demandes de connexions distantes envoyées par le processus Dnnnn d'un serveur distant. " LCKn (Lock) est un processus de verrouillage utilisé lorsque Oracle Parallel Server est installé. Le processus DBWR Le processus Database Writer (DBWR) a pour but de transférer les blocs de données modifiés (appelés dirty blocks) de la System Global Area vers les fichiers de la base de données, afin de sauvegarder de manière permanente les données de la base. Ainsi, lorsqu'un ordre SQL modifie la base de données (c'est-à-dire lorsqu'une requête SQL DELETE, INSERT ou UPDATE est reçue), les blocs de données affectés sont modifiés dans le fichier de données associé. Le processus LGWR Le rôle du processus LGWR (Log Writer) est de mettre à jour les fichiers journaux (Redo Log) dans la SGA et sur le disque. Ainsi ce processus est chargé d'écrire le contenu du cache Redo Log de la SGA dans le fichier Redo Log à chaque fois qu'un ordre COMMIT est réceptionné. Le processus SMON Le processus SMON (System Monitor) est chargé de vérifier la cohérence du système et de la rétablir suite à un incident au démarrage de la base suivant. Ainsi, si la base n'a pas été stoppée correctement, le processus analyse les informations stockées dans les rollback segments (les rollback segments sont les zones de stockage des opérations n'ayant pas encore été validées) puis annule toutes les informations en attente mais pour lesquelles aucune validation n'a été enregistrées (appelées deadlocks). Ainsi SMON a un rôle de libération des ressources utilisées inutilement par le système. D'autre part SMON surveille les espaces libres des fichiers de la base de données et les réorganise si nécessaire afin de les défragmenter. Le processus PMON Le processus PMON (Process Monitor) a pour but de récupérer les ressources associées à des défaillances de processus utilisateurs. Ainsi il supprime les processus en erreur, il annule les transactions n'ayant pas été validées (par exemple si un client est déconnecté brutalement lors de la transaction); il libère les verrous, et libère les ressources utilisées inutilement dans la SGA.

L'utilisation de la mémoire par Oracle. Oracle fait un usage poussé de la mémoire physique (RAM, Random Access Memory) du serveur afin de fournir les meilleures performances possibles.

Ainsi Oracle utilise la mémoire physique du serveur pour : " accélérer l'accès aux données de la base régulièrement accédées " mettre les processus en mémoire " optimiser la communication entre les processus et la base de données Ainsi la taille de la mémoire vive du serveur est primordiale pour la performance de la base de données. En effet, lorsqu'il n'y a plus de mémoire physique disponible, le système d'exploitation met à disposition de l'application une mémoire dite "virtuelle" composée de fichiers d'échange (fichiers swap) sur le disque du serveur. Or l'accès au disque dur est extrêmement plus long que l'accès à la mémoire physique, ce qui provoque des ralentissements notables dans le fonctionnement de la base de données Oracle. Dans une base de données Oracle on distingue généralement deux zones mémoire : " La zone SGA (System Global Area) assurant le partage des données des différents utilisateurs, c'est-à-dire qu'il s'agit de la zone contenant les structures de données accessibles par tous les processus " La zone PGA (Program Global Area) permettant le fonctionnement des divers processus (afin de stocker toutes les données ne nécessitant pas d'être partagées). La SGA (appelée aussi mémoire réservée) est composée de plusieurs constituants : " La Shared Pool (ou zone partagée) contenant des informations pouvant être réutilisées par les différents utilisateurs, c'est-à-dire les requêtes SQL et programmes PL/SQL pouvant être partagés, le dictionnaire de données, les curseurs, ... D'une manière générale, cette zone sert à mémoriser, analyser et traiter les requêtes SQL provenant des divers utilisateurs.

" Le Db block buffer (Database Buffer Cache ou cache des blocs de données) est un espace mémoire contenant toutes les données transitant de ou vers la base de données : blocs de données, blocs d'index et blocs contenant les ROLLBACK SEGMENTS. Il s'agit donc d'un cache permettant d'accélérer l'accès à certaines données de la base. " Le Redo Log buffer (ou cache Redo-log) contient les blocs de données (appelés Redo Entries) à modifier et les modifications à effectuer sur ces données, avant que l'ensemble de ces mises à jour de la base ne soient archivées dans les fichiers Redo-log La SGA et les processus d'arrière plan constituent ce que l'on appelle une instance; A chaque démarrage d'une instance, l'espace mémoire nécessaire à son bon fonctionnement lui est alloué, et est restitué lors de sa fermeture. L'ensemble des tailles des caches peut être modifié (augmentée ou diminuée) grâce aux paramètres du fichier d'initialisation (initSID.ora).

Présentation du dictionnaire de données Oracle Le dictionnaire de données Oracle représente le coeur de la base de données. Il s'agit d'un ensemble de tables systèmes contenant les informations relatives à la structure de la base de données : " Utilisateurs de la base (ainsi que leurs privilèges et leur rôle) " Noms et caractéristiques des objets contenus dans la base (tables, vues, index, clusters, triggers, packages, ...) " Contraintes d'intégrité " Ressources physiques allouées à la base Le dictionnaire est créé au moment de la création de la base et est mis à jour. Il appartient à l'utilisateur SYS, mais l'utilisateur SYSTEM, c'est-à-dire l'administrateur de la base, possède des droits de lecture sur des vues du dictionnaire. Enfin le dictionnaire de données est conservé dans le tablespace SYSTEM. Le dictionnaire de données sert principalement dans deux situations : " afin de vérifier la syntaxe et les privilèges sur une requête SQL (pour le DDL, Data Definition Language) " Afin d'obtenir des informations sur la structure de la base de données

Les différentes vues De nombreuses vues permettent à des utilisateurs d'accéder à certaines parties du dictionnaire de données. Les vues fournissent à l'administrateur de la base le meilleur moyen pour obtenir les caractéristiques techniques de celle-ci. Les vues du dictionnaire de données sont classées par famille et nommées en fonction de l'appartenance à une de ces familles. Voici la liste de ces familles de vues : " Les vues USER (dont le nom commence par USER_) donnent des informations sur tous les objets logiques dont l'utilisateur connecté est propriétaire (tables, index, vues, procédures, ...) " Les vues ALL (dont le nom commence par ALL_) fournissent des informations sur les objets pour lesquels l'utilisateur a un droit d'accès, c'est-à-dire les objets de la base créés par l'utilisateur ainsi que tous les objets accessibles par cet utilisateur. " Les vues DBA (dont le nom commence par DBA_). Ces vues sont réservées à l'administrateur de la base (DBA, DataBase Administrator) afin de lui fournir des informations sensibles sur tous les objets de la base de données. " Les vues V$ (dont le nom commence par V$_) sont des vues dynamiques permettant d'avoir des informations sur l'état courant de l'instance de la base de données de son démarrage à son arrêt. Elles permettent par exemple de connaître les fichiers physiques actuellement utilisés par la base (logs, rollback segments, ...). Cet article s'appuie sur la version 8.1.6 d'Oracle. Une séquence est un objet de base de données Oracle, au même titre qu'une table, une vue, etc... Autrement dit, il appartient à un utilisateur, on peut le manipuler, le modifier, à condition d'avoir les droits nécessaires. Cet article a pour but de définir ce qu'est une séquence, et de présenter les possibilités que cet objet offre. Définition d'une séquence

Définir une séquence équivaut à définir une suite de nombres entiers. L'évolution de cette suite est régie par un certain nombre de paramètres, que nous allons voir ensemble un peu plus loin. L'utilisation d'une séquence permet donc d'avoir à disposition une suite de valeurs. Ceci peut permettre de : " générer des clés uniques dans des tables " avoir un compteur à titre informatif, que l'on incrémente quand on veut " etc...

Ma première séquence Etant donné que la plupart des paramètres ont une valeur par défaut, il n'est pas nécessaire de tous les spécifier lorsqu'on souhaite créer une nouvelle séquence. Voici donc l'ordre SQL minimal de création d'une séquence : CREATE SEQUENCE ma_sequence; Dans cet ordre, "ma_sequence" correspond bien évidemment au nom de la séquence que vous souhaitez créer ; une séquence porte un nom, au même titre qu'une table, ou que tout autre objet dans Oracle. Cela permet de la manipuler... Si vous exécutez cet ordre SQL et si vous disposez des privilèges nécessaires (à savoir CREATE SEQUENCE), Oracle vous répondra bien gentiment "Séquence créée.". Mais comment voir ce qu'il y a dans cette séquence ? Comment l'exploiter ? L'interrogation d'une séquence se fait par l'utilisation des "pseudo-colonnes" CURRVAL et NEXTVAL. On parle de pseudo-colonne car cela se manipule un peu comme une colonne de table, mais ce n'est pas une colonne de table. " La pseudo-colonne CURRVAL retourne la valeur courante de la séquence. " La pseudo-colonne NEXTVAL incrémente la séquence et retourne la nouvelle valeur.

Exemples :
=== SQL> select ztblseq.currval from dual; select ztblseq.currval from dual
* ERREUR à la ligne 1 : ORA-08002: séquence ZTBLSEQ.CURRVAL pas encore définie dans cette session === Cette erreur est due au fait que l'on n'a jamais encore initialisé notre séquence, et que l'on essaye d'en récupérer la valeur courante.
Lors de la première utilisation d'une séquence, il faut utiliser NEXTVAL pour l'initialiser. ===
SQL> select ztblseq.nextval from dual; NEXTVAL --------- 1 === CURRVAL retourne maintenant 1. Si si, essayez... ===
SQL> select ztblseq.currval from dual; CURRVAL --------- 1 === Maintenant que l'on sait comment récupérer la valeur d'une séquence, et que l'on sait l'incrémenter, voyons quels sont les différents paramètres qui permettent de définir une séquence : Identifiant de la séquence Au sein d'une même base de données, plusieurs objets peuvent porter le même nom, à condition qu'ils soient chacun dans un schéma différent. Aussi, il est possible de spécifier dans quel schéma on souhaite créer la séquence : CREATE SEQUENCE schema_toto.sequence_de_toto; Valeur initiale et incrément

Par défaut, une séquence commence avec la valeur 1, et s'incrémente de 1 en 1 lors de chaque appel de NEXTVAL. Mais on peut tout à fait spécifier ses propres paramètres : CREATE SEQUENCE ma_sequence START WITH 5 INCREMENT BY 3;Dans cet exemple, on a défini la suite 5, 8, 11, 14, 17, 20...
Les paramètres START WITH et INCREMENT BY peuvent s'utiliser indépendamment. Pour faire une suite descendante, il suffit d'indiquer une valeur négative au paramètre INCREMENT BY : CREATE SEQUENCE ma_sequence INCREMENT BY -10; Valeur maxi et valeur mini Implicitement (par défaut), Oracle a créé notre première séquence avec les paramètres suivants (entre autres) : CREATE SEQUENCE ma_sequence NOMAXVALUE NOMINVALUE; Si on le souhaite, on peut fixer un plafond (pour une séquence ascendante) ou un plancher (pour une suite descendante) : CREATE SEQUENCE ma_sequence START WITH 1 INCREMENT BY 1 MAXVALUE 9999; CREATE SEQUENCE ma_sequence START WITH -1 INCREMENT BY -1 MINVALUE -9999;

Reboucler la séquence Lorsque la séquence atteint sa valeur maxi (resp. mini), on peut lui demander de s'arrêter (Oracle retournera une erreur lors de la prochaine tentative d'utilisation de NEXTVAL), ou de reprendre à sa valeur mini (resp. maxi) et reprendre son compte. Cette séquence comptera jusqu'à 10 puis retournera une erreur au NEXTVAL suivant : CREATE SEQUENCE ma_sequence START WITH 1 MINVALUE -10 MAXVALUE 10 NOCYCLE; Cette séquence comptera de 1 à 10, puis de -10 à 10, puis de -10 à 10... : CREATE SEQUENCE ma_sequence START WITH 1 MINVALUE -10 MAXVALUE 10 CYCLE; Par défaut, une séquence ne reboucle pas (cas n°1)

Mettre les valeurs en mémoire cache Afin d'optimiser l'utilisation des séquences, on peut demander à Oracle de placer en mémoire cache un certain nombre de valeurs de la séquence : CREATE SEQUENCE ma_sequence CACHE 100; Par défaut, Oracle maintient 20 valeurs en mémoire cache. La mise en cache est-elle importante ? Oui, elle peut avoir un effet significatif sur les performances. On peut mettre un nombre de valeurs élevé en mémoire cache. Forcer le respect de l'ordre de création Ce paramètre ne concerne que les serveurs fonctionnant en mode parallèle. Pour vérifier ceci, exécuter l'ordre SQL suivant : SELECT name, value FROM v$parameter WHERE name = 'parallel_server'; Si vous êtes en mode parallèle (TRUE), lorsque plusieurs NEXTVAL sont exécutés simultanément, ils ne sont pas toujours traités dans le bon ordre. Aussi, il faut activer l'option ORDER de la façon suivante : CREATE SEQUENCE ma_sequence ORDER;

Modifier une séquence Dans les exemples précédents, nous avons vu comment créer une séquence en spécifiant des attributs qui définissent son comportement. Ces attributs sont modifiables après création de la séquence. Il suffit d'utiliser l'ordre SQL ALTER SEQUENCE. Voici un exemple d'enchaînement d'ordres SQL : ===

SQL> create sequence ma_sequence start with 1 minvalue 0; Séquence créée. SQL> select ma_sequence.nextval from dual; NEXTVAL --------- 1
SQL> select 'La valeur courante est ' || ma_sequence.currval from dual; 'LAVALEURCOURANTEEST'||MA_SEQUENCE.CURRVAL --------------------------------------------------------------- La valeur courante est 1 SQL> alter sequence ma_sequence increment by 20; Séquence modifiée. SQL> select ma_sequence.nextval from dual; NEXTVAL --------- 21 SQL> select ma_sequence.nextval + ma_sequence.nextval from dual; MA_SEQUENCE.NEXTVAL+MA_SEQUENCE.NEXTVAL --------------------------------------- 82 SQL> alter sequence ma_sequence increment by -41 maxvalue 100 cycle nocache; Séquence modifiée. SQL> select ma_sequence.nextval from dual; NEXTVAL --------- 0 SQL> select ma_sequence.nextval from dual; NEXTVAL --------- 100 SQL> select ma_sequence.nextval from dual; NEXTVAL --------- 59 ===

Avez-vous remarqué que lors de l'exécution de "select ma_sequence.nextval + ma_sequence.nextval from dual;", une seule et même valeur de la séquence est utilisée ? Oracle considère NEXTVAL comme une pseudo-colonne, et par conséquent dans cet ordre SQL, il ne la "prend" qu'une seule fois au niveau de la base, la deuxième fois il reprend la valeur chargée en mémoire. Modifier une séquence

Une question qui revient souvent sur les forums Oracle est la suivante : "Comment faire pour créer une colonne de table auto-incrémentée, dans le but d'en faire une clé primaire ?". En effet, Oracle ne dispose pas de l'option auto_increment que l'on rencontre dans certains SGBD (dont MySQL). Le principe est le suivant :
" créer une séquence qui permettra de générer des valeurs entières uniques " créer un TRIGGER qui se déclenchera à chaque INSERT, pour alimenter le champ voulu avec une valeur unique. Voici un exemple de trigger : === create trigger t_matable_pk before insert on matable for each row begin select seq_matable_pk.nextval into :new.x from dual; end; === ... où "x" est le nom du champ à auto incrémenter. Cet exemple ne gère pas le contrôle d'unicité de la valeur que l'on va insérer, mais si le champ n'est alimenté QUE par l'utilisation de la séquence qui lui est dédiée, et si cette séquence n'est pas paramétrée pour reboucler, il n'y a pas de raison qu'une erreur de clé en double surgisse... Une séquence sans trou ? Etant donné qu'une séquence peut être interrogée à tout moment par tout utilisateur Oracle ayant les droits suffisants, IL NE FAUT PAS considérer les séquences comme un moyen de générer une suite de nombres sans "trous". Exemple : dans le cas de l'alimentation d'une clé primaire, si un enregistrement a été inséré, puis si la transaction a subi un "rollback", alors la séquence ne revient pas en arrière, et lors de l'insertion suivante, on aura l'impression que la séquence a sauté un ou plusieurs nombres. Donc : une séquence fournit un moyen d'obtenir des valeurs uniques, mais pas forcément continues.

Bibliographie
" Documentation Oracle : OraDoc " Schéma de la syntaxe de CREATE SEQUENCE : Syntaxe SQL Cet article se base sur des tests et recherches réalisés dans la version 8.1.5 d'Oracle.

Introduction

Tout au long de cet article, nous allons nous intéresser à une structure de données permettant de créer un Forum de discussion, matérialisé par la table suivante : ===
SQL> create table messages ( 2 id number, 3 id_parent number, 4 titre varchar2(50), 5 auteur varchar2(30), 6 dateheure date, 7 texte long); Table créée. SQL> alter table messages add primary key (id); Table modifiée. SQL> alter table messages modify dateheure default sysdate; Table modifiée. === La colonne id_parent contient NULL si le message est le premier message d'un fil de discussion, sinon elle contient l'id du message auquel le message répond. La colonne dateheure a pour valeur par défaut la date système du serveur de base de données. On pourra donc se permettre de l'ignorer lors de l'insertion des messages. Nous n'allons pas nous attarder sur l'enregistrement des messages dans la table (ordre SQL "insert"). Nous n'allons nous intéresser qu'à la façon dont les données sont lues dans la table, donc aux ordres SQL "select" qu'il faudra exécuter pour obtenir les résultats escomptés. Voici un exemple de contenu de la table messages : insert into messages (id, id_parent, titre, auteur, texte) values (1, NULL, 'Combien d''oeufs dans la pate à crêpes ?', 'John', 'Quelqu''un sait-il combien il faut d''oeufs ?'); insert into messages (id, id_parent, titre, auteur, texte) values (2, NULL, 'Sondage : votre marque de lait préférée', 'John', 'Dites-moi quelle est votre marque de lait préférée ?'); insert into messages (id, id_parent, titre, auteur, texte) values (3,1, 'Re : Combien d''oeufs dans la pate à crêpes ?', 'Martine', 'Je crois que c''est 2 par personne non ?'); insert into messages (id, id_parent, titre, auteur, texte) values (4,3, 'C''est sûrement ça', 'John', 'Tu dois avoir raison, merci !'); insert into messages (id, id_parent, titre, auteur, texte) values (5,2, 'Re : Sondage : votre marque de lait préférée', 'Fifi', 'Moi c''est Lait Blanc'); insert into messages (id, id_parent, titre, auteur, texte) values (6,2, 'Re : Sondage : votre marque de lait préférée', 'Petrus', 'SuperMilk'); insert into messages (id, id_parent, titre, auteur, texte) values (7,4, 'Re : C''est sûrement ça', 'Martine', 'De rien ;o)'); insert into messages (id, id_parent, titre, auteur, texte) values (8,2, 'Re : Sondage : votre marque de lait préférée', 'Fifi', 'Au fait pourquoi ce sondage ?'); insert into messages (id, id_parent, titre, auteur, texte) values (9,2, 'Re : Sondage : votre marque de lait préférée', 'Enrico', 'Le lait de chèvre'); insert into messages (id, id_parent, titre, auteur, texte) values (10,8, 'Re : Sondage : votre marque de lait préférée', 'John', 'Je suis statisticien de formation et j''ai rien d''autre à faire'); insert into messages (id, id_parent, titre, auteur, texte) values (11, NULL, 'Qui a vu le match de volley hier soir ?', 'Petrus', 'Si qq''un connaît le score, merci de nous le donner...'); insert into messages (id, id_parent, titre, auteur, texte) values (12,10, 'Re : Sondage : votre marque de lait préférée', 'Fifi', 'Ok'); insert into messages (id, id_parent, titre, auteur, texte) values (13,3, 'Re : Combien d''oeufs dans la pate à crêpes ?', 'Fifi', 'Oui c''est 2 par personne'); Ce qui nous donne le contenu suivant (par ordre chronologique de création des messages) :
ID ID_P TITRE AUTEUR HEURE ------ --------------------------------------------- ------------------ 1 Combien d'oeufs dans la pate à crêpes ? John 16:58:20 2 Sondage : votre marque de lait préférée John 16:58:37 3 1 Re : Combien d'oeufs dans la pate à crêpes ? Martine 16:58:51 4 3 C'est sûrement ça John 16:59:00 5 2 Re : Sondage : votre marque de lait préférée Fifi 16:59:08 6 2 Re : Sondage : votre marque de lait préférée Petrus 16:59:14 7 4 Re : C'est sûrement ça Martine 16:59:20 8 2 Re : Sondage : votre marque de lait préférée Fifi 16:59:26 9 2 Re : Sondage : votre marque de lait préférée Enrico 16:59:31 10 8 Re : Sondage : votre marque de lait préférée John 16:59:45 11 Qui a vu le match de volley hier soir ? Petrus 16:59:51 12 10 Re : Sondage : votre marque de lait préférée Fifi 16:59:59 13 3 Re : Combien d'oeufs dans la pate à crêpes ? Fifi 17:00:03

Ainsi : - les deux premiers messages sont des débuts de fil de discussion - le message ID=3 répond au message ID=1 car son ID_PARENT=1 - le message ID=13 répond au message ID=3 car son ID_PARENT=3 - etc...
Notre but est donc d'obtenir une hiérarchie de la forme :
ID=1 |-ID=3 | |-ID=4 | | |-ID=7 | |-ID=13 ID=2 |-ID=5 |-ID=6 |-ID=8 | |-ID=10 | | |-ID=12 |-ID=9 ID=11

Le lien entre un message et son parent est bien sûr fait avec les colonnes ID et ID_PARENT. Voici la syntaxe de l'ordre SQL permettant de récupérer la hiérarchie souhaitée : ===
SQL> select id, id_parent, titre 2 from messages 3 start with id_parent is null 4 connect by id_parent = prior id; ID ID_PARENT TITRE --------- --------- -------------------------------------------------- 1 Combien d'oeufs dans la pate à crêpes ? 3 1 Re : Combien d'oeufs dans la pate à crêpes ? 4 3 C'est sûrement ça 7 4 Re : C'est sûrement ça 13 3 Re : Combien d'oeufs dans la pate à crêpes ? 2 Sondage : votre marque de lait préférée 5 2 Re : Sondage : votre marque de lait préférée 6 2 Re : Sondage : votre marque de lait préférée 8 2 Re : Sondage : votre marque de lait préférée 10 8 Re : Sondage : votre marque de lait préférée 12 10 Re : Sondage : votre marque de lait préférée 9 2 Re : Sondage : votre marque de lait préférée 11 Qui a vu le match de volley hier soir ? 13 ligne(s) sélectionnée(s).

=== Notre objectif est atteint : nous avons les messages dans le bon ordre. Une requête hiérarchique permet donc de trier des enregistrements en utilisant des notions d'arborescence inter-enregistrement. La pseudo-colonne Level Dans une hiérarchie, on peut avoir un élément A qui a pour enfant un élément B, qui lui-même a pour enfant un élément C, etc... On peut alors dire que l'élément C est au niveau n+2 par rapport au niveau n de l'élément A. Afin de mieux voir la profondeur de hiérarchie, nous allons utiliser la pseudo-colonne LEVEL, qui permet de savoir à quel niveau hiérarchique on se trouve : ===
SQL> select level, titre, auteur 2 from messages 3 start with id_parent is null 4 connect by id_parent = prior id; LEVEL TITRE AUTEUR --------- -------------------------------------------------- --------- 1 Combien d'oeufs dans la pate à crêpes ? John 2 Re : Combien d'oeufs dans la pate à crêpes ? Martine 3 C'est sûrement ça John 4 Re : C'est sûrement ça Martine 3 Re : Combien d'oeufs dans la pate à crêpes ? Fifi 1 Sondage : votre marque de lait préférée John 2 Re : Sondage : votre marque de lait préférée Fifi 2 Re : Sondage : votre marque de lait préférée Petrus 2 Re : Sondage : votre marque de lait préférée Fifi 3 Re : Sondage : votre marque de lait préférée John 4 Re : Sondage : votre marque de lait préférée Fifi 2 Re : Sondage : votre marque de lait préférée Enrico 1 Qui a vu le match de volley hier soir ? Petrus 13 ligne(s) sélectionnée(s). ===

Pour plus de clarté, nous allons faire précéder chaque titre d'un nombre d'espaces égal au level qui lui correspond. Cela génèrera une indentation du plus bel effet ! ===
SQL> 1 select id, id_parent, rpad(' ', level-1) || titre as titre, auteur 2 from messages 3 start with id_parent is null 4 connect by id_parent = prior id;

ID ID_PARENT TITRE AUTEUR --- --------- ------------------------------------------------------ ---------- 1 Combien d'oeufs dans la pate à crêpes ? John 3 1 Re : Combien d'oeufs dans la pate à crêpes ? Martine 4 3 C'est sûrement ça John 7 4 Re : C'est sûrement ça Martine 13 3 Re : Combien d'oeufs dans la pate à crêpes ? Fifi 2 Sondage : votre marque de lait préférée John 5 2 Re : Sondage : votre marque de lait préférée Fifi 6 2 Re : Sondage : votre marque de lait préférée Petrus 8 2 Re : Sondage : votre marque de lait préférée Fifi 10 8 Re : Sondage : votre marque de lait préférée John 12 10 Re : Sondage : votre marque de lait préférée Fifi 9 2 Re : Sondage : votre marque de lait préférée Enrico 11 Qui a vu le match de volley hier soir ? Petrus 13 ligne(s) sélectionnée(s). ===

Nous venons donc de voir à quoi peut servir une requête hiérarchique. Parmis les applications possible de cet outil puissant : - les forums avec fil de discussion (notre exemple) - hiérarchie d'un groupe de personnes (chefs de service - employés) - gestion de catégories et sous-catégories pour organiser des informations - ... Quelques précisions sur la syntaxe des requêtes hiérarchiques La clause START WITH permet d'indiquer la condition qui détermine quels enregistrement sont au niveau 1 (aucun enregistrement au-dessus d'eux dans la hiérarchie). Si on n'avait voulu lister que les fils de discussion initiés par John, avec leurs réponses, voici la requête qu'il aurait fallu exécuter : ===
SQL>
1 select id, id_parent, rpad(' ', level-1) || titre as titre, auteur 2 from messages 3 start with id_parent is null and auteur = 'John' 4* connect by id_parent = prior id ID ID_PARENT TITRE AUTEUR --- --------- ------------------------------------------------------ ---------- 1 Combien d'oeufs dans la pate à crêpes ? John 3 1 Re : Combien d'oeufs dans la pate à crêpes ? Martine 4 3 C'est sûrement ça John 7 4 Re : C'est sûrement ça Martine 13 3 Re : Combien d'oeufs dans la pate à crêpes ? Fifi 2 Sondage : votre marque de lait préférée John 5 2 Re : Sondage : votre marque de lait préférée Fifi 6 2 Re : Sondage : votre marque de lait préférée Petrus 8 2 Re : Sondage : votre marque de lait préférée Fifi 10 8 Re : Sondage : votre marque de lait préférée John 12 10 Re : Sondage : votre marque de lait préférée Fifi 9 2 Re : Sondage : votre marque de lait préférée Enrico 12 ligne(s) sélectionnée(s). === Le 13ème et dernier message n'a pas été sélectionné car il ne fait pas partie d'un fil de discussion initié par John.

Les requêtes hiérarchiques sont donc non seulement un moyen de trier les enregistrements, mais aussi un moyen de sélectionner des enregistrements (même sans utilisation de la clause where). La clause CONNECT BY permet d'indiquer la condition qui lie un enregistrement à son enregistrement père. "connect by id_parent = prior id" doit se lire "la relation père-fils entre deux enregistrements est définie par le fait que la colonne id_parent de l'enregistrement fils est égale à la colonne id de l'enregistrement père" (c'est le mot clé PRIOR qui indique que l'on fait référence au père).