Oracle PDF
Oracle PDF
Oracle PDF
Objectifs
Ce chapitre présente l'architecture du serveur Oracle via l'étude des structures physiques, des
structures mémoire, des processus et des structures logiques qui entrent en jeu dans
l'établissement d'une connexion à une base de données, la création d'une session et l'exécution
de commandes SQL.
Un serveur Oracle :
Serveur Oracle
• est un système de gestion de
base de données qui offre
une méthode de gestion des
informations ouverte,
complète et intégrée,
• est constitué d'une instance
et d'une base de données
Oracle.
Serveur Oracle
Le serveur de bases de données est primordial pour la gestion des informations. En général, il
doit gérer de façon fiable dans un environnement multiutilisateur une quantité importante de
données pour que de nombreux utilisateurs puissent y accéder simultanément, et ce sans
affecter les performances. Ce type de serveur doit également empêcher tout accès non autorisé
et proposer des solutions efficaces de récupération des données après incident.
Instance Oracle
Une instance Oracle est constituée de la mémoire SGA et des processus d'arrière-plan utilisés
pour gérer une base de données. Une instance est identifiée à l'aide de méthodes propres à
chaque système d'exploitation. Elle ne peut ouvrir et utiliser qu'une seule base à la fois.
Processus
Connexion serveur
établie Session crée
Processus Serveur Oracle
utilisateur
Structure physique
La structure physique d'une base de données Oracle comprend trois types de fichier : fichiers
de contrôle, fichiers de données et fichiers de journalisation.
Mémoire SGA
La mémoire SGA est également appelée Shared Global Area. Elle stocke les informations de
la base qui sont partagées par les processus de base de données. Elle contient les données et les
informations de contrôle utilisées par le serveur Oracle. Enfin, elle est allouée dans la mémoire
virtuelle de l'ordinateur sur lequel est installé le serveur Oracle.
L'instruction suivante permet d'afficher les allocations de la mémoire SGA :
SQL> SHOW SGA:
Total System Global Area 36437964 bytes
Fixed Size 6543794 bytes
Variable Size 19521536 bytes
Database Buffers 16777216 bytes
Redo Buffers 73728 bytes
• Dynamique
• Taille définie à l'aide du paramètre SGA_MAX_SIZE
• Allocation et suivi sous forme de granules par les
composants de la mémoire SGA
– Allocation de mémoire virtuelle contiguë
– Taille des granules définie en fonction de la valeur
totale estimée de SGA_MAX_SIZE
Cache "library"
La taille du cache "library" dépend du dimensionnement défini pour la zone de mémoire
partagée. La mémoire est allouée lors de l'analyse d'une instruction ou de l'appel d'un
programme. Si la taille de la zone de mémoire partagée est trop restreinte, les instructions sont
sans cesse rechargées dans le cache "library" au détriment des performances. Le cache
"library" est géré par un algorithme LRU (Least Recently Used). A mesure que le cache se
remplit, les chemins d'exécution et les arborescences d'analyse (parse) les plus anciens sont
supprimés du cache "library" afin de libérer de l'espace pour les nouvelles entrées. Si les
instructions SQL ou PL/SQL ne sont pas réutilisées, elles sont finalement retirées de la
mémoire (sur la base de la liste LRU).
Le cache "library" est composé de deux structures :
• Zone SQL partagée : La zone SQL partagée stocke et partage le plan d'exécution et
l'arborescence de l'analyse des instructions SQL exécutées dans la base de données.
Lorsqu'une instruction SQL identique est exécutée pour la deuxième fois, elle peut
utiliser les informations d'analyse disponibles dans la zone SQL partagée pour accélérer
son exécution. Afin que les instructions SQL utilisent une zone de mémoire SQL
partagée lorsque cela s'avère possible, il est nécessaire que le texte, le schéma et les
variables attachées (bind variables) soient en tous points identiques.
• Zone PL/SQL partagée : La zone PL/SQL partagée stocke et partage les dernières
instructions PL/SQL exécutées. Les procédures et les programmes analysés et compilés
(fonctions, déclencheurs (triggers) et packages) sont stockés dans cette zone.
Tampon de journalisation
Le tampon de journalisation est un tampon circulaire qui contient les modifications apportées
aux blocs de fichiers de données. Ces informations sont stockées dans des entrées de
journalisation. Ces entrées contiennent les informations nécessaires à la recréation des données
avant toute modification via les opérations INSERT, UPDATE, DELETE, CREATE,
ALTER ou DROP.
Dimensionner le tampon de journalisation
La taille du tampon de journalisation est définie par le paramètre d'initialisation
LOG_BUFFER.
Remarque : Le dimensionnement du cache du tampon de journalisation est présenté en détail
dans le cours Oracle9i Tuning. Pour plus d'informations sur les fichiers de journalisation,
reportez-vous au chapitre Gérer les fichiers de journalisation.
Mémoire PGA
La mémoire PGA (Program Global Area ou Process Global Area) est une région de la mémoire
qui contient les données et les informations de contrôle d'un seul processus serveur ou d'un seul
processus d'arrière-plan. Elle est allouée lors de la création d'un processus et libérée à la fin de
celui-ci. Contrairement à la mémoire SGA, qui est partagée par plusieurs processus, la mémoire
PGA n'est utilisée que par un seul processus.
Contenu de la mémoire PGA
Le contenu de la mémoire PGA varie selon que l'instance est exécutée dans une configuration
serveur dédié ou serveur partagé. Elle inclut généralement les composants suivants :
• Zone privée de partage des ordres SQL : Contient des données telles que des informations
attachées et des structures mémoire d'exécution. Chaque session qui émet une instruction
SQL possède une zone privée de partage des ordres SQL. La zone de chaque utilisateur qui
soumet la même instruction SQL utilise une seule zone SQL partagée. La zone privée de
partage des ordres SQL d'un curseur est divisée en deux sous-zones :
– La zone persistante, qui contient des informations attachées. Elle n'est libérée que
lorsque le curseur est fermé.
– La zone d'exécution, qui est créée au tout début d'une demande d'exécution. En ce qui
concerne les commandes INSERT, UPDATE et DELETE, cette zone n'est libérée que
lorsque l'instruction a été exécutée. En ce qui concerne les interrogations, elle n'est
libérée que lorsque toutes les lignes sont extraites ou lorsque l'interrogation est
annulée.
Processus utilisateur
Un utilisateur de base de données qui doit demander des informations contenues dans la base
doit d'abord établir une connexion avec le serveur Oracle. La demande de connexion s'effectue
via un outil d'interface de base de données (SQL*Plus, par exemple) et le lancement du
processus utilisateur. Ce dernier n'entre pas directement en interaction avec le serveur Oracle.
Il génère des appels via l'interface UPI (User Program Interface) qui crée une session et
démarre un processus serveur
Processus serveur
Lorsque l'utilisateur a établi une connexion, un processus serveur démarre pour traiter les
demandes du processus utilisateur. Un processus serveur peut être dédié ou partagé. Dans un
environnement serveur dédié, le processus serveur traite la demande d'un seul processus
utilisateur. Le processus serveur prend fin lorsque le processus utilisateur se déconnecte. Dans
un environnement serveur partagé, le processus serveur traite les demandes de plusieurs
processus utilisateur. Le processus serveur communique avec le serveur Oracle à l'aide de
l'interface OPI (Oracle Program Interface).
Remarque : L'allocation du processus serveur dans un environnement serveur dédié par
rapport à un environnement serveur partagé est présentée en détail dans le cours Oracle9i
Tuning.
Processus d'arrière-plan
L'architecture Oracle possède cinq processus d'arrière-plan obligatoires qui seront présentés
plus loin dans ce chapitre. Oracle possède, en outre, un certain nombre de processus d'arrière-
plan facultatifs qui sont démarrés via l'exécution de l'option correspondante. Ces processus ne
sont pas décrits dans ce chapitre, à l'exception du processus d'arrière-plan ARCn. La liste
suivante contient une partie des processus d'arrière-plan facultatifs :
• RECO : Processus de récupération
• QMNn : Advanced Queuing
• ARCn : Processus d'archivage
• LCKn : RAC Lock Manager–Verrous d'instance
• LMON : RAC DLM Monitor–Verrous globaux
• LMDn : RAC DLM Monitor–Verrous à distance
• CJQ0 : Processus d'arrière-plan Coordinator Job Queue
• Dnnn : Répartiteur (Dispatcher)
• Snnn : Serveur partagé
• Pnnn : Processus esclave "Parallel Query"
Processus d'archivage
Tous les autres processus d'arrière-plan sont facultatifs, selon la configuration de la base de
données. Toutefois, l'un d'entre eux, ARCn, joue un rôle essentiel dans la récupération d'une
base suite à une défaillance du disque. Le serveur Oracle passe d'un fichier de journalisation
en ligne au suivant à mesure que ceux-ci se remplissent. Le passage d'un fichier de
journalisation à un autre est un changement de fichier de journalisation. Le processus ARCn
lance la sauvegarde ou l'archivage du groupe de fichiers de journalisation remplis à chaque
changement de fichier de journalisation. Il archive automatiquement le fichier de
journalisation en ligne pour qu'il puisse être réutilisé. Ainsi, toutes les modifications apportées
à la base sont conservées. Ceci permet au DBA de récupérer la base de données jusqu'au point
de panne, même si un disque est endommagé.
Archiver les fichiers de journalisation
L'une des principales décisions de l'administrateur consiste à déterminer s'il doit configurer la
base de données pour un fonctionnement en mode ARCHIVELOG ou en mode
NOARCHIVELOG.
Mode NOARCHIVELOG : Dans ce mode, les fichiers de journalisation en ligne sont écrasés à
chaque changement de fichier de journalisation. Le processus LGWR n'écrase pas un groupe
de fichiers de journalisation tant que le point de reprise du groupe n'est pas terminé. Ceci
garantit la récupération des données validées en cas d'échec d'une instance. Dans ce cas, seules
les données de la mémoire SGA sont perdues (aucune donnée des disques n'est perdue). Une
défaillance du système d'exploitation, par exemple, provoque la défaillance de l'instance.
Tablespace
Fichier de données
Segment Segment
Extent Blocs
Structure logique
La hiérarchie de la structure logique se présente comme suit :
• Une base de données Oracle contient au moins un tablespace.
• Un tablespace comporte un ou plusieurs segments.
• Un segment est composé d'extents.
• Un extent est composé de blocs logiques.
• Un bloc représente la plus petite unité sur laquelle portent les opérations de lecture et
d'écriture.
L'architecture d'une base de données Oracle contient les structures logiques et physiques qui
constituent la base de données.
• La structure physique comprend les fichiers de contrôle, les fichiers de journalisation en
ligne (online redo log) et les fichiers de données qui constituent la base de données.
• La structure logique est composée de tablespaces, de segments, d'extents (ensemble de
blocs contigus) et de blocs de données.
Le serveur Oracle permet un contrôle précis de l'utilisation de l'espace disque à l'aide de
tablespaces et de structures de stockage logiques constituées de segments, d'extents et de blocs
de données.
Outil Description
Oracle Universal Permet d'installer, de mettre à niveau ou de
Installer (OUI) supprimer des composants logiciels.
Oracle Database Outil doté d'une interface graphique qui entre en
Configuration interaction avec Oracle Universal Installer ou qui
Assistant peut être utilisé indépendamment afin de créer,
supprimer ou modifier une base de données.
SQL*Plus Utilitaire permettant d'accéder à des données
d'une base Oracle
Oracle Enterprise Interface utilisateur permettant d'administrer, de
Manager surveiller et de régler une ou plusieurs bases de
données
Installation interactive
UNIX :
Le programme d'installation, nommé runInstaller, se trouve dans le répertoire
INSTALL\install\solaris.
Sous Unix, n'exécutez pas l'installeur en tant qu'utilisateur root.
NT :
Le programme d'installation, nommé setup.exe, se trouve dans le répertoire
install/win32.
Remarque : Pour plus d'informations sur l'installation d'Oracle Server sur votre plate-forme,
consultez la documentation Oracle propre à votre système d'exploitation.
Où :
• FILENAME identifie le fichier de réponses.
• SILENT exécute Oracle Universal Installer en mode automatique.
• NOWELCOME n'affiche pas de fenêtre de bienvenue. Si vous utilisez SILENT, ce
paramètre n'est pas nécessaire.
Exemple de fichier de réponses pour Unix :
[General]
RESPONSEFILE_VERSION=1.7.0
[Session]
UNIX_GROUP_NAME="dba"
FROM_LOCATION="/u01/stage/products.jar"
ORACLE_HOME="/u01/app/oracle/ora9i"
ORACLE_HOME_NAME="Ora9i"
TOPLEVEL_COMPONENT={"oracle.server", "9.0.1.1.1"}
SHOW_COMPONENT_LOCATIONS_PAGE=false
SHOW_SUMMARY_PAGE=false
SHOW_INSTALL_PROGRESS_PAGE=false
SHOW_REQUIRED_CONFIG_TOOL_PAGE=false
SHOW_OPTIONAL_CONFIG_TOOL_PAGE=false
SHOW_END_SESSION_PAGE=false
NEXT_SESSION=true
SHOW_SPLASH_SCREEN=true
SHOW_WELCOME_PAGE=false
SHOW_ROOTSH_CONFIRMATION=true
SHOW_EXIT_CONFIRMATION=true
INSTALL_TYPE="Typical”
s_GlobalDBName="u01.us.oracle.com"
s_mountPoint="/u01/app/oracle/ora9i/dbs"
s_dbSid="db09"
b_createDB=true
SQL*Plus
SQL*Plus est l'outil de ligne de commande d'Oracle qui permet d'exécuter la suite de
commandes SQL (Structured Query Language) standard. SQL est un langage fonctionnel qui
permet aux utilisateurs de communiquer avec Oracle pour ajouter, mettre à jour ou modifier
des données dans la base, ou en extraire des données.
Oracle Management
Server Agent
Oracle
server
• Point de
lancement
central
• Peut être
exécutée
sur client
léger ou
client lourd
• Peut être
lancée en
mode
autonome
ou via un
OMS
Fonctionnalités de la console
La console fournit une interface utilisateur aux administrateurs et un point de lancement
central pour l'ensemble des outils et des applications de gestion. SQL*Plus Worksheet peut
être lancé depuis la console.
Il est possible d'exécuter la console en mode léger via le Web ou en tant que client lourd. Les
clients légers utilisent un navigateur Web pour se connecter à un serveur sur lequel sont
installés des fichiers de console. Pour les clients lourds, les fichiers doivent être installés
localement.
Il est possible de lancer la console en mode autonome ou en se connectant à Oracle
Management Server.
Remarque : Ce cours n'est pas destiné à présenter en détail Oracle Enterprise Manager, la
console ou Oracle Management Server. Pour plus d'informations sur l'utilisation d'Oracle
Enterprise Manager, voir le cours Oracle Enterprise Manager 9i.
Instance Oracle
Zone de mémoire Mémoire SGA
partagée
Cache Cache Tampon de
de tampons journali-
"library" de la base
de données sation
Cache
du dictionnaire Zone de Zone de mémoire
de données mémoire Java LARGE POOL
spfiledb01.ora
CONNECT / AS SYSDBA
STARTUP
PFILE
PFILE est un fichier texte que vous pouvez mettre à jour à l'aide d'un éditeur standard du
système d'exploitation. Ce fichier est en lecture seule pendant le démarrage de l'instance. S'il
est modifié, l'instance doit être interrompue et redémarrée pour que les nouvelles valeurs des
paramètres soient effectives.
Par défaut, le fichier PFILE se trouve dans le répertoire $ORACLE_HOME/dbs sous le nom
initSID.ora.
cp init.ora $ORACLE_HOME/dbs/initdba01.ora
Fichier SPFILE
Oracle9i propose un nouveau fichier binaire nommé SPFILE. Ce fichier ne doit pas être
modifié manuellement et doit toujours résider côté serveur. Une fois créé, le fichier est mis à
jour par le serveur Oracle. S'il est modifié manuellement, il devient inutile. Il permet
d'apporter à la base de données des modifications qui seront conservées après l'arrêt et le
redémarrage. En outre, il peut régler lui-même les valeurs des paramètres, qui sont
enregistrées dans le fichier. Il peut bénéficier de la prise en charge de RMAN pour effectuer des
sauvegardes du fichier de paramètres d'initialisation, parce qu'il réside côté serveur. Par défaut,
il se trouve dans le répertoire $ORACLE_HOME/dbs et son nom se présente sous le format
spfileSID.ora.
Paramètre Description
Paramètre Description
IFILE Nom du fichier de paramètres à imbriquer dans
le fichier de paramètres en cours. Il est possible
d'utiliser jusqu'à trois niveaux d'imbrication.
LOG_BUFFER Nombre d'octets alloués au tampon de
journalisation (redo log buffer) dans la mémoire
SGA.
MAX_DUMP_FILE_SIZE Taille maximale des fichiers trace, exprimée en
nombre de blocs du système d'exploitation.
PROCESSES Nombre maximal de processus du système
d'exploitation pouvant se connecter
simultanément à l'instance.
SQL_TRACE Active ou désactive la fonction trace SQL pour
chaque session utilisateur.
TIMED_STATISTICS Active ou désactive le moment du
déclenchement dans les fichiers trace et sur les
écrans de
Oracle9i Database surveillance. I 3-18
Administration
Démarrer une base de données en mode
NOMOUNT
OPEN
STARTUP
MOUNT
NOMOUNT
Instance
démarrée
SHUTDOWN
SHUTDOWN
MOUNT
Fichier de contrôle
ouvert pour cette
NOMOUNT instance
Instance
démarrée
SHUTDOWN
SHUTDOWN
Fichier de contrôle
ouvert pour cette
NOMOUNT instance
Instance
démarrée
SHUTDOWN
SHUTDOWN
STARTUP
STARTUP PFILE=$ORACLE_HOME/dbs/initdb01.ora
Commande STARTUP
Pour démarrer une instance, exécutez la commande suivante :
STARTUP [FORCE] [RESTRICT] [PFILE=filename]
[OPEN [RECOVER][database]
|MOUNT
|NOMOUNT]
(Remarque : Cet exemple n'intègre pas l'ensemble de la syntaxe.)
Où :
• OPEN permet aux utilisateurs d'accéder à la base de données,
• MOUNT monte la base de données pour certaines tâches d'administration, mais ne permet
pas aux utilisateurs d'y accéder,
• NOMOUNT crée la mémoire SGA et lance les processus d'arrière-plan, mais ne permet pas
d'accéder à la base de données,
• PFILE=parfile permet de configurer l'instance à partir d'un fichier contenant des
paramètres qui ne sont pas des paramètres par défaut.
Mode d'arrêt A I T N
Mode d'arrêt :
• A = ABORT
• I = IMMEDIATE
• T = TRANSACTIONAL
• N = NORMAL
Options d'arrêt
Effectuez un arrêt en mode Normal :
Le mode Normal est le mode d'arrêt par défaut. Il s'effectue dans les conditions suivantes :
• Aucune nouvelle connexion ne peut être établie.
• Le serveur Oracle attend la déconnexion préalable de tous les utilisateurs.
• Les tampons de journalisation et de la base de données sont écrits sur disque.
• Les processus d'arrière-plan prennent fin et la zone SGA est supprimée de la mémoire.
• Oracle ferme et démonte la base de données avant d'arrêter l'instance.
• La récupération de l'instance n'est pas nécessaire lors du redémarrage.
Arrêt en mode Transactional
L'arrêt en mode Transactional évite aux clients de perdre leurs travaux en cours. Il s'effectue
dans les conditions suivantes :
• Aucun client ne peut lancer de nouvelle transaction pour l'instance indiquée.
• Le client est déconnecté lorsqu'il termine la transaction en cours.
• La fin de toutes les transactions entraîne l'arrêt immédiat de la base de données.
• La récupération de l'instance n'est pas nécessaire lors du redémarrage.
Options d'arrêt
Arrêt en mode Abort
Si les arrêts en modes Normal et Immediate échouent, vous pouvez abandonner l'instance de
base de données en cours. Cette opération s'effectue dans les conditions suivantes :
• Les instructions SQL en cours de traitement par le serveur Oracle sont immédiatement
interrompues.
• Oracle n'attend pas la déconnexion des utilisateurs de la base de données.
• Les tampons de journalisation et de la base de données ne sont pas écrits sur disque.
• Les transactions non validées ne sont pas annulées.
• L'instance est interrompue sans fermeture des fichiers.
• La base de données n'est pas fermée, ni démontée.
• Une récupération est nécessaire au redémarrage ; elle s'effectue automatiquement.
Remarque : Il n'est pas conseillé de sauvegarder une base de données incohérente.
• Le fichier alertSID.log :
– enregistre les commandes,
– enregistre les résultats des principaux événements,
– conserve quotidiennement des informations
opérationnelles,
– établit le diagnostic des erreurs de la base de
données.
• Chaque entrée est associée à un horodatage.
• Il doit être géré par l'administrateur de base de
données.
• Son emplacement est défini par le paramètre
BACKGROUND_DUMP_DEST.
Fichier d'alertes
Chaque instance Oracle possède un fichier d'alertes. Au besoin, il est créé au démarrage de
l'instance. Vous êtes responsable de la gestion de ce fichier dont la taille augmente lors de
l'utilisation de la base de données. Consultez le fichier d'alertes dès que vous devez
diagnostiquer des opérations quotidiennes ou des erreurs. Ce fichier contient également des
pointeurs sur des fichiers trace qui délivrent des informations plus détaillées.
Le fichier d'alertes conserve un enregistrement des informations suivantes :
• Date/heure d'arrêt ou de démarrage de la base de données
• Liste de tous les paramètres d'initialisation qui ne sont pas des paramètres par défaut
• Démarrage des processus d'arrière-plan
• Thread utilisé par l'instance
• Numéro de séquence du journal utilisé par le processus LGWR (Log Writer)
• Informations relatives à un changement de fichier de journalisation
• Création de tablespaces et de segments d'annulation
• Instructions ALTER émises
• Informations concernant, par exemple, les messages d'erreur de type ORA-600 ou relatifs
aux extents (ensemble de blocs contigus)
• Niveau session :
– A l'aide de la commande ALTER SESSION :
ALTER SESSION SET SQL_TRACE = TRUE
– A l'aide de la procédure SGBD :
dbms_system.SET_SQL_TRACE_IN_SESSION
• Niveau instance :
– A l'aide du paramètre d'initialisation :
SQL_TRACE = TRUE
Présentation de l'exercice 3
Remarque : Cet exercice peut être réalisé avec SQL*Plus ou avec Oracle Enterprise Manager et
SQL*Plus Worksheet.
1 Connectez-vous à la base de données en tant qu' utilisateur SYS, puis arrêtez la base.
2 Une fois la base de données arrêtée, créez un fichier SPFILE à partir d'un fichier
PFILE.
Placez le fichier SPFILE dans le répertoire $HOME/ADMIN/PFILE en lui affectant
un nom au format spfileSID.ora (où SID correspond au nom de votre instance).
Créez le fichier SPFILE à partir du fichier PFILE qui se trouve dans le répertoire
$HOME/ADMIN/PFILE.
3 Affichez le fichier SPFILE depuis le système d'exploitation.
4 Connectez-vous en tant qu'utilisateur SYS, puis démarrez la base de données à l'aide du
fichier SPFILE.
5 a Arrêtez la base de données, puis ouvrez-la en mode lecture seule.
b Connectez-vous en tant qu'utilisateur HR avec le mot de passe HR, puis insérez une
ligne dans la table REGIONS en utilisant la syntaxe suivante :INSERT INTO
regions VALUES (5, ‘Mars’);
Que se passe-t-il ?
c Réaffectez à la base de données le mode lecture-écriture.
6 a Connectez-vous en tant qu'utilisateur HR avec le mot de passe HR, puis insérez la
ligne suivante dans la table REGIONS. Ne validez pas et ne quittez pas la session.
INSERT INTO regions VALUES ( 5, ‘Mars’ );
b Démarrez SQL*Plus dans une nouvelle session Telnet. Connectez-vous en tant
qu'utilisateur SYS AS SYSDBA, puis procédez à un arrêt en mode Transactional.
c Annulez l'insertion dans la session de HR, puis quittez. Que se passe-t-il dans la
session de HR ? Que se passe-t-il dans celle de SYS ?
7 a Connectez-vous en tant qu'utilisateur SYS, puis démarrez la base de données.
b Démarrez une autre session en tant qu'utilisateur HR.
Remarque : Conservez les sessions SQL*Plus de SYS et de HR ouvertes.
c Sous l'ID utilisateur SYS, activez la session en mode restreint.
d Sous l'ID utilisateur HR, effectuez une sélection (SELECT) dans la table REGIONS.
L'opération aboutit-elle ? Quittez la session, puis reconnectez-vous sous l'ID
utilisateur HR. Que se passe-t-il ?
e Sous l'ID utilisateur SYS, désactivez la session en mode restreint.
@cddba01.sql
> CREATE DATABASE dba01;
Elle contient :
• des fichiers de données, de contrôle et de
journalisation,
• l'utilisateur SYS et le mot de passe
change_on_install,
• l'utilisateur SYSTEM et le mot de passe manager,
• des tables internes (mais pas de vues du
dictionnaire de données).
Dictionnaire de données
L'un des composants essentiels d'une base de données Oracle est son dictionnaire de données,
composé d'un jeu de tables et de vues accessibles en lecture seule, fournissant des informations
sur la base de données associée.
Le dictionnaire de données est mis à jour par le serveur Oracle chaque fois qu'une commande
LDD est exécutée. En outre, certaines commandes LMD, telles que celle qui provoque
l'extension d'une table, peuvent mettre à jour le dictionnaire de données.
Le dictionnaire de données est vital pour la base de données Oracle, et constitue aussi une
source importante d'informations pour l'ensemble des utilisateurs, de l'utilisateur final au
développeur d'applications, en passant par l'administrateur de base de données.
Vous pouvez y accéder à l'aide d'instructions SQL. Le dictionnaire de données étant accessible
en lecture seule, vous ne pouvez exécuter que des interrogations sur les tables et les vues qu'il
contient.
• Présentation générale
– DICTIONARY, DICT_COLUMNS
• Objets de schéma
– DBA_TABLES, DBA_INDEXES, DBA_TAB_COLUMNS,
DBA_CONSTRAINTS
• Allocation d'espace
– DBA_SEGMENTS, DBA_EXTENTS
• Structure de base de données
– DBA_TABLESPACES, DBA_DATA_FILES
• Tables virtuelles
• Enregistrent l'activité en cours de la base de
données
• Sont constamment mises à jour lorsque la base de
données est active
• Les informations sont lues à partir de la mémoire et
du fichier de contrôle
• Permettent de surveiller et de régler la base de
données
• Le propriétaire est l'utilisateur SYS
• Les synonymes commencent par le préfixe V$
• Sont répertoriées dans la vue V$FIXED_TABLE
• V$CONTROLFILE
• V$DATABASE
• V$DATAFILE
• V$INSTANCE
• V$PARAMETER
• V$SESSION
• V$SGA
• $SPPARAMETER
• V$TABLESPACE
• V$THREAD
• V$VERSION
Convention Description
Présentation de l'exercice 5
Remarque : Cet exercice peut être réalisé avec SQL*Plus, ou Oracle Enterprise Manager et
SQL*Plus Worksheet.
Fichier de contrôle
Le fichier de contrôle est un petit fichier binaire nécessaire au démarrage et au bon
fonctionnement de la base de données. Chaque fichier de contrôle est associé à une seule base
de données Oracle. Avant l'ouverture d'une base, le fichier de contrôle est lu afin de
déterminer si cette base est opérationnelle.
Ce fichier est constamment mis à jour par le serveur Oracle pendant l'utilisation de la base de
données. Il doit donc être accessible en écriture à chaque ouverture de la base. Seul le serveur
Oracle peut modifier le contenu du fichier de contrôle ; l'administrateur de base de données et
l'utilisateur final ne peuvent pas modifier ce fichier.
Si ce fichier est inaccessible, la base ne fonctionne pas correctement. Si toutes les copies des
fichiers de contrôle d'une base de données sont perdues, cette dernière doit être récupérée pour
être ouverte.
CONTROL_FILES=
$HOME/ORADATA/u01/ctrl01.ctl, $HOME/ORADATA/u02/ctrl02.ctl
ctrl01.ctl ctrl02.ctl
1. Modifiez le SPFILE :
ALTER SYSTEM SET control_files =
'$HOME/ORADATA/u01/ctrl01.ctl',
'$HOME/ORADATA/u02/ctrl02.ctl' SCOPE=SPFILE;
2. Arrêtez la base de données :
shutdown immediate
3. Créez des fichiers de contrôle supplémentaires :
cp $HOME/ORADATA/u01/ctrl01.ctl
$HOME/ORADATA/u02/ctrl02.ctl
4. Démarrez la base de données :
startup
Vous pouvez également utiliser la commande SHOW PARAMETERS pour localiser les fichiers de
contrôle.
SQL> SHOW PARAMETER control_files;
NAME TYPE VALUE
-------------- ----------- ------------------------------
control_files string $HOME/ORADATA/u01/ctrl01.ctl,
$HOME/ORADATA/u02/ctrl02.ctl
Les informations d'un certain nombre de vues dynamiques des performances sont extraites du
fichier de contrôle. Exemples :
• V$BACKUP
• V$DATAFILE
• V$TEMPFILE
• V$TABLESPACE
• V$ARCHIVE
• V$LOG
• V$LOGFILE
• V$LOGHIST
• V$ARCHIVED_LOG
• V$DATABASE
Présentation de l'exercice 6
Remarque : Vous pouvez réaliser cet exercice avec SQL*Plus ou avec Oracle Enterprise
Manager et SQL*Plus Worksheet.
log1a.rdo log1a.rdo
log1b.rdo log1b.rdo
log1c.rdo log2c.rdo
Groupe 1 Groupe 2
Membre Membre
• Définissez le paramètre
DB_CREATE_ONLINE_LOG_DEST_n :
DB_CREATE_ONLINE_LOG_DEST_1
DB_CREATE_ONLINE_LOG_DEST_2
• Vous pouvez ajouter un groupe sans spécification
de fichier :
ALTER DATABASE ADD LOGFILE;
• Supprimer un groupe :
ALTER DATABASE DROP LOGFILE GROUP 3;
Vue V$LOGFILE
Interrogez cette vue pour obtenir le nom de tous les membres d'un groupe.
SQL> SELECT member FROM V$LOGFILE;
MEMBER
-------------------------------------
/u01/home/db03/ORADATA/u03/log02a.rdo
/u01/home/db03/ORADATA/u03/log01a.rdo
Vous pouvez obtenir des informations sur les journaux archivés à partir de la vue
V$INSTANCE.
Remarque : L'archivage est présenté en détail dans le cours Oracle9i DBA II.
Présentation de l'exercice 7
Remarque : Cet exercice peut être réalisé avec SQL*Plus, ou avec Oracle Enterprise Manager
et SQL*Plus Worksheet.
• Le tablespace SYSTEM :
– est créé en même temps que la base de données,
– contient le dictionnaire de données,
– contient le segment d'annulation SYSTEM.
• Le tablespace non SYSTEM
– sépare les segments,
– facilite l'administration de l'espace,
– gère la quantité d'espace allouée aux utilisateurs.
Types de tablespace
L'administrateur de base de données crée des tablespaces pour améliorer le contrôle et faciliter
la gestion de la base. Le serveur Oracle accepte deux types de tablespace : le tablespace
SYSTEM et tous les autres tablespaces.
Le tablespace SYSTEM :
• est créé en même temps que la base de données,
• doit exister dans toutes les bases de données,
• contient le dictionnaire de données, y compris les programmes stockés,
• contient le segment d'annulation SYSTEM,
• ne doit pas contenir de données utilisateur, bien que cela soit possible.
Les tablespaces non SYSTEM :
• facilitent l'administration de la base de données,
• séparent les segments d'annulation, les segments temporaires, les segments de données
d'application et les segments d'index d'application,
• séparent les données en fonction des besoins de sauvegarde,
• séparent les données dynamiques des données statiques,
• gèrent la quantité d'espace allouée aux objets utilisateur.
Tablespace d'annulation
Un tablespace d'annulation est utilisé dans la gestion automatique des annulations (AUM).
Pour plus d'informations, voir le chapitre "Gérer les données d'annulation".
CREATE UNDO TABLESPACE tablespace
[DATAFILE clause]
Tablespaces TEMPORARY
Vous pouvez gérer plus efficacement l'espace pour les opérations de tri en définissant des
tablespaces TEMPORARY réservés exclusivement aux segments de tri. Aucun objet de
schéma permanent ne peut résider dans un tablespace TEMPORARY.
Un segment de tri ou un segment temporaire est utilisé lorsqu'un segment est partagé par
plusieurs opérations de tri. Les tablespaces TEMPORARY améliorent les performances
lorsque plusieurs tris ne peuvent tenir dans la mémoire. Le segment de tri d'un tablespace
TEMPORARY donné est créé lors de la première opération de tri dans l'instance. La taille du
segment de tri augmente par allocation d'extents jusqu'à ce qu'elle soit égale ou supérieure au
nombre total des demandes de stockage de tous les tris en cours exécutés dans l'instance.
Redimensionner un tablespace
Vous pouvez augmenter la taille d'un tablespace de deux manières :
• en modifiant automatiquement ou manuellement la taille d'un fichier de données,
• en ajoutant un fichier de données au tablespace.
• ALTER DATABASE
– La base de données doit être montée.
– Le fichier de données cible doit exister.
Présentation de l'exercice 8
Remarque : Cet exercice peut être réalisé avec SQL*Plus ou avec Oracle Enterprise Manager et
SQL*Plus Worksheet.
Base de données
PROD
TABLESPACES
SYSTEM USER_DATA RBS TEMP
FICHIERS DE DONNEES
DISK2/ DISK3/ DISK1/ DISK1/
DISK1/SYS1.dbf USER1.dbf USER2.dbf ROLL1.dbf TEMP.dbf
SEGMENTS S_DEPT S_EMP S_DEPT S_EMP RBS1 RBS2 RBS1 RBS2
Temp
(suite) FIRST_N (suite) (suite)
Table Index AME
EXTENTS
1 2 1 2 1 2 1 1 2 2 1 DISPO 1 1 2 2 1
Table Partition
de table
Cluster Index
Types de segment
Les segments sont des objets qui occupent de l'espace dans une base de données. Ils utilisent
de l'espace dans les fichiers de données d'une base. Cette section décrit les différents types de
segment.
Table :
Les données d'une base sont généralement stockées dans des tables. Un segment de table
stocke les données d'une table qui n'est ni incluse dans un cluster, ni partitionnée. Les données
d'un segment de table ne sont pas stockées dans un ordre spécifique et l'administrateur de la
base de données a un contrôle très limité sur l'emplacement des lignes dans les blocs d'une
table. Toutes les données d'un segment de table doivent être stockées dans un seul tablespace.
Partition de table :
L'évolutivité et la disponibilité sont des points très importants à prendre en compte lorsqu'une
table de base de données est utilisée simultanément par de nombreux utilisateurs. Dans ce cas,
les données de la table peuvent être stockées dans plusieurs partitions, chacune se trouvant
dans un tablespace différent. Le serveur Oracle prend actuellement en charge le
partitionnement à l'aide d'une série de valeurs de clé, d'un algorithme de hachage et d'une liste
de valeurs. Lorsqu'une table est partitionnée, chacune de ses partitions constitue un segment
qui peut être contrôlé de manière indépendante à l'aide des paramètres de stockage.
L'utilisation de ce type de segment requiert l'option de partitionnement d'Oracle9i Enterprise
Edition.
Segment Segment
d'annulation temporaire
Types de segment
Table organisée en index :
Les données d'une table organisée en index sont stockées dans l'index en fonction de la valeur
de clé. Une table organisée en index ne nécessite pas de recherche, car toutes les données
peuvent être extraites directement à partir de l'arborescence d'index.
Partition d'index :
Un index peut être partitionné et réparti dans plusieurs tablespaces. Dans ce cas, chaque
partition correspond à un segment et ne peut pas occuper plusieurs tablespaces. Un index
partitionné est utilisé principalement pour réduire la contention en répartissant les
entrées/sorties d'index. Ce type de segment requiert l'option de partitionnement d'Oracle9i
Enterprise Edition.
Segment d'annulation :
Un segment d'annulation est utilisé par une transaction qui apporte des modifications à une
base. Avant toute modification des blocs de données ou d'index, l'ancienne valeur est stockée
dans le segment d'annulation pour permettre à l'utilisateur d'annuler les modifications, s'il le
souhaite.
Segment temporaire :
Lorsque l'utilisateur exécute des commandes telles que CREATE INDEX, SELECT
DISTINCT et SELECT GROUP BY, le serveur Oracle tente d'effectuer les tris dans la
mémoire. Lorsqu'un tri nécessite davantage d'espace disponible que n'en contient la mémoire,
des résultats intermédiaires sont écrits sur le disque dans des segments temporaires.
Segment Table
LOB imbriquée
Segment de
bootstrap
Types de segment
Segment LOB :
Vous pouvez utiliser une ou plusieurs colonnes d'une table pour stocker des objets LOB (Large
Object), tels que des documents texte, des images ou des données vidéo. Si les valeurs de ces
colonnes sont volumineuse, le serveur Oracle les enregistre dans des segments distincts
appelés segments LOB. La table contient uniquement un pointeur vers l'emplacement des
données LOB correspondantes.
Table imbriquée :
Une colonne de table peut être constituée d'une table définie par l'utilisateur contenant, par
exemple, les articles d'une commande. Dans ce cas, la table interne, appelée table imbriquée,
est stockée sous forme de segment distinct.
Segment de bootstrap :
Un segment de bootstrap, appelé également segment de cache, est créé par le script sql.bsq
en même temps que la base de données. Ce segment permet d'initialiser le cache du
dictionnaire de données lorsqu'une instance ouvre la base.
Le segment de bootstrap ne peut pas faire l'objet d'une interrogation, ni d'une mise à jour, et ne
nécessite aucune opération de maintenance de la part de l'administrateur de la base de
données.
Tablespace
Segment
Paramètres de stockage
Une clause de stockage peut être définie au niveau du segment afin de contrôler le mode
d'allocation des extents à un segment.
• Tout paramètre de stockage défini au niveau du segment annule l'option correspondante
définie au niveau du tablespace, sauf pour les paramètres de tablespace MINIMUM
EXTENT et UNIFORM SIZE.
• Lorsque des paramètres de stockage ne sont pas définis explicitement au niveau du
segment, les paramètres définis au niveau du tablespace sont utilisés par défaut.
• Lorsque les paramètres de stockage ne sont pas définis explicitement au niveau du
tablespace, les valeurs système par défaut du serveur Oracle sont utilisées.
Autres considérations :
• Lorsque vous modifiez des paramètres de stockage, les nouvelles valeurs s'appliquent
uniquement aux extents non encore alloués.
• Certains paramètres ne peuvent pas être définis au niveau du tablespace. Ils doivent être
indiqués au niveau du segment uniquement.
• Lorsque vous définissez une taille minimum d'extent pour le tablespace, cette taille
s'applique à tous les extents alloués ultérieurement à des segments du tablespace.
Fichier de données
En-tête
Extent utilisé Extent libre
de fichier
Extents
Lorsqu'un tablespace est créé, les fichiers de données qu'il contient présentent un en-tête qui
correspond au(x) premier(s) bloc(s) du fichier.
Chaque segment créé reçoit de l'espace provenant des extents libres du tablespace. L'espace
contigu utilisé par un segment est appelé extent utilisé. Lorsque des segments libèrent de
l'espace, les extents libérés sont ajoutés au pool d'extents libres disponibles dans le tablespace.
DESCRIBE dba_tablespaces
Name Null? Type
------------------ -------- ------------
TABLESPACE_NAME NOT NULL VARCHAR2(30)
BLOCK_SIZE NOT NULL NUMBER
...
En-tête
Espace libre
Données
Blocs de données
Un bloc de données Oracle contient :
• Un en-tête de bloc : l'en-tête contient l'adresse du bloc de données, le répertoire des
tables, le répertoire des lignes et les espaces de transaction utilisés lorsque des
transactions modifient des lignes du bloc. La croissance d'un en-tête de bloc s'effectue du
haut vers le bas.
• Un espace de données : les données des lignes sont insérées dans le bloc du bas vers le
haut.
• Un espace libre : l'espace libre se situe au milieu du bloc, ce qui permet l'extension de
l'en-tête et des données de ligne lorsque cela s'avère nécessaire. A l'origine, l'espace libre
d'un bloc est contigu, mais il peut être fragmenté par les suppressions et les mises à jour.
Lorsque c'est nécessaire, le serveur Oracle effectue une fusion de cet espace.
INITRANS
MAXTRANS
PCTFREE
PCTUSED
Insertions
80 %
Insertions
(en dehors de la liste
de blocs libres)
1 2
80 %
Insertions Insertion
(dans la liste
40 % de blocs libres)
3 4
Présentation de l'exercice 9
Remarque : Cet exercice peut être réalisé avec SQL*Plus ou avec Oracle Enterprise Manager et
SQL*Plus Worksheet.
Ancienne image
Table
Nouvelle
image
Segment d'annulation
Transaction
de mise à jour
Segment d'annulation
Un segment d'annulation permet d'enregistrer l'ancienne valeur (données d'annulation)
lorsqu'un processus modifie les données d'une base. Il enregistre l'emplacement des données et
leur valeur avant modification.
L'en-tête d'un segment d'annulation contient une table des transactions dans laquelle sont
stockées des informations sur les transactions en cours associées au segment d'annulation.
Les données d'annulation d'une transaction séquentielle sont stockées dans un seul segment
d'annulation.
Plusieurs transactions simultanées peuvent écrire des données dans un même segment
d'annulation.
Annulation
d'une transaction
SELECT *
Table FROM table
Nouvelle image
Image au démarrage de l'instruction
Cohérence en lecture
Le serveur Oracle permet à une instruction de voir les données de manière cohérente à un
instant donné, même si celles-ci sont modifiées par d'autres transactions.
Lorsque le serveur Oracle lance l'exécution d'une instruction SELECT, il détermine le numéro
SCN (System Change Number) en cours et s'assure que l'instruction ne traite pas les
modifications non validées avant ce numéro. Prenons le cas d'une longue interrogation
exécutée pendant que plusieurs modifications sont en cours : si une ligne contient des
modifications non validées au début de l'interrogation, le serveur Oracle crée une image
cohérente en lecture de cette ligne en extrayant l'image avant des modifications depuis le
segment d'annulation et en appliquant les modifications à une copie de la ligne en mémoire.
Cohérence en lecture d'une transaction
Une instruction SQL est toujours cohérente en lecture. Toutefois, vous pouvez demander la
cohérence en lecture d'une transaction en lecture seule en exécutant la commande suivante au
début de la transaction :
SQL> SET TRANSACTION READ ONLY;
Vous pouvez également demander la cohérence en lecture d'une transaction LMD en exécutant
la commande suivante au début de la transaction :
SQL> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
Dans les deux cas, le serveur Oracle fournit des données lues de manière cohérente dès le
début de la transaction. L'utilisation de la commande SERIALIZABLE peut affecter les
performances.
undo1db01.dbf
Fichier Tablespace
d'initialisation d'annulation (UNDO)
UNDO_MANAGEMENT=AUTO
UNDO_TABLESPACE=UNDOTBS
NAME STATUS
------------------------- ---------------
_SYSSMU4$ PENDING OFFLINE
• UNDO_SUPPRESS_ERRORS
– Lorsqu'il possède la valeur TRUE, ce paramètre
supprime les erreurs qui surviennent lors de tentatives
d'exécution d'opérations manuelles en mode AUTO.
• UNDO_RETENTION
– Ce paramètre contrôle le volume de données
d'annulation à conserver pour une lecture cohérente.
SELECT end_time,begin_time,undoblks
FROM v$undostat;
END_TIME BEGIN_TIME UNDO
------------------ ------------------ -----
22-JAN-01 13:44:18 22-JAN-01 13:43:04 19
22-JAN-01 13:43:04 22-JAN-01 13:33:04 1474
22-JAN-01 13:33:04 22-JAN-01 13:23:04 1347
22-JAN-01 13:23:04 22-JAN-01 13:13:04 1628
22-JAN-01 13:13:04 22-JAN-01 13:03:04 2249
22-JAN-01 13:03:04 22-JAN-01 12:53:04 1698
22-JAN-01 12:53:04 22-JAN-01 12:43:04 1433
22-JAN-01 12:43:04 22-JAN-01 12:33:04 1532
22-JAN-01 12:33:04 22-JAN-01 12:23:04 1075
Pour convertir des octets en mégaoctets, divisez-les par 1 048 576. Le résultat pour cette base
de données est de 18,22 Mo.
Présentation de l'exercice 10
Remarque : Vous pouvez réaliser cet exercice avec SQL*Plus ou avec Oracle Enterprise
Manager et SQL*Plus Worksheet.
Les types de données LOB stockent un pointeur dans la table et les données dans un autre
emplacement, sauf si la taille des données de type VARCHAR2 est inférieure à la taille
maximale de 4 000 octets. Les types de données LONG stockent toutes les données dans le
segment. En outre, les types de données LOB permettent de stocker les données dans un
segment et un tablespace distincts ou dans un fichier hôte.
Contrairement aux données de type LONG, les données LOB prennent en charge les attributs
des types d'objet (sauf les NCLOB) et la réplication.
Les données de type LONG sont stockées sous forme de morceaux de lignes chaînés, un
morceau de ligne d'un bloc pointant vers le morceau de ligne suivant stocké dans un autre
bloc. En conséquence, l'accès aux données doit s'effectuer séquentiellement. En revanche, les
données LOB offrent un accès systématique et aléatoire aux segments de données via une
interface de type fichier.
Types de données ROWID et UROWID
Le type de données ROWID peut être interrogé en même temps que les autres colonnes d'une
table. Il présente les caractéristiques suivantes :
• Il identifie de manière unique chaque ligne de la base de données.
• Il n'est pas enregistré explicitement sous la forme d'une valeur de colonne.
• Il permet de localiser une ligne, bien qu'il ne fournisse pas directement son adresse
physique.
• Il constitue la méthode la plus rapide pour accéder à une ligne d'une table.
• Il est stocké dans un index définissant des lignes ayant des valeurs de clé spécifiques.
Dans la version 8.1, le serveur Oracle fournit un type de données unique nommé ROWID
universel ou UROWID, qui prend en charge les ROWID de tables étrangères (non Oracle) et
peut stocker tous les types de ROWID. Par exemple, un type de données UROWID est
nécessaire pour le stockage d'un ROWID pour des lignes d'une table organisée en index. Pour
permettre l'utilisation du type UROWID, le paramètre COMPATIBLE doit posséder la valeur
Oracle8.1 ou une valeur supérieure.
• ROWID étendu
• ROWID restreint
Format de ROWID
Un ROWID étendu nécessite 10 octets de stockage sur disque et s'affiche sur 18 caractères. Il
est constitué des éléments suivants :
• Numéro d'objet de données : numéro unique dans la base de données, affecté à
chaque objet de données créé, tel qu'une table ou un index.
• Numéro de fichier relatif : numéro unique affecté à chaque fichier d'un
tablespace.
• Numéro de bloc : numéro indiquant l'emplacement, dans le fichier, du bloc
contenant la ligne.
• Numéro de ligne : numéro identifiant la position du pointeur des lignes dans l'en-
tête de bloc.
En interne, le numéro d'objet de données nécessite 32 bits, le numéro de fichier relatif, 10 bits,
le numéro de bloc, 22 bits, et le numéro de ligne, 16 bits, soit un total de 80 bits ou 10 octets.
Un ROWID étendu s'affiche à l'aide d'un schéma d'encodage en base 64 qui utilise six positions
pour le numéro d'objet, trois positions pour le numéro de fichier relatif, six positions pour le
numéro de bloc et trois positions pour le numéro de ligne. Ce schéma utilise les caractères A-
Z, a-z, 0-9, et /, soit un total de 64 caractères, comme dans l'exemple ci-dessous :
En-tête de ligne
Longueur de colonne
Bloc de base
de données Valeur de colonne
• Calcul de PCTFREE
(Longueur de ligne moyenne – Longueur de ligne initiale) * 100
• Calcul de PCTUSED
Longueur de ligne moyenne * 100
100 - PCTFREE -
Espace de données disponible
Pointeur
Pour identifier les tables sur lesquelles une opération DROP COLUMN n'est pas terminée,
interrogez la vue DBA_PARTIAL_DROP_TABS.
SQL > SELECT * FROM dba_partial_drop_tabs;
OBJECT_NAME CREATED
----------- ---------
EMPLOYEES 16-APR-01
Présentation de l'exercice 11
Remarque : Vous pouvez réaliser cet exercice avec SQL*Plus ou avec Oracle Enterprise
Manager et SQL*Plus Worksheet.
• Logique
– Index basés sur une colonne ou concaténés
– Index uniques ou non-uniques
– Index basés sur une fonction
– Index de domaine
• Physique
– Index partitionnés ou non partitionnés
– Index B-Tree
Index normaux ou à clé inversée
– Index bitmap
Entrée d'index
Racine
Branche
Index B-Tree
Bien que tous les index utilisent une structure B-Tree, l'expression index B-Tree est
généralement associée à un index qui stocke une liste de ROWID pour chaque clé.
Structure d'un index B-Tree
Dans la partie supérieure de l'index figure la racine contenant les entrées qui pointent sur le
niveau suivant de l'index. Le niveau suivant correspond aux blocs branche qui pointent sur les
blocs du niveau suivant de l'index. Au bas de l'arborescence se trouvent les noeuds feuille qui
contiennent les entrées d'index pointant sur les lignes de la table. Les blocs feuille comportent
deux liens facilitant le balayage de l'index dans l'ordre croissant et décroissant des valeurs de
clé.
Format des entrées feuille d'un index
Une entrée d'index est constituée :
• d'un en-tête d'entrée contenant le nombre de colonnes et les informations de verrouillage,
• de paires de valeurs correspondant à la longueur de la colonne de clé, qui définissent la
taille de la colonne dans la clé et la valeur de la colonne (le nombre de paires correspond
au nombre maximal de colonnes dans l'index),
• de l'identificateur ROWID d'une ligne, qui contient les valeurs de clé.
Table Fichier 3
Bloc 10
Bloc 11
Index Bloc 12
ROWID ROWID
clé de début de fin bitmap
<Blue, 10.0.3, 12.8.3, 1000100100010010100>
<Green, 10.0.3, 12.8.3, 0001010000100100000>
<Red, 10.0.3, 12.8.3, 0100000011000001001>
<Yellow, 10.0.3, 12.8.3, 0010001000001000010>
8
Index bitmap (suite)
La valeur maximale peut correspondre à une période spécifique, par exemple, une année.
Reconstruire un index
La reconstruction d'un index présente les caractéristiques suivantes :
• Un index est créé à partir d'un index existant.
• Aucun tri n'est nécessaire lors de la reconstruction d'un index à partir d'un index existant,
ce qui permet d'améliorer les performances.
• L'ancien index est supprimé après la création du nouvel index. Lors de la reconstruction,
un espace suffisant est nécessaire dans le tablespace de l'ancien index et dans celui du
nouvel index.
• Le nouvel index ne contient pas d'entrées supprimées, ce qui lui permet d'utiliser l'espace
de manière optimale.
• Les interrogations peuvent utiliser l'index existant pendant la construction du nouvel
index.
Cas de reconstruction d'index
Reconstruisez un index dans les cas suivants :
• L'index existant doit être transféré vers un autre tablespace. Cette opération peut s'avérer
nécessaire si l'index se trouve dans le même tablespace que la table ou si des objets
doivent être redistribués sur les disques.
INDEX_STATS
Présentation de l'exercice 12
Remarque : Cet exercice peut être réalisé avec SQL*Plus ou avec Oracle Enterprise
Manager et SQL*Plus Worksheet.
NUMB_OE_IDX ODD_EVEN
NUMB_NO_IDX NO
Déclencheur de Contrainte
base de données d'intégrité
Données
Code
d'application
Table
Contrainte Description
Types de contrainte
Par défaut, toutes les colonnes d'une table acceptent les valeurs NULL (absence de valeur).
Une contrainte NOT NULL exige qu'une colonne d'une table contienne des valeurs.
Une contrainte de clé UNIQUE nécessite que toutes les valeurs d'une colonne ou d'un ensemble
de colonnes (clé) soient uniques. Une colonne ou un ensemble de colonnes d'une table ne peut
pas contenir deux valeurs de ligne identiques.
Chaque table de la base de données ne peut contenir plus d'une contrainte de clé primaire. Ce
type de contrainte garantit :
• que la colonne définie ne peut pas posséder de doublons de valeur de ligne dans une table,
• que les colonnes de clé primaire ne contiennent pas de valeurs NULL.
• qu'une contrainte d'intégrité CHECK sur une colonne ou un ensemble de colonnes nécessite
qu'une condition définie soit vraie ou inconnue pour toutes les lignes de la table.
Bien que les contraintes NOT NULL et CHECK ne nécessitent aucune attention particulière de
la part de l'administrateur de base de données (DBA), les contraintes UNIQUE, de clé primaire
et de clé étrangère doivent être gérées pour garantir une disponibilité optimale et des
performances acceptables.
Nouvelles Données
données existantes
Instruction LMD
COMMIT
Vérifier la validité
des contraintes
différées
Non Non
N'utilisez pas Créez un Créez un index
d'index index unique non-unique
Où :
CONSTRAINT : identifie la contrainte d'intégrité par le nom constraint stocké dans le
dictionnaire de données.
USING INDEX : indique que les paramètres définis dans index-clause doivent être
utilisés pour l'index auquel fait appel le serveur Oracle afin d'appliquer une contrainte
UNIQUE ou de clé primaire (l'index porte le même nom que la contrainte).
DEFERRABLE : indique que la vérification des contraintes peut être différée jusqu'à la fin de
la transaction à l'aide de la commande SET CONSTRAINT.
NOT DEFERRABLE : indique que la contrainte est vérifiée à la fin de chaque instruction
LMD (une contrainte NOT DEFERRABLE ne peut pas être différée par des sessions ou des
transactions. NOT DEFERRABLE est utilisé par défaut.)
INITIALLY IMMEDIATE : indique qu'au début de chaque transaction, la contrainte doit, par
défaut, être vérifiée à la fin de chaque instruction LMD (si aucune clause INITIALLY n'est
définie, INITIALLY IMMEDIATE est utilisé par défaut).
INITIALLY DEFERRED : indique qu'il s'agit d'une contrainte DEFERRABLE et que, par
défaut, elle n'est vérifiée qu'à la fin de chaque transaction.
DISABLE : désactive la contrainte d'intégrité (lorsqu'une contrainte d'intégrité est désactivée,
le serveur Oracle ne l'applique pas).
[CONSTRAINT constraint]
{PRIMARY KEY (column [, column ]... )
[USING INDEX index_clause]
|UNIQUE (column [, column ]... )
[USING INDEX index_clause]
|FOREIGN KEY (column [, column ]... )
REFERENCES [schema.]table [(column [, column ]... )]
[ON DELETE CASCADE]
|CHECK (condition)
}
[constraint_state]
Remarque :
• Il est recommandé d'adopter une convention d'appellation standard pour les contraintes,
notamment pour les contraintes CHECK, puisque vous pouvez créer plusieurs fois la
même contrainte avec des noms différents.
• Vous devez utiliser des contraintes de table dans les cas suivants :
– lorsqu'une contrainte s'applique à plusieurs colonnes,
– lorsqu'une table est modifiée pour recevoir des contraintes autres que NOT NULL.
• La définition d'une contrainte à partir du type NOT NULL une fois la table créée n'est
possible que dans le cas suivant :
ALTER TABLE table MODIFY column CONSTRAINT constraint NOT
NULL;
Exemple de définition de contraintes après la création d'une table
SQL> ALTER TABLE hr.employee
2 ADD(CONSTRAINT employee_dept_id_fk FOREIGN KEY(dept_id)
3 REFERENCES hr.department(id)
4 DEFERRABLE INITIALLY DEFERRED);
Remarque : La clause EXCEPTIONS, décrite dans la section "Vérifier les contraintes" du
présent chapitre, peut être utilisée pour identifier les lignes qui enfreignent une contrainte
ajoutée à l'aide de la commande ALTER TABLE.
• Table verrouillée
ENABLE • Peut utiliser des index
VALIDATE uniques ou non-uniques
• Requiert des données de
table valides
Nom Description
CONSTRAINT_TYPE Le type de la contrainte est P, U, R ou C selon qu'il s'agit
respectivement d'une contrainte de clé primaire, de clé
unique, de clé étrangère ou d'une contrainte CHECK. Les
contraintes NOT NULL sont stockées sous forme de
contraintes CHECK.
BAD Indique que la contrainte doit être réécrite afin d'éviter des
problèmes tels que ceux liés au passage à l'an 2000.
RELY Si cet indicateur est défini, il est utilisé dans l'optimiseur.
LAST_CHANGE Indique la date à laquelle la contrainte a été activée ou
Colonnes des contraintes désactivée pour la dernière fois.
Pour identifier les colonnes des contraintes de la table EMPLOYEES de l'utilisateur HR, lancez
l'interrogation suivante :
SQL> SELECT c.constraint_name, c.constraint_type,
2 cc.column_name
3 FROM dba_constraints c, dba_cons_columns cc
4 WHERE c.owner='HR'
5 AND c.table_name='EMPLOYEE'
6 AND c.owner = cc.owner
7 AND c.constraint_name = cc.constraint_name
8 ORDER BY cc.position;
CONSTRAINT_NAME C COLUMN_NAME
---------------- - ---------------
EMPLOYEE_DEPT... R DEPT_ID
EMPLOYEE_ID_PK P ID
SYS_C00565 C LAST_NAME
3 rows selected.
Profils
Un profil correspond à un ensemble nommé contenant les limites suivantes, relatives aux mots
de passe et aux ressources :
• durée de vie et expiration des mots de passe,
• historique des mots de passe,
• vérification de la complexité des mots de passe,
• verrouillage d'un compte,
• temps CPU,
• opérations d'entrée/sortie (E/S),
• durée d'inactivité,
• durée de connexion,
• espace mémoire (zone SQL privée pour serveur partagé uniquement),
• sessions simultanées.
Une fois un profil créé, l'administrateur de base de données peut l'affecter à chaque utilisateur.
Si les limites relatives aux ressources sont actives, le serveur Oracle limite l'utilisation de la
base de données et des ressources au profil défini pour l'utilisateur.
Historique Verrouillage
des mots d'un compte
de passe
Utilisateur Configurer
des profils
Paramètre Description
FAILED_LOGIN_ATTEMPTS Nombre d'échecs de connexion
avant verrouillage du compte
Paramètre Description
PASSWORD_REUSE_TIME Période, en jours, pendant laquelle
un mot de passe ne peut pas être
réutilisé
Paramètre Description
PASSWORD_VERIFY_FUNCTION Fonction PL/SQL qui vérifie la
complexité d'un mot de passe
avant que celui-ci ne soit affecté
function_name(
userid_parameter IN VARCHAR2(30),
password_parameter IN VARCHAR2(30),
old_password_parameter IN VARCHAR2(30))
RETURN BOOLEAN
Créer un profil
Utilisez la commande CREATE PROFILE pour administrer les mots de passe :
CREATE PROFILE profile LIMIT
[FAILED_LOGIN_ATTEMPTS max_value]
[PASSWORD_LIFE_TIME max_value]
[ {PASSWORD_REUSE_TIME
|PASSWORD_REUSE_MAX} max_value]
[PASSWORD_LOCK_TIME max_value]
[PASSWORD_GRACE_TIME max_value]
[PASSWORD_VERIFY_FUNCTION
{function|NULL|DEFAULT} ]
Modifier un profil
Utilisez la commande ALTER PROFILE pour modifier les limites relatives aux mots de passe
affectées à un profil :
ALTER PROFILE profile LIMIT
[FAILED_LOGIN_ATTEMPTS max_value]
[PASSWORD_LIFE_TIME max_value]
[ {PASSWORD_REUSE_TIME
|PASSWORD_REUSE_MAX} max_value]
[PASSWORD_LOCK_TIME max_value]
[PASSWORD_GRACE_TIME max_value]
[PASSWORD_VERIFY_FUNCTION
{function|NULL|DEFAULT} ]
Pour affecter aux paramètres de mot de passe une valeur inférieure à un jour :
1 heure : PASSWORD_LOCK_TIME = 1/24
10 minutes : PASSWORD_LOCK_TIME = 10/1400
5 minutes : PASSWORD_LOCK_TIME = 5/1440
Ressource Description
Obtenir des informations sur les limites relatives aux mots de passe et aux
ressources
Interrogez la vue DBA_USERS pour obtenir des informations sur le statut des comptes.
SQL> SELECT username, password, account_status,
2 FROM dba_users;
USERNAME PASSWORD ACCOUNT_STATUS
------- -------- --------------
SYS 8A8F025737A9097A OPEN
SYSTEM D4DF7931AB130E37 OPEN
OUTLN 4A3BA55E08595C81 OPEN
DBSNMP E066D214D5421CCC OPEN
HR BB69FBB77CFA6B9A OPEN
OE 957C7EF29CC223FC LOCKED
Présentation de l'exercice 14
Remarque : Vous pouvez réaliser cet exercice avec SQL*Plus ou avec Oracle Enterprise
Manager et SQL*Plus Worksheet.
Verrouillage Tablespace
de compte par défaut
Mécanisme Tablespace
d'authen- temporaire
tification
Domaine de
sécurité
Privilèges Quotas de
de rôle tablespace
Privilèges Limites
directs relatives aux
ressources
Utilisateurs et sécurité
Domaine de sécurité
L'administrateur de base de données définit le nom des utilisateurs autorisés à accéder à la
base de données. Un domaine de sécurité définit les paramètres qui s'appliquent à l'utilisateur.
Mécanisme d'authentification
Trois méthodes permettent d'authentifier un utilisateur qui souhaite accéder à la base de
données :
• Par le dictionnaire de données
• Par le système d'exploitation
• Par le réseau
La méthode d'authentification est précisée lorsque vous définissez l'utilisateur dans la base de
données. Elle peut ensuite être modifiée. Le présent chapitre porte uniquement sur
l'authentification par la base de données et par le système d'exploitation.
Remarque : Pour plus d'informations sur l'authentification par le système d'exploitation à
l'aide de rôles, reportez-vous au chapitre "Initiation au serveur Oracle".
L'authentification via le réseau est présentée dans le cours Oracle9i DBA II.
Objets de schéma
• Un schéma est un ensemble
nommé d'objets. Tables
Déclencheurs
• Lorsqu'un utilisateur est
Contraintes
créé, un schéma
correspondant est Index
également créé. Vues
Séquences
• Un utilisateur ne peut être
associé qu'à un seul Programmes stockés
schéma. Synonymes
Supprimer un utilisateur
DROP USER user [CASCADE]
Règles :
• L'option CASCADE supprime tous les objets du schéma avant de supprimer l'utilisateur.
Elle doit être définie si le schéma contient des objets.
• Vous ne pouvez pas supprimer un utilisateur connecté au serveur Oracle.
USERNAME DEFAULT_TABLESPACE
--------- ------------------
SYS SYSTEM
SYSTEM SYSTEM
OUTLN SYSTEM
DBSNMP SYSTEM
HR SAMPLE
OE SAMPLE
Présentation de l'exercice 15
Remarque : Vous pouvez réaliser cet exercice avec SQL*Plus ou avec Oracle Enterprise
Manager et SQL*Plus Worksheet.
Privilèges
Un privilège est un droit d'exécution d'un type donné d'instruction SQL ou un droit d'accès à
l'objet d'un autre utilisateur. Il autorise son détenteur à :
• se connecter à une base de données,
• créer une table,
• sélectionner des lignes dans la table d'un autre utilisateur,
• exécuter la procédure stockée d'un autre utilisateur.
Privilèges système
Chaque privilège système permet à un utilisateur d'effectuer une opération spécifique ou une
catégorie d'opérations sur la base de données. Par exemple, le privilège lié à la création de
tablespaces est un privilège système.
Privilèges objet
Chaque privilège objet autorise un utilisateur à exécuter une action spécifique sur un objet tel
qu'une table, une vue, une séquence, une procédure, une fonction ou un package.
Dans sa tâche de contrôle des privilèges, un administrateur de base de données est chargé :
• d'autoriser un utilisateur à effectuer un type d'opération,
• d'accorder et de révoquer le droit d'effectuer certaines opérations système,
• d'accorder des privilèges directement à des utilisateurs ou à des rôles,
• d'accorder des privilèges à tous les utilisateurs (PUBLIC).
Privilèges système
Ces privilèges peuvent être classés comme suit :
• Privilèges autorisant l'exécution d'opérations sur l'ensemble du système, tels que
CREATE SESSION, CREATE TABLESPACE
• Privilèges autorisant la gestion d'objets dans un schéma propre à l'utilisateur, tels que
CREATE TABLE
• Privilèges autorisant la gestion d'objets de n'importe quel schéma, tels que CREATE ANY
TABLE
Les privilèges peuvent être gérés à l'aide des commandes LDD GRANT et REVOKE, qui
permettent d'accorder et de révoquer des privilèges système à un utilisateur ou à un rôle (pour
plus d'informations sur les rôles, voir le chapitre "Gérer les rôles").
Où :
system_privilege : désigne le privilège système à accorder.
role : désigne le nom du rôle à accorder.
PUBLIC : accorde le privilège système à tous les utilisateurs.
WITH ADMIN OPTION : autorise le bénéficiaire à accorder son privilège ou son rôle à
d'autres utilisateurs ou rôles.
Catégorie Exemples
SYSOPER STARTUP
SHUTDOWN
ALTER DATABASE OPEN | MOUNT
ALTER DATABASE BACKUP CONTROLFILE TO
RECOVER DATABASE
ALTER DATABASE ARCHIVELOG
RESTRICTED SESSION
SYSDBA SYSOPER PRIVILEGES WITH ADMIN OPTION
CREATE DATABASE
ALTER TABLESPACE BEGIN/END BACKUP
RESTRICTED SESSION
RECOVER DATABASE UNTIL
• Le paramètre O7_DICTIONARY_ACCESSIBILITY :
• contrôle les restrictions relatives aux privilèges
système,
• permet d'accéder aux objets du schéma SYS s'il
possède la valeur TRUE,
• grâce à la valeur par défaut FALSE, les privilèges
système qui permettent d'accéder à n'importe quel
schéma interdisent l'accès au schéma SYS.
GRANT
Privilèges objet
Un privilège objet est un privilège ou droit autorisant la réalisation d'une action donnée sur une
table, une vue, une séquence, une procédure, une fonction ou un package spécifique. Chaque
objet présente un ensemble propre de privilèges pouvant être accordés. Le tableau de la
diapositive répertorie les privilèges correspondant à différents objets. Ainsi, les seuls
privilèges qui s'appliquent à une séquence sont SELECT et ALTER. Vous pouvez restreindre
les privilèges UPDATE, REFERENCES et INSERT en précisant un sous-ensemble de colonnes
pouvant être mises à jour. Vous pouvez limiter un droit de type SELECT en créant une vue
présentant un sous-ensemble de colonnes et en accordant le privilège SELECT sur la vue. Un
privilège accordé sur un synonyme octroie directement un droit sur la table de base référencée
par ce synonyme.
Remarque : La diapositive ne fournit pas la liste exhaustive des privilèges objet.
Présentation de l'exercice 16
Remarque : Cet exercice peut être réalisé avec SQL*Plus ou avec Oracle Enterprise Manager et
SQL*Plus Worksheet.
Privilèges
Un privilège est un droit d'exécution d'un type donné d'instruction SQL ou un droit d'accès à
l'objet d'un autre utilisateur. Il autorise son détenteur à :
• se connecter à une base de données,
• créer une table,
• sélectionner des lignes dans la table d'un autre utilisateur,
• exécuter la procédure stockée d'un autre utilisateur.
Privilèges système
Chaque privilège système permet à un utilisateur d'effectuer une opération spécifique ou une
catégorie d'opérations sur la base de données. Par exemple, le privilège lié à la création de
tablespaces est un privilège système.
Privilèges objet
Chaque privilège objet autorise un utilisateur à exécuter une action spécifique sur un objet tel
qu'une table, une vue, une séquence, une procédure, une fonction ou un package.
Dans sa tâche de contrôle des privilèges, un administrateur de base de données est chargé :
• d'autoriser un utilisateur à effectuer un type d'opération,
• d'accorder et de révoquer le droit d'effectuer certaines opérations système,
• d'accorder des privilèges directement à des utilisateurs ou à des rôles,
• d'accorder des privilèges à tous les utilisateurs (PUBLIC).
Privilèges système
Ces privilèges peuvent être classés comme suit :
• Privilèges autorisant l'exécution d'opérations sur l'ensemble du système, tels que
CREATE SESSION, CREATE TABLESPACE
• Privilèges autorisant la gestion d'objets dans un schéma propre à l'utilisateur, tels que
CREATE TABLE
• Privilèges autorisant la gestion d'objets de n'importe quel schéma, tels que CREATE ANY
TABLE
Les privilèges peuvent être gérés à l'aide des commandes LDD GRANT et REVOKE, qui
permettent d'accorder et de révoquer des privilèges système à un utilisateur ou à un rôle (pour
plus d'informations sur les rôles, voir le chapitre "Gérer les rôles").
Où :
system_privilege : désigne le privilège système à accorder.
role : désigne le nom du rôle à accorder.
PUBLIC : accorde le privilège système à tous les utilisateurs.
WITH ADMIN OPTION : autorise le bénéficiaire à accorder son privilège ou son rôle à
d'autres utilisateurs ou rôles.
Catégorie Exemples
SYSOPER STARTUP
SHUTDOWN
ALTER DATABASE OPEN | MOUNT
ALTER DATABASE BACKUP CONTROLFILE TO
RECOVER DATABASE
ALTER DATABASE ARCHIVELOG
RESTRICTED SESSION
SYSDBA SYSOPER PRIVILEGES WITH ADMIN OPTION
CREATE DATABASE
ALTER TABLESPACE BEGIN/END BACKUP
RESTRICTED SESSION
RECOVER DATABASE UNTIL
• Le paramètre O7_DICTIONARY_ACCESSIBILITY :
• contrôle les restrictions relatives aux privilèges
système,
• permet d'accéder aux objets du schéma SYS s'il
possède la valeur TRUE,
• grâce à la valeur par défaut FALSE, les privilèges
système qui permettent d'accéder à n'importe quel
schéma interdisent l'accès au schéma SYS.
GRANT
Privilèges objet
Un privilège objet est un privilège ou droit autorisant la réalisation d'une action donnée sur une
table, une vue, une séquence, une procédure, une fonction ou un package spécifique. Chaque
objet présente un ensemble propre de privilèges pouvant être accordés. Le tableau de la
diapositive répertorie les privilèges correspondant à différents objets. Ainsi, les seuls
privilèges qui s'appliquent à une séquence sont SELECT et ALTER. Vous pouvez restreindre
les privilèges UPDATE, REFERENCES et INSERT en précisant un sous-ensemble de colonnes
pouvant être mises à jour. Vous pouvez limiter un droit de type SELECT en créant une vue
présentant un sous-ensemble de colonnes et en accordant le privilège SELECT sur la vue. Un
privilège accordé sur un synonyme octroie directement un droit sur la table de base référencée
par ce synonyme.
Remarque : La diapositive ne fournit pas la liste exhaustive des privilèges objet.
Présentation de l'exercice 16
Remarque : Cet exercice peut être réalisé avec SQL*Plus ou avec Oracle Enterprise Manager et
SQL*Plus Worksheet.
Utilisateurs
A B C
Privilèges
SELECT ON INSERT ON
JOBS JOBS
Rôles Description
CONNECT, Fournis pour garantir une
RESOURCE, DBA compatibilité descendante
EXP_FULL_DATABASE Privilèges d'export de la
base de données
IMP_FULL_DATABASE Privilèges d'import de la
base de données
DELETE_CATALOG_ROLE Privilèges DELETE sur les tables
du dictionnaire de données
EXECUTE_CATALOG_ROLE Privilège EXECUTE sur les
packages du dictionnaire de
données
SELECT_CATALOG_ROLE Privilège SELECT sur les tables
du dictionnaire de données
Rôles prédéfinis
Les rôles présentés dans le tableau sont automatiquement définis pour les bases de données
Oracle à l'exécution des scripts de création de base de données. Les rôles CONNECT,
RESOURCE et DBA sont fournis pour de assurer une compatibilité descendante avec les
versions antérieures du serveur Oracle.
Les rôles EXP_FULL_DATABASE et IMP_FULL_DATABASE sont fournis pour faciliter
l'utilisation des utilitaires Import et Export.
Les rôles DELETE_CATALOG_ROLE, EXECUTE_CATALOG_ROLE et
SELECT_CATALOG_ROLE permettent d'accéder aux vues et aux packages du dictionnaire de
données. Ils sont accordés aux utilisateurs qui ne disposent pas du rôle DBA, mais qui doivent
accéder aux vues et aux tables du dictionnaire de données.
Autres rôles spéciaux
Le serveur Oracle crée également d'autres rôles qui vous permettent d'administrer la base de
données. Sur la plupart des systèmes d'exploitation, il s'agit des rôles OSOPER et OSDBA. Leur
nom peut varier selon le système d'exploitation utilisé.
D'autres rôles sont définis dans les scripts SQL fournis avec la base de données. Par exemple,
AQ_ADMINISTRATOR_ROLE permet d'administrer Advanced Queuing. AQ_USER_ROLE
est obsolète, mais il est principalement conservé pour assurer la compatibilité avec la version
8.0.
Rôles d'application
La clause de package USING de l'instruction CREATE ROLE crée un rôle d'application. Seules
les applications utilisant un package PL/SQL autorisé peuvent activer un rôle d'application.
Les développeurs d'applications n'ont pas besoin d'intégrer des mots de passe aux applications
pour sécuriser un rôle. Ils peuvent en effet créer un rôle d'application et désigner le package
PL/SQL autorisé à l'activer.
SQL> CREATE ROLE admin_role IDENTIFIED USING hr.employees;
Dans l'exemple, admin_role est un rôle d'application qui ne peut être activé que par les
modules définis dans le package PL/SQL hr.employee.
Utilisateurs
Rôles
utilisateur HR_CLERK HR_MANAGER PAY_CLERK
Rôles
d'application BENEFITS PAYROLL
Privilèges
d'application
Privilèges Benefits Privilèges Payroll
PAY_CLERK PAY_CLERK_RO
Présentation de l'exercice 17
Remarque : Cet exercice peut être réalisé avec SQL*Plus ou avec Oracle Enterprise Manager et
SQL*Plus Worksheet.
5 Sous l'ID utilisateur SYSTEM, tentez de créer la vue CUST_VIEW dans la table
CUSTOMERS d'Emi. Que se passe-t-il ?
6 Sous l'ID utilisateur Emi, accordez à SYSTEM le privilège SELECT sur CUSTOMERS1.
En tant que SYSTEM, tentez ensuite de créer la vue CUST_VIEW dans la table
CUSTOMERS1 d'Emi. Que se passe-t-il ?
Fonction d'audit
Si un utilisateur non autorisé supprime des données, l'administrateur de base de données peut
effectuer un audit de toutes les connexions à la base et de toutes les suppressions (qu'elles aient
abouti ou non) effectuées sur l'ensemble des tables de la base. L'administrateur peut
rassembler des statistiques sur les tables mises à jour, le nombre d'entrées/sorties (E/S)
logiques réalisées et le nombre d'utilisateurs qui se connectent simultanément aux périodes les
plus chargées.
Catégories d'audit
Que l'audit de la base de données soit activé ou non, Oracle enregistre toujours certaines
opérations effectuées sur la base dans la trace d'audit du système d'exploitation :
• Démarrage de l'instance : l'enregistrement d'audit indique le nom de l'utilisateur du
système d'exploitation qui a démarré l'instance, l'identificateur du terminal, la date et
l'heure et si l'audit de la base de données était activé ou non.
• Arrêt de l'instance : l'enregistrement d'audit indique le nom de l'utilisateur du système
d'exploitation qui a arrêté l'instance, l'identificateur du terminal, ainsi que la date et
l'heure.
• Privilèges de l'administrateur : l'enregistrement d'audit indique le nom de l'utilisateur du
système d'exploitation qui s'est connecté à Oracle avec des privilèges d'administrateur.
Audit d'une base de données
L'audit d'une base de données consiste à surveiller et à enregistrer certaines actions exécutées
sur la base de données par les utilisateurs. Les informations sur les actions sont enregistrées
dans la trace d'audit.
Utilisez la trace d'audit pour identifier les activités suspectes. Par exemple, si un utilisateur non
autorisé supprime des données dans des tables, l'administrateur de base de données peut
décider d'auditer toutes les connexions à la base de données en conjonction avec les
suppressions de lignes ayant réussi ou échoué dans les tables de la base.
Options
d'audit
Trace d'audit
Base de
de l'OS
données
Trace d'audit
• Audit d'instruction
AUDIT TABLE;
• Audit de privilège
AUDIT create any trigger;
Options d'audit
Audit d'instruction :
audit sélectif d'instructions SQL et non des objets de schéma spécifiques qui s'y rapportent. Par
exemple, l'option AUDIT TABLE analyse plusieurs instructions LDD, quelle que soit la table
sur laquelle elles sont exécutées. Vous pouvez configurer un audit d'instruction pour analyser
certains utilisateurs ou tous les utilisateurs de la base de données.
Audit de privilège :
audit sélectif de privilèges système dans le but de réaliser les actions correspondantes, par
exemple, AUDIT CREATE ANY TRIGGER. Vous pouvez configurer ce type d'option pour
analyser un utilisateur donné ou tous les utilisateurs de la base de données.
Audit d'objet de schéma :
audit sélectif d'instructions spécifiques relatives à un objet de schéma donné, par exemple,
AUDIT SELECT ON HR.EMPLOYEES. Ce type d'audit s'applique toujours à tous les
utilisateurs de la base de données.
Vous pouvez choisir n'importe quelle option d'audit et préciser les conditions suivantes :
• WHENEVER SUCCESSFUL / WHENEVER NOT SUCCESSFUL
• BY SESSION / BY ACCESS
Audit détaillé
• Permet la surveillance de l'accès aux données en
fonction du contenu
• S'implémente à l'aide du package DBMS_FGA
Options d'audit
Audit détaillé : permet de surveiller l'accès aux données en fonction du contenu. Le package
PL/SQL DBMS_FGA gère les règles d'audit basé sur les données. A l'aide de ce package,
l'administrateur de base de données crée une règle d'audit sur la table cible. Si l'une des lignes
renvoyées par un bloc d'interrogations correspond à la condition d'audit, une entrée
d'événement d'audit (nom utilisateur, texte SQL, variable attachée (bind variable), nom de
règle, ID de session, horodatage, etc.) et d'autres attributs sont intégrés à la trace d'audit
Désactiver un audit
Utilisez l'instruction NOAUDIT pour arrêter un audit lancé par la commande AUDIT.
Remarque : L'instruction NOAUDIT inverse l'effet d'une instruction AUDIT exécutée
précédemment. Elle doit présenter la même syntaxe que l'instruction AUDIT précédente et
inverse uniquement l'effet de cette instruction. Par conséquent, si une instruction AUDIT
(instruction A) active un audit sur un utilisateur et qu'une seconde instruction (B) active un
audit sur tous les utilisateurs, l'instruction NOAUDIT qui désactive l'audit pour tous les
utilisateurs inverse l'instruction B, mais n'affecte pas l'instruction A et continue d'analyser
l'utilisateur concerné par l'instruction A.
Autres applications
Base de
données
Oracle
Export
Utilitaire
SQL*Loader
Import
Base de
données
Oracle
Chargement des données
par chemin direct
Processus
serveur
Table EMPLOYEES
Processus Processus
esclave esclave
Table EMPLOYEES
Fichier de paramètres
SQL*Loader Rejet
(facultatif)
Traitement du champ
Rebut Acceptation
Sélection de l'enregistrement Fichier des
enregistrements
Sélection refusés
Serveur Oracle
Fichier de rebut
Insertion Rejet
(facultatif)
Fichier journal
Caractéristiques de SQL*Loader
L'utilitaire SQL*Loader charge les données des fichiers externes dans les tables d'une base de
données Oracle. Ses caractéristiques sont les suivantes :
• SQL*Loader peut utiliser un ou plusieurs fichiers en entrée.
• Plusieurs enregistrements en entrée peuvent être combinés pour former un enregistrement
de chargement logique.
• Les champs en entrée peuvent être de longueur fixe ou de longueur variable.
• Les données en entrée peuvent se présenter sous n'importe quel format : caractère, binaire,
décimal condensé, date et décimal non condensé.
• Les données peuvent être chargées à partir de différents types de support, tels que des
disques, des bandes ou des canaux nommés.
• Les données peuvent être chargées simultanément dans plusieurs tables.
• Des options sont disponibles pour ajouter des données aux tables ou remplacer des
données existantes.
• Les fonctions SQL peuvent être appliquées aux données en entrée avant le stockage de la
ligne dans la base de données.
• Les valeurs de colonne peuvent être générées automatiquement en fonction de règles. Par
exemple, une valeur de clé séquentielle peut être générée et stockée dans une colonne.
• Les données peuvent être chargées directement dans la table sans utiliser le cache de
tampons de la base de données.
$sqlldr hr/hr \
> control=case1.ctl \
> log=case1.log direct=Y
case1.ctl
SQL*Loader
Table EMPLOYEES
case1.log
Utiliser SQL*Loader
Ligne de commande
Lorsque vous appelez SQL*Loader, vous pouvez définir les paramètres qui déterminent les
caractéristiques de la session. Ces paramètres peuvent être entrés dans n'importe quel ordre,
éventuellement séparés par des virgules. Vous pouvez indiquer les valeurs des paramètres ou,
dans certains cas, accepter les paramètres par défaut sans entrer de valeurs.
Si vous appelez SQL*Loader sans définir de paramètres, un écran d'aide affiche les
paramètres disponibles et leurs valeurs par défaut.
Enregistrements logiques
SQL*Loader organise les données en entrée en enregistrements physiques, en fonction du
format d'enregistrement défini. Un enregistrement physique est par défaut un enregistrement
logique, mais pour une plus grande flexibilité, il est possible d'indiquer à SQL*Loader de
combiner un certain nombre d'enregistrements physiques à un enregistrement logique. Pour
réaliser cette opération, SQL*Loader peut utiliser l'une des deux méthodes suivantes :
• Combiner un nombre fixe d'enregistrements physiques pour la création de chaque
enregistrement logique.
• Combiner des enregistrements physiques à des enregistrements logiques lorsqu'une
condition donnée est vraie.
Utiliser CONCATENATE pour assembler des enregistrements logiques
Le mot-clé CONCATENATE est utilisé lorsque SQL*Loader doit toujours ajouter le même
nombre d'enregistrements physiques pour créer un enregistrement logique. L'exemple suivant
utilise CONCATENATE avec integer, qui indique le nombre d'enregistrements physiques à
combiner :
CONCATENATE integer
Conventionnel
Table Chemin
Enregistrement direct
des données
Repère
Espace utilisé uniquement par le high-water mark
chargement par chemin conventionnel
SQL*Loader
load2.dat
load2.ctl
SQL*Loader
load3.dat
load3.ctl
Table
Repère
high-water mark
• Informations d'en-tête
• Informations générales
• Informations sur les tables
• Informations sur les fichiers de données
• Informations de chargement des tables
• Statistiques récapitulatives
• Statistiques supplémentaires relatives aux
chargements des données par chemin direct et
informations sur la technologie multithread
Présentation de l'exercice 19
Remarque : Cet exercice peut être réalisé avec SQL*Plus ou avec Oracle Enterprise Manager
et SQL*Plus Worksheet.
Encodages
Un encodage de jeux de caractères définit les codes numériques correspondant aux caractères
qu'un ordinateur ou un terminal peut afficher et recevoir.
Les encodages de jeux de caractères permettent d'interpréter des données en symboles
significatifs, d'un terminal vers une machine hôte.
Oracle prend en charge différents types d'encodage :
• simple octet,
• longueur variable,
• longueur fixe,
• Unicode.
Stockent les données dans des Stockent les données dans des
colonnes de type CHAR, VARCHAR2, colonnes de type NCHAR,
CLOB, LONG NVARCHAR2, NCLOB
Peuvent stocker des jeux de Peuvent stocker des jeux de
caractères de longueur variable caractères Unicode de type
AL16UTF16 ou UTF8
• Deux possibilités :
– AL16UTF16
– UTF8
• Le facteur espace est-il à prendre en compte ?
• Le facteur performances est-il à prendre en
compte ?
Paramètre d'initialisation
Variable d'environnement
• NLS_LANGUAGE indique :
– la langue des messages
– le jour et le mois
– les symboles utilisés pour A.D, B.C, A.M, P.M
– le mécanisme de tri par défaut
• NLS_TERRITORY indique :
– le numéro du jour et de la semaine
– le format de date par défaut, le caractère décimal, le
séparateur de groupe et les symboles monétaires ISO
et nationaux par défaut
Paramètre Valeurs
NLS_LANGUAGE AMERICAN
NLS_DATE_LANGUAGE AMERICAN
NLS_SORT BINARY
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_DATE_FORMAT DD-MON-RR
NLS_NUMERIC_CHARACTERS ,.
Colonne Description
NLS_DATE_LANGUAGE Modifie explicitement la langue des noms
de jour et de mois et de leur abréviation
ainsi que celle des autres éléments de format
de date.
NLS_SORT Modifie la séquence de tri linguistique
qu'utilise le serveur Oracle pour trier les
caractères (la valeur de tri doit correspondre
au nom d'une séquence de tri linguistique).
Colonne Description
NLS_CURRENCY Définit explicitement un nouveau symbole
monétaire national.
NLS_ISO_CURRENCY Définit explicitement le territoire dont le
symbole monétaire ISO doit être utilisé.
NLS_DATE_FORMAT Définit explicitement un nouveau format de
date par défaut (la valeur doit correspondre
à un modèle de format de date).
NLS_NUMERIC_CHARACTERS Définit explicitement un nouveau caractère
décimal et un nouveau séparateur de
groupes de données.
Prise en charge de la double devise pour l'euro
L'euro, la nouvelle devise de l'Union Européenne, a été introduit le 1er janvier 1999. Pour
permettre sa prise en charge, le support double devise a été ajouté pour les territoires
concernés. Le paramètre d'initialisation NLS_DUAL_CURRENCY permet de définir un second
symbole monétaire pour la session utilisateur.
Dans le cadre de la prise en charge de la double devise, le symbole de l'euro a été ajouté pour
les territoires suivants :
Autriche Italie
Belgique Luxembourg
Danemark Pays-Bas
Finlande Portugal
France Espagne
Allemagne Suède
Grèce Royaume-Uni
Irlande
Les jeux de caractères ISO, tels que WE8ISO8859P15 et la page de codes Microsoft
WE8MSWIN1252 contiennent le code du symbole de l'euro.
• Variable d'environnement :
NLS_LANG=French_France.UTF8
• Autres variables d'environnement :
– NLS_DATE_FORMAT
– NLS_DATE_LANGUAGE
– NLS_SORT
– NLS_NUMERIC_CHARACTERS
– NLS_CURRENCY
– NLS_ISO_CURRENCY
– NLS_CALENDAR
NLS_LANG=
<language>_<territory>.<charset>
NLS_NCHAR=<ncharset>
DBMS_SESSION.SET_NLS(‘NLS_DATE_FORMAT’,
’’’DD.MM.YYYY’’’) ;
Tri linguistique
Un tri binaire est un mécanisme de tri conventionnel qui permet de trier les lettres en fonction
des valeurs binaires utilisées pour coder les caractères. La position alphabétique d'un caractère
peut varier d'une langue à l'autre.
Lorsque vous souhaitez comparer des chaînes, Oracle effectue un tri monolingue en deux
passages. Le premier passage sert à comparer la valeur principale de l'intégralité de la chaîne
de la table principale et le second passage à comparer la valeur secondaire de la table
secondaire. En règle générale, les lettres présentant le même aspect possèdent la même valeur
principale. Oracle définit des lettres avec des différences diacritiques et de casse pour la même
valeur principale, mais des valeurs secondaires différentes. Ce tri est ainsi plus performant que
le tri binaire, mais reste limité.
Pour effectuer le tri multilingue, Oracle utilise un mécanisme de tri qui repose sur une norme
ISO (ISO14651) et sur Unicode 3.0. Chaque langue peut ainsi trier correctement chaque
caractère codé.
A ce jour, Oracle reconnaît 84 tris linguistiques (68 tris monolingues et 13 tris multilingues).
Pour obtenir la liste complète des tris, voir Oracle9i Globalization Support Manual.
Tri NLS
Par exemple, la lettre ä est triée avant la lettre b en français, mais se trouve après z dans le tri
binaire.
Pour résoudre les problèmes de tri binaire, le serveur Oracle permet d'effectuer des tris
linguistiques à l'aide du paramètre NLS_SORT.
Impact du support NLS sur le tri
L'exemple suivant présente les trois types de tri :
• binaire,
• monolingue avec French,
• multilingue avec French_M.
Dans l'exemple, une liste de quatre termes français est créée dans une table.
La valeur BINARY est affectée au paramètre NLS_SORT. Vous remarquerez que dans
BINARY, e est placé avant è. En effet, dans l'encodage de jeux de caractères, e possède une
valeur binaire inférieure à celle de è.
SQL> ALTER SESSION SET NLS_SORT = BINARY;
Session altered.
SQL> SELECT num, word, def
2 FROM list
3 ORDER BY word;
NUM WORD DEF
--- ----- -------
4 gelez freeze
2 gelé frozen
1 gelée frost
3 gèle freezes
La valeur French est ensuite affectée au paramètre NLS_SORT. French est un tri monolingue
qui effectue uniquement deux passages, ce qui ne permet pas de saisir toutes les nuances de la
langue française. Par exemple, en français, les lettres sont triées de la gauche vers la droite, et
les accents, de la droite vers la gauche. Ce thème sera abordé dans le tri multilingue.
SQL> ALTER SESSION SET NLS_SORT = FRENCH;
Session altered.
SQL> SELECT num, word, def
2 FROM list
3 ORDER BY word;
NUM WORD DEF
--- ----- -------
2 gelé frozen
3 gèle freezes
1 gelée frost
4 gelez freeze
NLSSORT permet de définir le tri au niveau interrogation. Dans l'exemple suivant, la valeur
BINARY est affectée au paramètre NLS_SORT au niveau session, mais le tri est modifié au
niveau interrogation. Vous remarquerez que les résultats sont identiques à ceux de l'exemple
ci-dessus.
SQL> ALTER SESSION SET NLS_SORT=BINARY;
Session altered.
SQL> SELECT num, word, def
2 FROM list
3 ORDER BY NLSSORT(word,'NLS_SORT=FRENCH_M');
SELECT TO_CHAR(hire_date,’DD.Mon.YYYY’,
‘NLS_DATE_LANGUAGE=FRENCH’)
FROM employees;
Hire Date
-----------
15.Dec.1997
03.Nov.1998
11.Nov.1997
19.Mar.1999
24.Jan.2000
23.Fev.2000
24.Mar.2000
21.Avr.2000
11.Mar.1997
23.Mar.1998
24.Jan.1998
23.Fev.1999
24.Mar.1999
21.Avr.2000
11.Mai.1996
19.Mar.1997
24.Mar.1998
23.Avr.1998
24.Mai.1999
04.Jan.2000
LAST_NAME TO_CHAR(SA
------------------------- ----------
Doran 7.500,00
Sewall 7.000,00
Vishney 10.500,00
Greene 9.500,00
Marvins 7.200,00
Lee 6.800,00
Ande 6.400,00
Banda 6.200,00
Ozer 11.500,00
Bloom 10.000,00
Fox 9.600,00
Smith 7.400,00
Bates 7.300,00
Kumar 6.100,00
Abel 11.000,00
Hutton 8.800,00
Taylor 8.600,00
Livingston 8.400,00
Grant 7.000,00
• Indexation linguistique
• Niveau de performances élevé avec le tri local
CREATE INDEX list_word ON
list (NLSSORT(word, ‘NLS_SORT =
French_M’));
• Paramètre NLS_COMP de comparaison linguistique
NLS_DATABASE_PARAMETERS:
• PARAMETER
(NLS_CHARACTERSET,
NLS_NCHAR_CHARACTERSET)
• VALUE
• NLS_INSTANCE_PARAMETERS:
– PARAMETER (paramètres d'initialisation explicitement
définis)
– VALUE
• NLS_SESSION_PARAMETERS:
– PARAMETER (paramètres de session)
– VALUE
NLS_TIME_TZ_FORMAT
NLS_TIMESTAMP_TZ_FORMAT
NLS_DUAL_CURRENCY
NLS_COMP
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE
17 rows selected.
La vue suivante contient les paramètres de session.
SQL> SELECT * FROM nls_session_parameters;
PARAMETER VALUE
------------------------------ ----------------------------
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE AMERICAN
NLS_SORT BINARY
NLS_TIME_FORMAT HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY $
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE
17 rows selected.
• V$NLS_VALID_VALUES:
– PARAMETER
(LANGUAGE, SORT, TERRITORY, CHARACTERSET)
– VALUE
• V$NLS_PARAMETERS:
– PARAMETER (paramètres de session NLS,
NLS_CHARACTERSET)
– VALUE
Pour afficher les valeurs en cours des paramètres NLS, utilisez la syntaxe suivante :
SQL> SELECT * FROM v$nls_parameters;
PARAMETER VALUE
------------------------------ ----------------------------
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE AMERICAN
NLS_CHARACTERSET WE8ISO8859P1
NLS_SORT BINARY
NLS_TIME_FORMAT HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY $
NLS_NCHAR_CHARACTERSET AL16UTF16
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE
19 rows selected.
Remarque : Différentes vues contiendront une nouvelle colonne CHARACTER_SET_NAME
qui indique le nom du jeu de caractères : CHAR_CS pour le jeu de caractères de la base de
données et NCHAR_CS pour le jeu de caractères nationaux.
Par exemple, DBA_TAB_COLUMNS crée cette colonne à partir de la vue COL$.
Présentation de l'exercice 20
Remarque : Vous pouvez réaliser cet exercice avec SQL*Plus ou avec Oracle Enterprise
Manager et SQL*Plus Worksheet.
Exécuter pupbld.sql
Le script pupbld.sql, situé dans le répertoire $ORACLE_HOME/sqlplus/admin, crée
la table Product User Profile et les procédures associées. Ce script doit être exécuté sous l'ID
utilisateur SYS. Son exécution permet, entre autres, d'éviter l'affichage d'un message
d'avertissement à chaque connexion d'un utilisateur à SQL*Plus.
$ sqlplus system/manager
SQL> @$ORACLE_HOME/sqlplus/admin/pupbld.sql
1 2
Transaction 1 Transaction 2
4 3
1 2
1 2
5 3
4 3
4
Extent inactif
6 1
1 2
5 2
6 3
4 3
Extent actif
Paramètre OPTIMAL
Le paramètre OPTIMAL définit la taille en octets à laquelle un segment d'annulation doit
revenir, si possible. Ce paramètre permet de réduire les pertes d'espace dans un segment
d'annulation. Si vous définissez le paramètre OPTIMAL, un segment d'annulation peut libérer
de l'espace lorsque les transactions qui ont provoqué l'augmentation de la taille du segment
d'annulation sont terminées.
La libération d'extents n'a pas lieu dès la fin des transactions. Elle n'est exécutée que lorsque
l'en-tête passe d'un extent à l'autre. Les extents sont libérés lorsque les deux conditions
suivantes sont vraies :
• la taille en cours du segment d'annulation est supérieure à la valeur du paramètre
OPTIMAL,
• il existe des extents contigus inactifs.
Le serveur Oracle tente de libérer des extents jusqu'à ce que la taille du segment d'annulation
soit égale à la valeur du paramètre OPTIMAL, mais il peut interrompre l'opération si l'extent
suivant à libérer est en cours d'utilisation.
Le serveur Oracle libère toujours les extents inactifs les plus anciens, parce qu'ils sont peu
susceptibles d'être utilisés pour la cohérence en lecture.
ROLLBACK_SEGMENTS=(rbs01, rbs02)
Oui
Obtention des
segments
d'annulation
publics
Mise en ligne de tous
les segments
d'annulation obtenus
• OLTP
– Des segments d'annulation petits et nombreux
– Quatre transactions par segment d'annulation
– Jusqu'à dix transactions par segment d'annulation
• Batch
– Peu de grands segments d'annulation
– Un par transaction
0.50
0.40
Probabilité
d'extension 0.30
0.20
0.10
0.00
0 10 20 30 40
Nombre d'extents
Causes possibles
Une transaction utilise un seul segment d'annulation et peut échouer si le segment d'annulation
ne contient pas suffisamment d'espace (ORA-01562). Cette situation peut avoir l'une des
origines suivantes :
• Le tablespace ne contient pas l'espace suffisant pour permettre l'extension des segments
d'annulation (ORA-01560).
• Le nombre d'extents dans le segment d'annulation a atteint la valeur du paramètre
MAXEXTENTS et il est impossible d'allouer des extents supplémentaires (ORA-01628).
Solution
Si le tablespace ne comporte pas d'espace libre, augmentez l'espace disponible :
• en définissant OPTIMAL pour éviter qu'un seul segment d'annulation n'utilise tout
l'espace libre du tablespace,
• en ramenant les segments d'annulation à leur taille optimale,
• en augmentant la taille du tablespace.
Si un segment d'annulation ne peut allouer plus d'extents, car la limite imposée par le
paramètre MAXEXTENTS a été atteinte :
• augmentez la valeur de MAXEXTENTS du segment d'annulation,
• supprimez et recréez le segment d'annulation en utilisant une plus grande taille d'extent
pour éviter que le problème ne se reproduise.
SELECT *
Table FROM table Bloc réutilisé
Nouvelle image
Image au début de l'instruction
Causes possibles
Pour assurer la cohérence en lecture, le serveur Oracle empêche l'instruction de voir les
modifications des autres utilisateurs non validées au début de son exécution ou effectuées
après le début de son exécution. Si le serveur Oracle ne peut pas créer une image des données
cohérente en lecture, l'utilisateur reçoit l'erreur ORA-01555 SNAPSHOT TOO OLD. Cette
erreur se produit lorsque la transaction qui a effectué la modification a déjà validé les données
et que :
• l'espace de transaction de l'en-tête d'annulation a été réutilisé,
• l'image avant du segment d'annulation a été remplacée par une autre transaction.
Solution
Vous pouvez réduire les erreurs de cohérence en lecture en affectant aux segments :
• une valeur MINEXTENTS plus élevée,
• une plus grande taille d'extents,
• une valeur OPTIMAL plus élevée.
Remarque : Vous ne pouvez pas éviter ces erreurs en augmentant la valeur du paramètre
MAXEXTENTS.
Session
de blocage
1 2
4 1
Extent 3
5 3
3 2
4
Extent existant
Nouvel extent
Causes possibles
Lorsqu'un extent d'un segment d'annulation est plein, le serveur Oracle tente de réutiliser
l'extent suivant du segment. Si ce dernier contient une entrée en cours, c'est-à-dire une entrée
générée par une transaction qui est toujours active, il ne peut pas être utilisé. Dans ce cas, un
segment d'annulation alloue un extent supplémentaire. La transaction ne peut pas ignorer un
extent de l'anneau et poursuit les opérations d'écriture dans un extent suivant. Une transaction
qui n'a effectué que quelques modifications et qui est inactive depuis un long moment peut
entraîner l'augmentation de la taille des segments d'annulation, même s'il existe un grand
nombre d'extents libres. Dans ce cas, un espace considérable est perdu et l'administrateur de
base de données peut intervenir pour empêcher l'augmentation excessive de la taille des
segments d'annulation.
2 Parmi les zones mémoire suivantes, laquelle ne fait pas partie de la mémoire SGA ?
a le cache de tampons de la base de données,
b la mémoire PGA,
c le tampon de journalisation,
d la zone de mémoire partagée.
Réponse : B
4 Parmi les zones mémoire suivantes, laquelle permet de mettre en mémoire cache les
informations du dictionnaire de données ?
a le cache de tampons de la base de données,
b la mémoire PGA,
c le tampon de journalisation,
d la zone de mémoire partagée.
Réponse : D
6 La mémoire PGA est une région de la mémoire qui contient les données et les
informations de contrôle de plusieurs processus serveur ou de plusieurs processus
d'arrière-plan.
a Vrai
b Faux
Réponse : Faux. Une mémoire PGA est une région de la mémoire qui contient les
données et les informations de contrôle d'un seul processus serveur ou d'un seul
processus d'arrière-plan.
7 Parmi les processus suivants, lequel ou lesquels sont disponibles au démarrage d'une
instance Oracle ?
a le processus utilisateur,
b le processus serveur,
c les processus d'arrière-plan.
Réponse : C
10 La structure physique d'une base Oracle est composée de fichiers de contrôle, de fichiers
de données et de fichiers de journalisation.
a Vrai
b Faux
Réponse : Vrai
Cet exercice s'effectue en salle. Votre formateur vous fournira les comptes de connexion et
vous aidera à vous connecter à votre compte. Notez ci-dessous les informations qu'il vous
fournira :
$ sqlplus /nolog
SQL*Plus: Release 9.0.1.0.0 - Production on Thu Nov 29
15:44:51 2001
(c) Copyright 2001 Oracle Corporation. All rights
reserved.
SQL> CONNECT / AS SYSDBA
Connected.
1 Connectez-vous à la base de données sous l'ID utilisateur SYS AS SYSDBA, puis arrêtez
la base.
2 Une fois la base de données arrêtée, créez un fichier SPFILE à partir du fichier PFILE.
Placez le fichier SPFILE dans le répertoire $HOME/ADMIN/PFILE en lui affectant
un nom au format spfileSID.ora (où SID correspond au nom de votre
instance). Créez le fichier SPFILE à partir du fichier PFILE qui se trouve dans le
répertoire $HOME/ADMIN/PFILE.
*.background_dump_dest='/u01/home/dba01/ADMIN/BDUMP'
*.compatible='9.0.0'
*.control_files='/u01/home/dba01/ORADATA/u01/ctrl01.ctl'
*.core_dump_dest='/u01/home/dba01/ADMIN/CDUMP'
*.db_block_size=4096
*.db_cache_size=4M
*.db_domain='world'
*.db_name='dba01'
*.global_names=TRUE
*.instance_name='dba01'
*.java_pool_size='0'
*.max_dump_file_size='10240'
*.remote_login_passwordfile='exclusive'
*.service_names='dba01'
*.shared_pool_size=8M
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS'
*.user_dump_dest='/u01/home/dba01/ADMIN/UDUMP'
b Connectez-vous sous l'ID utilisateur HR avec le mot de passe HR, puis insérez une
ligne dans la table REGIONS comme suit :
INSERT INTO regions VALUES (5, ‘Mars’);
Que se passe-t-il ?
HR SESSION
SQL> CONNECT hr/hr
Connected.
SQL> INSERT INTO regions VALUES (5, 'Mars');
1 row created.
b Démarrez SQL*Plus dans une nouvelle session Telnet. Connectez-vous en tant
qu'utilisateur SYS AS SYSDBA, puis procédez à un arrêt en mode
Transactional.
SYS SESSION
SQL> CONNECT / AS SYSDBA
Connected.
SQL> SHUTDOWN TRANSACTIONAL
HR SESSION
SQL> ROLLBACK;
Rollback complete.
SQL> EXIT;
ERROR:
ORA-01089: immediate shutdown in progress - no operations
are permitted
Disconnected from Oracle9i Enterprise Edition Release
9.0.0.0.0 - Beta
With the Partitioning option
JServer Release 9.0.0.0.0 - Beta (with complications)
SYS SESSION
Database closed.
Database dismounted.
SYS SESSION
SQL> CONNECT / AS SYSDBA
Connected to an idle instance.
SQL> STARTUP
ORACLE instance started.
HR SESSION
SQL> CONNECT hr/hr
Connected.
SYS SESSION
SQL> ALTER SYSTEM ENABLE RESTRICTED SESSION;
System altered.
HR SESSION
SQL> SELECT *
2 FROM regions;
REGION_ID REGION_NAME
---------- -------------------------
1 Europe
2 Americas
3 Asia
4 Middle East and Africa
SQL> EXIT
SQL> CONNECT hr/hr
ERROR:
ORA-01035: ORACLE only available to users with
RESTRICTED SESSION privilege
Warning: You are no longer connected to ORACLE.
SYS SESSION
SQL> ALTER SYSTEM DISABLE RESTRICTED SESSION;
System altered.
TABLE_NAME
------------------------------
ALL_ALL_TABLES
ALL_ARGUMENTS
ALL_ASSOCIATIONS
ALL_AUDIT_POLICIES
ALL_BASE_TABLE_MVIEWS
ALL_CATALOG
ALL_CLUSTERS
ALL_CLUSTER_HASH_EXPRESSIONS
ALL_COLL_TYPES
.
.
.
1063 rows selected.
6 rows selected.
FILE_NAME
---------------------------------------
/u01/home/dba01/ORADATA/u01/system01.dbf
11 Quel est le volume d'espace libre disponible et le volume d'espace utilisé dans la base de
données ?
Indices
- Interrogez la vue DBA_FREE_SPACE du dictionnaire de données pour afficher la
quantité d'espace disponible dans la base de données.
- Interrogez la vue DBA_SEGMENTS du dictionnaire de données pour afficher la
quantité d'espace utilisé.
Indices
- Connectez-vous sous l'ID utilisateur SYS.
- Fermez la base de données à l'aide de l'option IMMEDIATE.
- Utilisez la ligne de commande du système d'exploitation pour copier le fichier de
contrôle .ctl en lui affectant l'extension .bak.
- Supprimez le fichier de contrôle .ctl.
- Démarrez la base de données.
RECORDS_TOTAL
-------------
40
GROUP# STATUS
------ ----------------
1 ACTIVE
2 CURRENT
3 UNUSED
4 UNUSED
- continued -
GROUP# BYTES
------ ----------
3 1048576
4 1048576
b DATA02, géré localement avec des extents de taille uniforme (assurez-vous que les
tailles des extents utilisés dans le tablespace sont des multiples de 100 Ko).
TABLESPACE_NAME
------------------------------
SYSTEM
TEMP
USERS
INDX
SAMPLE
DATA01
DATA02
DATA03
UNDO2
INDEX01
10 rows selected.
- suite -
SQL> @$HOME/STUDENT/LABS/lab09_01.sql
FILE_NAME
-------------------------------------
/u01/home/dba01/ORADATA/u04/data01.dbf
SQL> @$HOME/STUDENT/LABS/lab09_05.sql
SQL> @$HOME/STUDENT/LABS/lab10_03.sql
SQL> CONNECT hr/hr
Connected.
SQL> INSERT INTO departments
2 (department_id,department_name)
3 VALUES (9999,'x');
1 row created.
4 Dans la session que vous avez ouverte sous l'ID SYS, passez dutablespace d'annulation
UNDOTBS à UNDO2 à l'aide de la commande ALTER SYSTEM.
SQL> ROLLBACK;
Rollback complete.
SQL> EXIT;
8 Dans la session que vous avez ouverte sous l'ID utilisateur SYS, supprimez le tablespace
UNDOTBS. Que se passe-t-il ?
NAME VARCHAR2(50)
REGION VARCHAR2(5)
CUST_CODE VARCHAR2(3)
DATE_OF_DELY DATE
Vous savez que des lignes seront insérées dans la table ORDERS sans valeur pour
DATE_OF_DELY et que la table sera mise à jour une fois la commande effectuée.
Utilisez le tablespace USERS et conservez, si vous le souhaitez, les paramètres de
stockage par défaut.
SQL> @$HOME/STUDENT/LABS/lab11_02.sql
3 Identifiez les fichiers et les blocs qui contiennent les lignes destinées à la table ORDERS.
Indice : Interrogez la vue DBA_EXTENTS du dictionnaire de données.
COUNT(*)
----------
1
COUNT(*)
----------
2
6 Créez une autre table, ORDERS2, à partir de la table ORDERS, mais avec le paramètre
MINEXTENTS=10. Vérifiez que la table a bien été créée avec le nombre d'extents
indiqué.
COUNT(*)
----------
10
COUNT(*)
----------
2
8 Videz la table ORDERS2 en libérant de l'espace. Combien d'extents la table comporte-t-
elle à présent ?
COUNT(*)
----------
10
9 Exécutez le script lab11_09.sql pour insérer des lignes dans la table ORDERS2.
SQL> @$HOME/STUDENT/LABS/lab11_09.sql
SQL> @$HOME/STUDENT/LABS/lab12_05a.sql
NO OE
---------- ----------
10000 2
c En utilisant des tailles d'extent uniformes de 4 Ko, créez les index B-Tree
NUMB_OE_IDX et NUMB_NO_IDX, respectivement sur les colonnes ODD_EVEN et
NO de la table NUMBERS. Placez ces index dans le tablespace INDEX01. Vérifiez la
taille totale des index et écrivez ci-dessous le nombre de blocs.
Indice : Utilisez une valeur PCTINCREASE égale à zéro pour créer des extents de
taille identique. Vérifiez dans DBA_SEGMENTS le nombre total de blocs alloués aux
extents.
NUMB_OE_IDX ODD_EVEN
NUMB_NO_IDX NO
SEGMENT_NAME BLOCKS
--------------- ----------
NUMB_OE_IDX 40
NUMB_NO_IDX 46
NUMB_OE_IDX ODD_EVEN
NUMB_NO_IDX NO
SEGMENT_NAME BLOCKS
--------------- ----------
NUMB_OE_IDX 2
NUMB_NO_IDX 72
SQL> @$HOME/STUDENT/LABS/lab13_01.sql
6 rows selected.
3 Sous l'ID utilisateur SYSTEM, exécutez le script lab13_03.sql afin d'insérer deux
enregistrements dans la table PRODUCTS.
SQL> @$HOME/STUDENT/LABS/lab13_03.sql
5 a Assurez-vous que les nouvelles lignes ajoutées à la table ne violent pas la contrainte
portant sur la table PRODUCT.
Indice : Pour ce faire, activez la contrainte NOVALIDATE.
6 Effectuez les opérations requises pour identifier les cas de violation de contrainte
détectés dans la table PRODUCTS, modifiez les codes produit (PRODUCT_ID) en
conséquence, puis assurez-vous que toutes les données, nouvelles ou existantes, sont
conformes à la contrainte (considérez que la table comporte plusieurs milliers de lignes et
qu'il est bien trop long de vérifier chacune d'elles manuellement).
Indice : Procédez comme suit :
a Créez la table EXCEPTIONS.
e Activez la contrainte.
SQL> @$HOME/STUDENT/LABS/lab13_07.sql
SQL> INSERT INTO system.orders
2 VALUES (800,'01-JAN-98','J01',NULL);
INSERT INTO system.orders
*
ERROR at line 1:
ORA-02291: integrity constraint
(SYSTEM.ORDERS_CUST_CODE_FK) violated - parent key not
found
SQL> INSERT INTO system.customers
2 VALUES ('J01','Sports Store', 'East');
1 row created.
SQL> ROLLBACK;
Rollback complete.
SQL> @$HOME/STUDENT/LABS/lab14_01.sql
SQL> @$ORACLE_HOME/rdbms/admin/utlpwdmg.sql
2 Tentez de remplacer le mot de passe de l'utilisateur Jeff par Jeff. Que se passe-t-il ?
7 rows selected.
Profile altered.
8 Connectez-vous sous l'ID Jeff en entrant un mot de passe non valide. Après deux
tentatives, connectez-vous avec le mot de passe correct. Que se passe-t-il ? Pourquoi ?
6 Sous l'ID utilisateur SYSTEM, supprimez le quota de Bob sur son tablespace par défaut.
8 Bob a oublié son mot de passe. Affectez à Bob le mot de passe OLINK et demandez-lui
de le modifier lors de sa prochaine connexion.
SQL> @$HOME/STUDENT/LABS/lab16_02a.sql;
SQL> CONNECT emi/abcd12
Connected.
CREATE TABLE customers1 (cust_code VARCHAR2(3),
name VARCHAR2(50),
region VARCHAR2(5) )
TABLESPACE data01;
CUS NAME
REGIO
--- -------------------------------------------------- ---
--
A01 TKB SPORT SHOP
West
A02 VOLLYRITE
North
A03 JUST TENNIS
North
A04 EVERY MOUNTAIN
South
A05 SHAPE UP
South
A06 SHAPE UP
West
A07 WOMENS SPORTS
South
c Connectez-vous en tant que SYSTEM, puis autorisez Bob à sélectionner des données
A08 NORTH
dans la WOODS HEALTH
table CUSTOMERS1 AND Que
d'Emi. FITNESS SUPPLY
se passe-t-il ? CENTER
East
J01 Sports Store
East
SQL> CONNECT system/manager
9 rows selected.
Connected.
SQL> GRANT select ON emi.customers1 TO bob;
GRANT select ON emi.customers1 TO bob
*
ERROR at line 1:
ORA-01031: insufficient privileges
6 Autorisez Emi à démarrer et arrêter la base de données, mais pas à en créer une
nouvelle.
SQL> CONNECT / AS SYSDBA
Connected.
SQL> GRANT sysoper TO emi;
Grant succeeded.
5 Sous l'ID utilisateur SYSTEM, tentez de créer la vue CUST_VIEW dans la table
CUSTOMERS d'Emi. Que se passe-t-il ?
View created.
$ cd $HOME/STUDENT/LABS
$ sqlldr hr/hr control=lcase1.ctl log=$HOME/lcase1.log
SQL*Loader: Release 9.0.1.0.0 - Production on Fri Oct 5
23:18:29 2001
(c) Copyright 2001 Oracle Corporation. All rights
reserved.
Commit point reached - logical record count 27
...
$ cd $HOME/STUDENT/LABS
$ sqlldr hr/hr control=lcase2.ctl direct=true
log=$HOME/STUDENT/LABS/lcase2.log
SQL*Loader: Release 9.0.1.0.0 - Production on Fri Oct 5
10:40:10 2001
(c) Copyright 2001 Oracle Corporation. All rights
reserved.
Load completed - logical record count 27.
...
$ more lcase2.log
-- Note: Path used: Direct
b Restaurez les données à l'aide d'un chargement par chemin direct en parallèle depuis la
table EMPLOYEES. Définissez un degré de parallélisme de deux. Vérifiez les
données.
SQL> ALTER SESSION ENABLE PARALLEL DML;
Session altered
SQL> INSERT /*+ parallel(employees2,2) */
2 INTO employees2 NOLOGGING
3 SELECT * FROM employees;
107 rows created.
SQL> COMMIT;
Commit complete.
SQL> SELECT employee_id, first_name, last_name
2 FROM employees2;
EMPLOYEE_ID FIRST_NAME LAST_NAME
---------- ----------------- -------------------------
139 John Seo
140 Joshua Patel
141 Trenna Rajs
142 Curtis Davies
…
107 rows selected.
PARAMETER VALUE
------------------------------ --------------
NLS_CHARACTERSET WE8ISO8859P1
NLS_NCHAR_CHARACTERSET AL16UTF16
2 Quelles sont les valeurs valides pour le jeu de caractères de la base de données ?
VALUE
-------------------
AL16UTF16
AL24UTFFSS
AL32UTF8
AR8ADOS710
AR8ADOS710T
AR8ADOS720
AR8ADOS720T
.
.
.
2 Parmi les zones mémoire suivantes, laquelle ne fait pas partie de la mémoire SGA ?
a le cache de tampons de la base de données,
b la mémoire PGA,
c le tampon de journalisation,
d la zone de mémoire partagée.
Réponse : B
4 Parmi les zones mémoire suivantes, laquelle permet de mettre en mémoire cache les
informations du dictionnaire de données ?
a le cache de tampons de la base de données,
b la mémoire PGA,
c le tampon de journalisation,
d la zone de mémoire partagée.
Réponse : D
6 La mémoire PGA est une région de la mémoire qui contient les données et les informations
de contrôle de plusieurs processus serveur ou de plusieurs processus d'arrière-plan.
a Vrai
b Faux
Réponse : Faux. Une mémoire PGA est une région de la mémoire qui contient les
données et les informations de contrôle d'un seul processus serveur ou d'un seul processus
d'arrière-plan.
10 La structure physique d'une base Oracle est composée de fichiers de contrôle, de fichiers de
données et de fichiers de journalisation.
a Vrai
b Faux
Réponse : Vrai
Cet exercice s'effectue en salle. Votre formateur vous fournira les comptes de connexion et
vous aidera à vous connecter à votre compte. Notez ci-dessous les informations qu'il vous
fournira :
$ sqlplus /nolog
SQL*Plus: Release 9.0.1.0.0 - Production on Thu Nov 29
15:44:51 2001
(c) Copyright 2001 Oracle Corporation. All rights
reserved.
SQL> CONNECT / AS SYSDBA
Connected.
1 Connectez-vous à la base de données sous l'ID utilisateur SYS, puis arrêtez la base.
Remarque : Bien que la console OEM puisse être utilisée pour démarrer et arrêter la
base de données, vous utiliserez SQL*Plus dans le cadre de ce cours. Les instructions
suivantes ont été fournies uniquement pour votre information.
2 Une fois la base de données arrêtée, créez un fichier SPFILE à partir du fichier PFILE.
Placez le fichier SPFILE dans le répertoire $HOME/ADMIN/PFILE en lui affectant
un nom au format spfileSID.ora (où SID correspond au nom de votre
instance). Créez le fichier SPFILE à partir du fichier PFILE qui se trouve dans le
répertoire $HOME/ADMIN/PFILE.
$ more $HOME/ADMIN/PFILE/spfile$ORACLE_SID.ora
*.background_dump_dest='/u01/home/dba01/ADMIN/BDUMP'
*.compatible='9.0.0'
*.control_files='/u01/home/dba01/ORADATA/u01/ctrl01.ctl'
*.core_dump_dest='/u01/home/dba01/ADMIN/CDUMP'
*.db_block_size=4096
*.db_cache_size=4M
*.db_domain='world'
*.db_name='dba01'
*.global_names=TRUE
*.instance_name='dba01'
*.java_pool_size='0'
*.max_dump_file_size='10240'
*.remote_login_passwordfile='exclusive'
*.service_names='dba01'
*.shared_pool_size=8M
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS'
*.user_dump_dest='/u01/home/dba01/ADMIN/UDUMP'
Remarque : Bien que la console OEM puisse être utilisée pour démarrer et arrêter la
base de données, vous utiliserez SQL*Plus dans le cadre de ce cours. Les instructions
suivantes ont été fournies uniquement pour votre information.
Remarque : Bien que la console OEM puisse être utilisée pour démarrer et arrêter la
base de données, vous utiliserez SQL*Plus dans le cadre de ce cours. Les instructions
suivantes ont été fournies uniquement pour votre information.
HR SESSION
CONNECT hr/hr@[service name]
Connected.
INSERT INTO regions VALUES (5, 'Mars');
1 row created.
SYS SESSION
SQL> CONNECT / AS SYSDBA
Connected.
SQL> SHUTDOWN TRANSACTIONAL
HR SESSION
ROLLBACK;
Rollback complete.
EXIT;
ERROR:
ORA-01089: immediate shutdown in progress - no operations
are permitted
Disconnected
SYS SESSION
Database closed.
Database dismounted.
SYS SESSION
SQL> CONNECT / AS SYSDBA
Connected to an idle instance.
SQL> STARTUP
ORACLE instance started.
HR SESSION
SQL> CONNECT hr/hr
Connected.
c Sous l'ID utilisateur SYS, activez la session en mode restreint.
SYS SESSION
SQL> ALTER SYSTEM ENABLE RESTRICTED SESSION;
System altered.
HR SESSION
SQL> SELECT *
2 FROM regions;
REGION_ID REGION_NAME
---------- -------------------------
1 Europe
2 Americas
3 Asia
4 Middle East and Africa
SQL> EXIT
SQL> CONNECT hr/hr
ERROR:
ORA-01035: ORACLE only available to users with RESTRICTED
SESSION privilege
Warning: You are no longer connected to ORACLE.
SYS SESSION
SQL> ALTER SYSTEM DISABLE RESTRICTED SESSION;
System altered.
Remarque : Bien que la console OEM puisse être utilisée pour placer la base de données
en mode d'accès restreint, vous utiliserez SQL*Plus dans le cadre de ce cours. Les
instructions suivantes ont été fournies uniquement pour votre information.
TABLE_NAME
------------------------------
ALL_ALL_TABLES
ALL_ARGUMENTS
ALL_ASSOCIATIONS
ALL_AUDIT_POLICIES
ALL_BASE_TABLE_MVIEWS
ALL_CATALOG
ALL_CLUSTERS
ALL_CLUSTER_HASH_EXPRESSIONS
ALL_COLL_TYPES
.
.
.
1063 rows selected.
6 rows selected.
FILE_NAME
---------------------------------------
/u01/home/dba01/ORADATA/u01/system01.dbf
11 Quel est le volume d'espace libre disponible et le volume d'espace utilisé dans la base de
données ?
Indices
- Interrogez la vue DBA_FREE_SPACE du dictionnaire de données pour afficher la
quantité d'espace disponible dans la base de données.
- Interrogez la vue DBA_SEGMENTS du dictionnaire de données pour afficher la
quantité d'espace utilisé.
9 rows selected.
Indices
- Connectez-vous sous l'ID utilisateur SYS.
- Fermez la base de données à l'aide de l'option IMMEDIATE.
- Utilisez la ligne de commande du système d'exploitation pour copier le fichier de
contrôle .ctl en lui affectant l'extension .bak.
- Supprimez le fichier de contrôle .ctl.
- Démarrez la base de données.
Remarque : Pour les besoins de cet exercice réalisé en salle, fermez la base de
données à l'aide de SQL*Plus.
Utiliser SQL*Plus
$ cp $HOME/ORADATA/u01/ctrl01.ctl
$HOME/ORADATA/u01/ctrl01.bak
$ rm $HOME/ORADATA/u01/ctrl01.ctl
Utiliser SQL*Plus
SQL> STARTUP
ORACLE instance started.
Total System Global Area 21790412 bytes
Fixed Size 278220 bytes
Variable Size 16777216 bytes
Database Buffers 4194304 bytes
Redo Buffers 540672 bytes
ORA-00205: error in identifying controlfile, check alert
log for more info
Utiliser SQL*Plus
cp $HOME/ORADATA/u01/ctrl01.bak
$HOME/ORADATA/u01/ctrl01.ctl
Utiliser SQL*Plus
SQL> STARTUP
ORACLE instance started.
Utiliser SQL*Plus
$ cp $HOME/ORADATA/u01/ctrl01.ctl
$HOME/ORADATA/u02/ctrl02.ctl
$ chmod 660 $HOME/ORADATA/u02/ctrl02.ctl
Utiliser SQL*Plus
SQL> STARTUP
ORACLE instance started.
Database mounted.
Database opened.
SELECT records_total
FROM v$controlfile_record_section
WHERE type = 'DATAFILE';
RECORDS_TOTAL
-------------
40
$ rm $HOME/ORADATA/u03/log03a.rdo
$ rm $HOME/ORADATA/u04/log03b.rdo
GROUP# STATUS
------ ----------------
1 ACTIVE
2 CURRENT
3 UNUSED
4 UNUSED
- continued -
- continued -
GROUP# BYTES
------ ----------
3 1048576
4 1048576
Utiliser la console O
- Sélectionnez Storage > Tablespaces.
- Utilisez la commande Create dans le menu accessible par le bouton droit de la
souris pour ajouter le tablespace.
TABLESPACE_NAME
-----------------------
SYSTEM
TEMP
USERS
INDX
SAMPLE
DATA01
DATA02
DATA03
UNDO2
INDEX01
10 rows selected.
ALTER DATABASE
DATAFILE '$HOME/ORADATA/u03/data02.dbf' RESIZE 1500K;
Database altered.
$ mv $HOME/ORADATA/u02/index01.dbf
$HOME/ORADATA/u06/index01.dbf
3 (suite)
ls $HOME/ORADATA/u01/*
/u01/home/dba01/ORADATA/u01/ctrl01.bak
/u01/home/dba01/ORADATA/u01/querydata01.dbf
/u01/home/dba01/ORADATA/u01/ctrl01.ctl
/u01/home/dba01/ORADATA/u01/system01.dbf
ls $HOME/ORADATA/u05
Utiliser la console OEM
ora_data03_xg17n9nd.dbf
- Sélectionnez Instance > Configuration.
- Sélectionnez ALL INITIALIZATION PARAMETERS.
- Vérifiez la sélection du bouton radio Running.
- Accédez à DB_CREAT_FILE_DEST, puis affectez-lui la valeur u05.
- Cliquez sur OK.
- Une boîte de dialogue indiquant la modification des paramètres s'affiche. Cliquez sur
OK.
- Utilisez SQL*Plus Worksheet plutôt que la console OEM pour créer le tablespace.
Oracle pourra ainsi indiquer l'emplacement et le nom d'Oracle-Managed File.
FILE_NAME
-------------------------------------
/u01/home/dba01/ORADATA/u04/data01.dbf
SELECT segment_name
FROM dba_rollback_segs
WHERE tablespace_name = 'UNDO2';
SEGMENT_NAME
--------------------
_SYSSMU9$
_SYSSMU10$
_SYSSMU11$
_SYSSMU12$
_SYSSMU13$
_SYSSMU14$
_SYSSMU15$
_SYSSMU16$
8 rows selected.
4 Dans la session que vous avez ouverte sous l'ID SYS, passez du tablespace d'annulation
UNDOTBS à
UNDO2 à l'aide de la commande ALTER SYSTEM.
SELECT segment_name
FROM dba_rollback_segs
WHERE tablespace_name = 'UNDOTBS';
SEGMENT_NAME
--------------------
_SYSSMU1$
_SYSSMU2$
_SYSSMU3$
_SYSSMU4$
_SYSSMU5$
_SYSSMU6$
_SYSSMU7$
_SYSSMU8$
8 rows selected.
SELECT a.usn,a.name,b.status
FROM v$rollname a, v$rollstat b
WHERE a.name
IN ( SELECT segment_name
FROM dba_segments
WHERE tablespace_name = 'UNDOTBS‘ )
AND a.usn = b.usn;
ROLLBACK;
Rollback complete.
EXIT;
8 Dans la session que vous avez ouverte sous l'ID utilisateur SYS, supprimez le tablespace
UNDOTBS. Que se passe-t-il ?
NAME VARCHAR2(50)
REGION VARCHAR2(5)
CUST_CODE VARCHAR2(3)
DATE_OF_DELY DATE
Vous savez que des lignes seront insérées dans la table ORDERS sans valeur pour
DATE_OF_DELY et que la table sera mise à jour une fois la commande effectuée.
Utilisez le tablespace USERS et conservez, si vous le souhaitez, les paramètres de
stockage par défaut.
2 Exécutez le script lab11_02.sql pour insérer des lignes dans les tables.
3 Identifiez les fichiers et les blocs qui contiennent les lignes destinées à la table ORDERS.
Indice : Interrogez la vue DBA_EXTENTS du dictionnaire de données.
SELECT count(*)
FROM dba_extents
WHERE segment_name='ORDERS'
AND owner='SYSTEM';
COUNT(*)
----------
1
5 Allouez manuellement à la table ORDERS un extent possédant la taille par défaut, puis
vérifiez que l'opération a abouti.
SELECT count(*)
FROM dba_extents
WHERE segment_name='ORDERS'
AND owner='SYSTEM';
COUNT(*)
----------
2
6 Créez une autre table, ORDERS2, à partir de la table ORDERS, mais avec le paramètre
MINEXTENTS=10. Vérifiez que la table a bien été créée avec le nombre d'extents
indiqué.
SELECT count(*)
FROM dba_extents
WHERE segment_name='ORDERS2'
AND owner='SYSTEM';
COUNT(*)
----------
10
Remarque : Vous devez vous connecter à la console OEM en tant que SYSTEM pour ne
pas générer d'erreur.
SELECT count(*)
FROM dba_extents
WHERE segment_name='ORDERS'
AND owner='SYSTEM';
COUNT(*)
----------
2
SELECT count(*)
FROM dba_extents
WHERE segment_name='ORDERS2'
AND owner='SYSTEM';
COUNT(*)
----------
10
DESCRIBE orders2;
Name Null? Type
-------------------------- -------- ------------------
ORD_ID NUMBER(3)
ORD_DATE DATE
CUST_CODE VARCHAR2(3)
DATE_OF_DELY DATE
DESCRIBE orders2;
Name Null? Type
-------------------------- -------- ------------------
ORD_ID NUMBER(3)
ORD_DATE DATE
CUST_CODE VARCHAR2(3)
Remarque : Cliquez sur le bouton radio Bitmap pour créer l'index bitmap, sinon un
index B*tree est créé par défaut.
3 Notez les fichiers et les blocs utilisés par les extents dans l'index CUST_REGION_IDX.
Indice : Accédez à ces informations par la vue DBA_EXTENTS.
NO OE
---------- ----------
10000 2
c En utilisant des tailles d'extent uniformes de 4 Ko, créez les index B-Tree
NUMB_OE_IDX et NUMB_NO_IDX, respectivement sur les colonnes ODD_EVEN et
NO de la table NUMBERS. Placez ces index dans le tablespace INDEX01. Vérifiez la
taille totale des index et écrivez ci-dessous le nombre de blocs.
Indice : Utilisez une valeur PCTINCREASE égale à zéro pour créer des extents de
taille identique. Vérifiez dans DBA_SEGMENTS le nombre total de blocs alloués aux
extents.
NUMB_OE_IDX ODD_EVEN
NUMB_NO_IDX NON
NUMB_OE_IDX ODD_EVEN
NUMB_NO_IDX NON
Utiliser SQL*Plus Worksheet
- Entrez la commande SQL.
- Sélectionnez Execute.
6 rows selected.
Oracle9i Database Administration I D-69
Exercice 13 (suite)
2 b Vérifier le nom et le type des index créés pour valider les contraintes.
Indice : Les index ne sont créés que pour les contraintes de clé primaire et de clé
unique. Ils portent le même nom que ces contraintes.
SELECT index_name,table_name,uniqueness
FROM dba_indexes
WHERE index_name in
( SELECT constraint_name
FROM dba_constraints
WHERE table_name IN ('PRODUCTS', 'ORDERS', 'CUSTOMERS')
AND owner='SYSTEM'
AND constraint_type in ('P','U') ) ;
INDEX_NAME TABLE_NAME UNIQUENES
------------------------------ ---------- ---------
CUSTOMERS_CUST_CODE_PK CUSTOMERS NONUNIQUE
ORDERS_ORD_ID_PK ORDERS UNIQUE
3 Sous l'ID utilisateur SYSTEM, exécutez le script lab13_03.sql afin d'insérer deux
enregistrements dans la table PRODUCTS.
5 a Assurez-vous que les nouvelles lignes ajoutées à la table ne violent pas la contrainte
portant sur la table PRODUCT.
Indice : Pour ce faire, activez la contrainte NOVALIDATE.
Utiliser SQL*Plus
c Utilisez les ROWID dans la table EXCEPTIONS pour afficher les lignes de la table
PRODUCTS qui violent la contrainte (ne demandez pas l'affichage des colonnes
LOB).
e Activez la contrainte.
Utiliser SQL*Plus
SQL> @$ORACLE_HOME/rdbms/admin/utlpwdmg.sql
2 Tentez de remplacer le mot de passe de l'utilisateur Jeff par Jeff. Que se passe-t-il ?
3 Essayez de remplacer le mot de passe de Jeff pour qu'il respecte le format de gestion
des mots de passe.
Indice : Le mot de passe doit contenir au moins un chiffre, un caractère et un signe de
ponctuation.
4 Modifiez le profil DEFAULT pour que les paramètres suivants s'appliquent aux
utilisateurs possédant ce profil :
- le compte doit être verrouillé après deux tentatives de connexion,
- le mot de passe doit expirer après un délai de 30 jours,
- le même mot de passe ne doit pas être réutilisé pendant au moins une minute,
- le compte doit bénéficier d'une période de grâce de cinq jours pour la
modification d'un mot de passe qui a expiré.
- Assurez-vous que ces exigences sont satisfaites.
Indices
Modifiez les limites du profil par défaut à l'aide de la commande ALTER
PROFILE.
Interrogez la vue DBA_PROFILES du dictionnaire de données pour vérifier les
résultats.
7 rows selected.
CONNECT jeff/super1$
Connected.
Profile altered.
Réponse : Le compte ne se verrouille pas si vous vous connectez avec le mot de passe
correct à la troisième tentative (voir étape 5). En effet, lorsque vous avez affecté la
valeur NULL à la fonction PASSWORD_VERIFY_FUNCTION, celle-ci a été
désactivée à l'étape 7.
Bien que la console OEM puisse être utilisée pour modifier le tablespace TEMPORARY
de Bob, utilisez SQL*Plus Worksheet dans cette étape afin de recevoir l'erreur générée
lorsque vous vous connectez sous l'ID utilisateur Bob.
Bien que la console OEM puisse être utilisée pour modifier le mode de passe de Bob,
utilisez SQL*Plus Worksheet dans cette étape afin de montrer qu'aucune erreur ne peut
être générée puisque Bob peut modifier son mot de passe.
6 Sous l'ID utilisateur SYSTEM, supprimez le quota de Bob sur son tablespace par défaut.
8 Bob a oublié son mot de passe. Affectez à Bob le mot de passe OLINK et demandez-lui
de le modifier lors de sa prochaine connexion.
9 rows selected.
Remarque : Vous recevez une erreur indiquant que votre privilège est insuffisant.
CONNECT system/manager
Connected.
6 Autorisez Emi à démarrer et arrêter la base de données, mais pas à en créer une
nouvelle.
Remarque : Lorsque vous utilisez la console OEM, ne vous connectez pas sous l'ID
utilisateur Emi.
Remarque : Vous recevez une erreur ORA-01031, car en tant que SYSTEM, vous ne
possédez pas les privilèges suffisants pour réaliser cette opération.
$ more lcase1.log
-- Note: Path used: Conventional
$ more lcase2.log
-- Note: Path used: Direct
COMMIT;
b Restaurez les données à l'aide d'un chargement par chemin direct en parallèle depuis la
table EMPLOYEES. Définissez un degré de parallélisme de deux. Vérifiez les
données.
COMMIT;
Commit complete.
2 Quelles sont les valeurs valides pour le jeu de caractères de la base de données ?
Sommaire
Préface
I Introduction
Objectifs du cours I-2
Oracle9i Enterprise Edition I-3
Tâches de l'administrateur de base de données I-4
iii
iv
5 Utiliser les vues du dictionnaire de données et les vues dynamiques des performances
Objectifs 5-2
Objets de base de données intégrés 5-3
Dictionnaire de données 5-4
Tables de base et vues du dictionnaire de données 5-5
Créer des vues du dictionnaire de données 5-6
Contenu du dictionnaire de données 5-7
Utilisation du dictionnaire de données 5-8
Catégories de vues du dictionnaire de données 5-9
Exemples de vues du dictionnaire de données 5-11
Tables dynamiques des performances 5-12
Exemples de tables dynamiques des performances 5-13
Conventions d'appellation des scripts d'administration 5-15
Synthèse 5-46
Présentation de l'exercice 5 5-17
vi
vii
viii
ix
18 Fonction d'audit
Objectifs 18-2
Fonction d'audit 18-3
Instructions relatives à l'audit 18-4
xi
xii
xiii