administration+oracle7

Administration d'une base de donnes Oracle7 Administration

----------------------------------------------------------------------------------------------------------------------------------------

Universit de Nice-Sophia Antipolis 2 JC Grattarola

ADMINISTRATION D'UNE BASE DE DONNEES

I-ADMINISTRATION D'UNE BASE DE DONNEES

Les principales tches dun administrateur dune base de donnes sont les suivantes:

* Installation et mise jour du noyau serveur et des outils d'application* Planification des ressources de mmorisation des donnes* Organisation des structures logiques et physiques des donnes* Cration et gestion des utilisateurs et de leurs droits daccs (privilges)* Gestion et optimisation des performances du systme* Gestion de la scurit du systme: gestion des accs concurrents* Gestion de la scurit du systme: sauvegardes, restaurations et archivages de la base* Gestion de bases de donnes rparties

Les autres utilisateurs assurent les tches suivantes:

Dveloppeurs dapplication:* Conception et ralisation dune application* Conception de la structure de la base de donnes* Evaluation des besoins en ressources de mmorisation* Optimisation des performances de lapplication* Etablissement des mesures de scurit

Utilisateurs dapplication* Saisie, modification et suppression de donnes* Gnration des tats de sortie

Pour raliser les tches qui lui sont dvolues, ladministrateur de la base dispose

* de deux comptes spciaux: SYS et SYSTEM crs en mme temps que la base dedonnes.

SYS est propritaire des tables et des vues du dictionnaire de donnes. Ces tables et ces vues,essentielles pour le fonctionnement du serveur, ne peuvent tre modifies que par Oracle7 lui-mme.SYSTEM cre les tables et les vues qui fournissent les informations ncessaires l'administration du sytme; il cre les tables et les vues utilises par les outils Oracle.

SYS et SYSTEM possdent le rle OSDBA; ils disposent de tous les privilges Systme quileur permettent d'excuter toutes les oprations ncessaires au fonctionnement d'une instance.


----------------------------------------------------------------------------------------------------------------------------------------


* doutils spcifiques:

a/ SERVER MANAGER:outil dadministration et de contrle de la base de donnesqui permet de

* dmarrer et arrter une instance* monter, dmonter, ouvrir et fermer une instance* contrler en temps rel lutilisation et les performances du serveur* raliser des sauvegardes et des restaurations* excuter des commandes SQL et PL/SQL

Server manager est appel par les commandes:

* svrmgrl : mode caractres* svrmgrm: mode graphique (motif)

svrmgrl permet d'excuter les commandes suivantes:

Dmarrage d'une instanceSTARTUP [RESTRICT] [FORCE] [PFILE=filename] [NOMOUNT | MOUNT [EXCLUSIVE | {PARALLEL | SHARED} [RETRY]] | OPEN [RECOVER] [dbname] [EXCLUSIVE | {PARALLEL | SHARED} [RETRY]]]Arrt d'une instanceSHUTDOWN [NORMAL | IMMEDIATE | ABORT | dbname]Activation du module MonitorMONITOR { FILE | PROCESS | IO | LATCH | LOCK | ROLLBACK | SESSION | STATISTIC | TABLE }Activation ou dsactivation de l'archivage automatiqueARCHIVE LOG {{STOP|LIST}|{START|NEXT||ALL}[TO 'destination']}Restauration dune base ou de tablespacesRECOVER { [DATABASE [UNTIL {CANCEL | CHANGE integer | TIME

date}] [USING BACKUP CONTROL FILE]] | [TABLESPACE ts-name [,tsname]] | [DATAFILE 'filename' [,'filename']]}Connexion la baseCONNECT {[username [/password] ]|[INTERNAL]} ['@'instance-spec]Dconnexion de la baseDISCONNECT


----------------------------------------------------------------------------------------------------------------------------------------


Affectation de valeurs des variables systmeSET options: ARRAYSIZE, AUTORECOVERY, CHARWIDTH, COMPATIBILITY CYCLE, DATEWIDTH, ECHO, FETCHROWS, HISTORY, INSTANCE, LABWIDTH, LINES, LOGSOURCE, LONGWIDTH, MAXDATA, NUMWIDTH, RETRIES, SERVER OUTPUT, SPOOL, STOPONERROR, TERM, TERMOUT, TIMING

Affichage des valeurs de variables systmeSHOW options: same as SET plus ALL, ERRORS, LABEL, PARAMETERS, SGA and VAR

Sortie de Server ManagerEXIT

Introduction d'un commentaire dans un script SQLREMARK

Excution d'une commande PL/SQLEXECUTE pl/sql_block

Desciption d'un objet de la baseDESCRIBE {table_name | view_name | proc_name | package_name |

function_name }

Excution d'une commande systmeHOST [os_command]

Impression de la valeur d'une variable dfinie avec la commande VARIABLEPRINT variable

Activation ou dsactivation d'un fichier de spoolingSPOOL [filename | OFF]Dclaration d'une variable, utilisable avec les commandes EXECUTE ou PRINTVARIABLE type name

Excution de script SQL ou PL/SQL@ script name

b/ SQL*LOADER: cet utilitaire permet de

* charger dans la bases des donnes, ayant des formats divers, provenant defichiers externes.

* manipuler des champs de donnes avant leur insertion dans la base (contrles de validit)* distribuer des enregistrements dun fichier dans plusieurs tables* transformer plusieurs enregistrements physiques en un enregistrement logique


----------------------------------------------------------------------------------------------------------------------------------------


c/ EXPORT et IMPORT Utilities: utilitaires ralisant:

* larchivage de donnes* le transfert de donnes entre bases Oracle* le stockage de donnes dans des fichiers externes la base* le stockage des dfinitions dobjets (tables, clusters, index) avec ou sans les donnes* la sauvegarde des seules tables modifies depuis le dernier export (export incrmental ou cumulatif)* la restauration de donnes accidentellement supprimes

d/ ENTERPRISE MANAGER

Outil graphique dAdministration de Bases de Donnes permettant de raliser, partir dunposte de travail Windows NT, les tches suivantes :

-Administration, diagnostic, optimisation de plusieurs bases-Distribution de software des postes clients-Programmation de Jobs sexcutant intervalles rguliers-Gestion dvnements travers le rseau

SQL*LOADER,EXPORT-IMPORT et ENTERPRISE MANAGER sont prsents au chapitreVIII.

II-AUTHENTIFICATION DUN ADMINISTRATEUR

Ladministrateur dune base de donnes doit raliser des oprations particulires commelouverture ou la fermeture de linstance ; il doit pour cela bnficier de privilges spciaux.Lidentification et lauthentification dun administrateur rpondent ainsi des rgles descurit trs strictes ; elles peuvent se faire de deux manires diffrentes :

-Authentification Systme-Utilisation dun fichier Password

Authentification Systme

Sur la plupart des systmes dexploitation, lauthentification systme impose de placer lelogin OS de ladministrateur dans un groupe spcial (groupe dba sous Unix).Le paramtre dinitialisation remote_login_password doit tre gal NONE.


----------------------------------------------------------------------------------------------------------------------------------------


Fichier Password

Le fichier password est utilis pour authentifier les utilisateurs possdant les privilgesSYSOPER ou SYSDBA qui permettent dexcuter, sous svrmgrl, les commandes suivantes :

SYSOPER: STARTUP, SHUTDOWON, ALTERDATABASE OPEN/MOUNT, ALTERDATABASE BACKUP, ARCHIVELOG, RECOVER

SYSDBA : STARTUP, SHUTDOWON, ALTERDATABASE OPEN/MOUNT, ALTERDATABASE BACKUP, ARCHIVELOG, RECOVER avec loption WITH ADMIN OPTIONet CREATE DATABASE

Un fichier password est cr laide de la commandeorapwd FILE=filename,PASSWORD=password,ENTRIES=max_users(password :valeur du password pour sys et internal)

Le paramtre dinitialisation remote_login_password doit tre gal EXCLUSIVE

Latribution des privilges SYSOPER ou SYSDBA (ou des rles OSOPER ou OSDBA) unutilisateur ajoute cet utilisateur au fichier password. Elle se fait par un utilisateur ayant cesprivilges (SYS ou SYSTEM connects sous svrmgrl avec le privilge SYSDBA).

SvrmgrlSVRMGRL> connect systeme/password as SYSDBA

La liste des utilisateurs possdant les privilges SYSDBA ou SYSOPER peut tre visualise laide de la commande

Select username, sysdba,sysoper from v$pwfile_user

Administration d'une base de donnes Oracle7 Architecture

----------------------------------------------------------------------------------------------------------------------------------------


Une instance est constitue de trois types d'lments:

-System Global Area (SGA): ensemble des buffers ncessaires la gestion destransactions

-Process: ensemble des processus Systme et des processus Utilisateurs-Files: ensembles des fichiers contenant les informations

ARCHITECTURE DUNE BASE DE DONNEES

Data BaseBuffer Cache

Zone de partagedes ordres SQL

BufferRedo Log

System Global Area

PMON SMON

Serveurddi

DBWR LGWR ARCH

UserProcess

Fichiers Database Fichiers Redo Log

Fichiers de contrle

FichiersRedo LogArchivs

Serveurpartag

UserProcessUser

Process


----------------------------------------------------------------------------------------------------------------------------------------


I-SYSTEM GLOBAL AREA

Oracle cre et utilise des structures mmoire rassembles dans la System Global Area (SGA),partages par les diffrents utilisateurs.La SGA et les processus background constituent une instance; lespace mmoire ncessaire la SGA est allou au dmarrage dune instance et est restitu la fermeture de cette instance.Les donnes de la SGA sont partages par lensemble des utilisateurs connects un momentdonn; elles sont divises en plusieurs types de buffers:

* Data base Buffer Cache: Il contient les blocs de donnes, les blocs dindex,desblocs contenant les ROLLBACK SEGMENTS et des blocs pour la gestion du systme, lesplus rcemment utiliss; il peut contenir des donnes modifies qui nont pas encore tenregistres sur disque.

* Redo Log Buffer: Il contient les redo entries (toutes les donnes avant leur mise jour,toutes les modifications effectues sur ces donnes,la trace de toutes les transactionsvalides ou non encore valides),ensemble des modifications ralises sur la base; ces redoentries sont mmorises sur un redo log file, qui pourra tre utilis en cas de panne.

* Zone de partage des ordres SQL: cette zone est utilise pour mmoriser,analyser ettraiter les ordres SQL soumis par les utilisateurs

II-LES PROCESSUS

Une base Oracle contient deux types de processus:

* User Process* Oracle Process

Un User Process est cr et maintenu pour excuter le code dun programme applicatif(ex:application Oracle Forms) ou d'un outil Oracle (ex: Server Manager); le User processcommunique avec les Process Server travers le programme interface.

Les Oracle Process sont diviss en deux catgories:

* Process Server qui prennent en charge les demandes des utilisateursLe Process Server est responsable de la communication entre la SGA et le Process User; ilanalyse et excute les ordres SQL, lit les fichiers DATABASE et ramne les blocs de donnesen SGA, retourne le rsultat au Process User.Oracle peut tre configur de deux faons:

- avec un dedicated server, un server process traite les requtes d'un seul user process- avec un multi-threaded server, plusieurs user processes se partagent un petit nombre

de server processes, minimisant le nombre de server processes et maximisant l'utilisation desressources systme.

* Background Process qui prennent en charge les mcanismes internes dOracle

Ils ont pour nom ora_processname_SID


----------------------------------------------------------------------------------------------------------------------------------------


DataBase Writer (DBWR):crit les blocs modifis de la base dans les fichiers Datafile, d'unemanire dsynchronise par rapport aux transactions, en utilisant une LRU listLog Writer (LGWR): crit le contenu du buffer Redo Log de la SGA dans le fichier RedoLog en ligne lors d'un COMMITCheckpoint (CKPT): signale au DBWR la ncessit d'un CHECKPOINT et trace cetvnement dans les fichiers de contrle et dans les en-ttes des fichiers Datafile. Il estfacultatif; s'il est absent il est suppl par LGWR.System Monitor (SMON): il rtablit la cohrence du systme aprs un incident et libre lesressources utilises par le systmeProcess Monitor (PMON): il rcupre les anomalies des process USER; il supprime lesprocess en erreur, annule les transactions non valides, libre les verrous, libre les ressourcesutilises dans la SGA. Il contrle galement les dispatchers et les process serveurs.Archiver (ARCH): il recopie les fichiers redo log pleins sur un fichier archive pour pallierune perte ventuelle dun fichier DATABASE (optionnel,existe en mode ARCHIVELOGuniquement)Recoverer(RECO): il est utilis pour rsoudre les transactions interrompues par une pannedans un systme de bases de donnes distribuesDispatcher(Dnnn): processus prsent dans une configuration multi-threaded. Il y a au moinsun de ces processus pour chaque protocole de communication. Il dirige les requtes d'unutilisateur vers un serveur partag et lui renvoie ses requtes.Lock(LCKn): de 1 10 processus de verrouillage peuvent tre utiliss lorsque Oracle ParallelServer est install.

Le programme Interface

Cest un mcanisme par lequel un programme utilisateur communique avec le server process;il est utilis comme une mthode de communication standard entre un client et Oracle. Il agitcomme un mcanisme de communication en formattant les donnes, transfrant les donnes,interceptant et retournant les erreurs. Il ralise les conversions de donnes, en particulier entrediffrents types dordinateurs ou avec des donnes de programmes externes.

III-LES FICHIERS

Il existe quatre types de fichiers* Fichiers Datafile* Fichiers Redo Log* Fichiers Control* Fichiers Archivage

Fichiers DatafileIls contiennent toutes les donnes de la base; toutes les structures logiques et physiques y sontstockes (tables, index, rollback segments). Ils possdent les caractristiques suivantes:

* un fichier Datafile peut tre associ une seule base de donnes* les fichiers Datafile ont un ensemble de caractristiques qui permet de leur allouer automatiquement une extension en cas de dpassement de capacit* un ou plusieurs Datafiles forment une unit logique appele tablespace, prsente au chaptre 3.


----------------------------------------------------------------------------------------------------------------------------------------


* un fichier Datafile est constitu dun ensemble de blocs dont la taille dpend du sytme dexploitation.

Les nouvelles donnes et les donnes modifies ne sont pas ncessairement critesimmdiatement sur un fichier Datafile; afin d'optimiser les performances du sytme, elles sontmmorises dans la SGA et sont crites priodiquement sur les fichiers Datafile par leprocess DBWR.

Fichiers Redo LogIls contiennent toutes les donnes modifies et sont utiliss en cas de perte des fichiersDatafile; ils sont au minimum deux et ont un fonctionnement circulaire. Ils peuvent treutiliss de faon unique ou multiplexe:

* Faon unique: un seul fichier Redo Log est en service un moment donn; quand unfichier est plein, le deuxime est mis en service; les modifications ne sont stockes quuneseule fois.

* Faon multiplexe: plusieurs groupes de plusieurs fichiers Redo Log sont en serviceet mis jour simultanment.

Fichiers Control fileChaque base possde au moins un fichier de contrle. Il est hautement recommand de lemultiplexer pour des raisons de scurit. Ils contiennent la description physique de la base:

* nom de la base* nom et chemin daccs des fichiers Dafile et Redo Log* date et heure de cration de la base* informations concernant la cohrence de la base (checkpoint)

Ils sont utiliss au dmarrage dune instance et pour la restauration si ncessaire. Ils sontmodifis chaque modification structurelle de la base.

Fichiers ArchivageIls contiennent des copies des fichiers Redo Log (mode ARCHIVELOG uniquement)

Fichier initSID.oraIl contient les paramtres de fonctionnement dune instance et un paramtre identifiant le oules fichiers CONTROL; il est utilis la cration ou au dmarrage dune instance (SID: nomde la base).

Les diffrents paramtres dfinissent

* les limites des ressources de la base* les nombres maximum dutilisateurs ou de process simultans* les noms des fichiers et des rpertoires utiliss par le systme

Lensemble de ces paramtres est mmoris dans la vue v$parameter; il est prsent enAnnexe C. Tous les fichiers sont stocks dans le rpertoire ORACLE_HOME/dbs,ORACLE_HOME contenant le rpertoire dinstallation de Oracle.


----------------------------------------------------------------------------------------------------------------------------------------


IV-LE DICTIONNAIRE DE DONNEES

Le dictionnaire de donnes est form par un ensemble de tables systme contenant toutes lesinformations sur les structures logiques et physiques de la base:

*noms des utilisateurs*privilges et rles de chaque utilisateur*noms et caractristiques des objets de la base (tables, vues, snapshots, index, clusters, synonyms, squences, procdures,fonctions, packages, triggers, etc..)*contraintes dintgrit*ressources alloues*activit de la base*etc....

Seul Oracle peut mettre jour les tables du dictionnaire de donnes. Il contient des vuesaccessibles aux utilisateurs laide de lordre SELECT. Il est conserv dans le tablespaceSYSTEM; il est la proprit de l'utilisateur SYS.Les classes de vues:

USER_...: informations sur tous les objets dont lutilisateur est propritaireALL_.....: informations sur tous les objets accessibles par lutilisateur connectDBA_....: informations sur tous les objets de la base(utilisable uniquement par les utilisateursayant le privilge SELECT ANY TABLE)V$.........: informations sur dynamic performance tables dcrivant ltat actuel du systme(locks,rollback segments,control files,etc....).Les vues les plus couramment utilises possdent un synonyme simple.

Lannexe A prsente l'ensemble de ces vues; le contenu de chacune d'elles est fourni dansOracle7 Server Reference.

Le dictionnaire de donnes a deux usages principaux:

* vrification de chaque requte DDL (syntaxe et privilges)* informations sur la structure de la base

V-ORGANISATION DES REPERTOIRESLe serveur Oracle7 est install dans le rpertoire dfini dans la variable ORACLE_HOME; ilcontient les sous-rpertoires suivants:

bin: excutables de tous les produitsdbs: fichiers: initsid.ora datafiles logfiles controlfilesrdbms: outils d'administrationlib: bibliothques des produits Oracleotrace: Oracle traceows: Oracle WebServerguicommon2: bibliothques,fichiers,scripts et messages utiliss par les produits d'interfacenetwork: SQL*NET version 2et pour chaque produit installproduct_name: admindemo doc install lib mesg


----------------------------------------------------------------------------------------------------------------------------------------


Le serveur Oracle7 utilise galement des rpertoires admin,arch,bg,core,db,user pouraccueillir en particulier les fichiers trace contenant le dtail de l'excution des processus; leslocalisations de ces rpertoires sont dfinies par les valeurs des paramtresbackground_core_dump, core_dump_dest, log_archive_dest, use_dump_dest du fichierinitSID.ora.

VI-QUESTIONS

I/ A l'aide des commandes sytme, rechercher le nom de chaque instance Oracle dmarre surle systme

II/ Rechercher lensemble des processus actifs d'une instance

III/ A l'aide de l'outil svrmgrm- trouver le nom des fichiers Datafile et Log file de la base IUP- trouver les caractristiques principales de la SGA- afficher les paramtres qui ont servi au dmarrage de l'instance

IV/ Afficher le nom et la description des vues du dictionnaire de donnes;reprer les vuescontenant les informations relatives l'architecture de la base de donnes.

V/ A l'aide de ces vues, retrouver les informations suivantes:- taille des diffrents buffers de la SGA- rpertoire de mmorisation des fichiers datafile,log file et control file- taille des fichiers datafile- tat et nom des fichiers log file et control file

Administration d'une base de donnes Oracle7 Cration

----------------------------------------------------------------------------------------------------------------------------------------


I-CREATION DUNE BASE DE DONNEES

La cration dune base de donnes comporte les tapes suivantes:

a/ Sauvegarde des bases existantes: cette opration est facultative maisrecommande

b/ Cration des fichiers paramtres: Chaque instance est dmarre laide dunfichier paramtre initSID.ora ou SID est le nom de la base; un modle de fichier paramtreest fourni avec la distribution du noyau (init.ora).Le fichier paramtre de la base devraindiquer au minimum les valeurs des paramtres suivants: db_name, db_domain,control_files, db_block_size, db_block_buffers, processes, rollback_segments; par dfaut,Oracle recherche ce fichier dans ORACLE-HOME/dbs.

c/ Dmarrage d'une base de donnesLe dmarrage d'une base de donnes se fait en trois tapes:

- dmarrage de l'instance- montage de la base- ouverture de la base

Le dmarrage de l'instance dclenche l'allocation de l'espace pour la SGA et la cration desbackground processes; aucun fichier datafile ou logfile n'est associ l'instance.Une instance est identifie par son nom, qui est mmoris dans la variable ORACLE_SIDex: ORACLE_SID=test; export ORACLE_SIDIl est galement ncessaire de charger la variable ORACLE_HOME avec le nom du rpertoireracine du noyauex: ORACLE_HOME=/net4/oracle ; export ORACLE_HOME

Le montage de la base associe une base avec une instance; l'instance ouvre les fichiersControl file; cette option permet l'administrateur de raliser diffrentes oprations telles querestauration ou sauvegarde, les autres utilisateurs n'ayant pas accs la base.

L'ouverture de la base rend la base disponible pour les oprations des utilisateurs; Oracleouvre les fichiers Datafile et les fichiers redo log on line

Ces trois tapes sont ralises l'aide de la commande STARTUP de l'outil svrmgrl

Dmarrage de l'instance:STARTUP NOMOUNT pfile = /initSID.oraMontage de la baseSTARTUP MOUNT pfile = /initSID.oraOuverture de la baseSTARTUP OPEN pfile = /initSID.ora

CREATION DUNE BASE DE DONNEES


----------------------------------------------------------------------------------------------------------------------------------------


Le paramtre pfile indique le rpertoire de stockage du fichier initSID.ora; par dfaut, Oraclerecherche ce fichier dans le rpertoire ORACLE_HOME/dbs.Le passage dune tape la suivante se fait laide des commandes de loutil svrmgrl (servermanager en mode caractres) et avec le mot cl INTERNALSous UNIX, un utilisateur peut utiliser svrmgrl sil appartient un groupe dadministration dela base (DBA par dfaut); les groupes sont dfinis dans le fichier /etc/group.

En mode caractres lappel svrmgrl se fait par la commandesvrmgrlSVRMGR> Connect internalConnected

d/ Cration de la base laide de la commande CREATE DATABASE

Cette commande excute les oprations suivantes:- cration des fichiers Datafile- cration des fichiers Control file- cration des fichiers Redo Log file- cration du tablespace SYSTEM et du SYSTEM rollback segment- cration du dictionnaire de donnes dans le tablespace SYSTEM- cration des utilisateurs SYS/CHANGE_ON_INSTALL et SYSTEM/MANAGER- spcification de lensemble de caractres utilis pour stocker les donnes dans la base- MOUNT et OPEN de la base

Syntaxe de la commande CREATE DATABASE

CREATE DATABASE databaseDATAFILE filespec[AUTOEXTEND OFF ]

ON [NEXT integer K ] [MAXSIZE UNLIMITED ]M integer K

MLOGFILE [GROUP integer ] filespec

[CONTROLFILE REUSE ][MAXLOGFILES integer ][MAXLOGMEMBERS integer ][MAXLOGHISTORY integer ][MAXDATAFILES integer ][MAXINSTANCES integer ][CHARACTER SET 'US7ASCII' ][ARCHIVELOG ]NOARCHIVELOG[EXCLUSIVE ]

filespecfilename SIZE integer K/M [REUSE]


----------------------------------------------------------------------------------------------------------------------------------------


e/ Cration des vues du dictionnaire de donnes

Elle se fait laide des scripts SQL fournis dans le rpertoire ORACLE_HOME/rdbms/admin

- sous SYS: catalog.sql vues et synonymes publicscatproc.sql procdures systmecataudit.sql audit

- sous SYSTEM, et pour chaque administrateur de la basecatdbsyn.sql synonymes sur les vues DBA_*

f/ Activation de sqlplus- sous SYSTEM excuter la procdure pupbld.sql qui se trouve dans

ORACLE_HOME/sqlplus/admin

g/ Restauration des bases sauvegardes en a/

II-ACTIONS SUR L'ETAT DUNE BASE

Dmarrage d'une base

STARTUP [FORCE] [RESTRICT] [PFILE=nom_fich] [ OPENMOUNTNOMOUNT ]

FORCE: fermeture de linstance si ouverte, puis dmarrageRESTRICT: pour les utilisateurs dont le privilge SYSTEM correspond RESTRICTEDSESSIONPFILE: nom du fichier init.ora utiliserNOMOUNT: dmarrage de linstanceMOUNT: dmarrage de linstance et ouverture des fichiers CONTROLOPEN: dmarrage complet de la base

Modification de l'tat dune base

ALTER DATABASE nom_base MOUNTOPEN

Arrt dune base

SHUTDOWN ABORT arrt brutal de tous les processIMMEDIAT annulation des transactions en coursNORMAL avec attente de deconnexion


----------------------------------------------------------------------------------------------------------------------------------------


III-LES TABLESPACES

Les donnes dune base Oracle sont mmorises dans une ou plusieurs units logiquesappeles tablespaces et physiquement dans des fichiers associs ces tablespaces.

Ladministrateur de la base peut utiliser le concept de tablespace pour

* contrler lallocation despace disque* assigner des quotas de ressource disque aux utilisateurs* contrler la disponibilit des donnes en rendant les tablespaces online ou offline* constituer des units de sauvegarde ou de restauration partielle de la base*rpartir les zones de stockage entre plusieurs disques pour accrotre les performances

Chaque base contient au moins un tablespace appel SYSTEM, qui est automatiquement crpar lordre CREATE DATABASE; ce tablespace SYSTEM contient toujours les tables dudictionnaire de donnes, les procdures, les fonctions, les packages, les triggers et le rollbacksegment SYSTEM.

TABLESPACE SYSTEM

TABLESPACE APPLICATION 1

DONNEES

INDEX

INDEX

TABLESPACE APPLICATION 2

DONNEES

BASE DE DONNEES


----------------------------------------------------------------------------------------------------------------------------------------


Ladministrateur de la base peut crer dautres tablespaces laide de la commande CREATETABLESPACE et attribuer aux utilisateurs des droits daccs ces tablespaces.

CREATE TABLESPACE tablespaceDATAFILE filespec[AUTOEXTEND OFF ]

ON [NEXT integer K ] [MAXSIZE UNLIMITED ]M integer K

M[DEFAULT STORAGE storage_clause][ONLINE ] OFFLINE[PERMANENT ] TEMPORARY

Un tablespace peut tre online ou offline. Ladministrateur peut rendre un tablespace offlinepour:

* rendre une partie de la base non accessible, alors quun accs normal continue sur lesautres tablespaces* faire la sauvegarde des informations contenues dans ce tablespace* rendre une application et ses tables innaccessibles pendant la maintenance de lapplication

Un tablespace est constitu dun ou plusieurs fichiers physiques qui contiennent les diffrentstypes de segments(donnes, index, rollback); la taille dun tablespace peut tre augmente enlui affectant un nouveau fichier laide de la commande ALTER TABLESPACE.

IV-LES ROLLBACK SEGMENTS

Une base de donnes contient un ou plusieurs ROLLBACK SEGMENTS; un rollbacksegment enregistre les actions dune transaction qui peuvent tre annules en cas dincidentafin de remettre la base de donnes dans un tat cohrent.Un rollback segment est constitu de plusieurs entres , chacune delles contenant le nomdu fichier et le numro de block modifis par la transaction ainsi que le contenu du bloc dedonnes avant la transaction.Le rollback segment SYSTEM est cr lors de la cration de la base dans le tablespaceSYSTEM; il nest utilis que pour les transactions portant sur les donnes du dictionnaire(commandes du langage de dfinition).Un ou plusieurs autres rollback segments doivent exister pour les transactions portant sur desdonnes utilisateur; leur nombre est fonction du dbit transactionnel (cf Organisationphysique des donnes).

Un rollback segment est cr l'aide de la commande

CREATE ROLLBACK SEGMENT rollback_name


----------------------------------------------------------------------------------------------------------------------------------------


TABLESPACE tablespace_nameSTORAGE clauseA sa cration, un rollback segment est offline; il doit tre mis online l'aide de la commandeALTER ROLLBACK SEGMENT rolback segment_name onlinepour tre utilis pendant la session.Pour tre en permanence online un rollback segment doit figurer dans le fichier init.ora delinstance.Un rollback segment est supprim par la commande DROP ROLLBACK SEGMENTrollback segment_name; il devra avoir t mis offline auparavant.Les caractristiques des rollback segments sont mmorises dans la vuesys.dba_rollback_segs du dictionnaire de donnes.

Une transaction peut tre oriente dans un rollback segment avec la commande

SET TRANSACTION USE ROLLBACK SEGMENT nom_rollback

cest alors la premire commande de la transaction

V-QUESTIONS

I/ Etudiez les scripts fournis en Annexe D et dterminer la structure de la base cre parCretdbGEN.sql.Inspirez vous de ces scripts pour crer votre propre base.

II/ Cration d'une instanceSur la station qui vous a t dsigne (o vous appartenez au groupe dba), ralisez, dansl'ordre indiqu, les oprations suivantes pour crer une nouvelle instance

A/ Dfinition de l'environnement de travail-Placez vous dans le groupe dba l'aide de la commande newgrp dba

-Excutez la commande umask 002 pour permettre Oracle d'crire dans vos rpertoires

-Positionnez les variables denvironnementORACLE_HOME=/net4/oracleORACLE_SID= ( reprsentera toujours votre nom dutilisateur Oracle)

-Crez le rpertoire /oracle//scriptscd /oraclemkdir -p /login>/scripts

et recopiez les scripts de /net4/oracle/data/GENERIC/scripts dans ce rpertoire

-Crez les rpertoires d'accueil des fichiers trace suivants:/oracle//admin/oracle//arch/oracle//bg/oracle//core/oracle//db/oracle//user

-Dsactivez linstance IUP l'aide de la commande unset TWO_TASK


----------------------------------------------------------------------------------------------------------------------------------------


B/ Cration de la base Crez la base de donnes ayant les paramtres suivants:- tous les fichiers seront mmoriss dans /oracle/- les fichiers REDO LOG seront dsigns par log1(2).dbf et auront une taille

de 200K (2 groupes de deux fichiers)- le fichier Datafile sera mmoris dans le mme rpertoire et aura une taille de 10M.

son nom sera sys.dbf

C/ Cration des vues du dictionnaire de donnesSous svrmgrl,excutez les procdures catalogues permettant de crer les vues du dictionnairede donnes

D/Activez sqlplus

E/Dmarrez linstance cre; vrifiez que les processus Oracle sont activs

III/ Retrouvez laide des vues du dictionnaire de donnes les caractristiques physiques(tablespaces, rollback segments,...) de cette base

IV /Sur la base que vous venez de crer prcdemment, crez un tablespace ayant lescaractristiques suivantes:Nom du tablespace: Nom du fichier associ: .dbfLocalisation du fichier associ: /oracle/Taille du fichier associ: 200K

V/ Dans le tablespace cr, crez la table T_ (col1 number(3),col2 char(10)).

VI/ Assurez-vous que la table a t cre dans le bon tablespace.

VII/ Insrez une ligne dans la table T_; Que se passe-t-il?

VIII/ Crez le rollback segment RBS_ dans le tablespace . Essayez nouveaud'insrer une ligne dans la table T. Que se passe-t-il? Comment peut-on remdier auproblme?

IX/ Ecrivez une requte SQL qui affiche toutes les caractristiques des rollback segments dela base

X/ Crez un deuxime rollback segment RBS_2; Insrerz deux lignes dans la tableT_ et faite en sorte que l' insertion se droule laide du rollback segmentRBS_ pour la premire puis de RBS_2 pour la deuxime; vrifiez cetteproprit laide des tables virtuelles du dictionnaire de donnes v$rollname et v$rollstat. (valeur du champ Xacts de la vue v$rollstat)

Administration d'une base de donnes Oracle7 Organisation physique

----------------------------------------------------------------------------------------------------------------------------------------


I- ORGANISATION GENERALE

Une base de donnes Oracle est physiquement constitue par un ensemble de fichiers o sontstockes les donnes; elle est divise en units logiques appeles tablespaces (cf chapitre 3).Le niveau le plus fin de granularit est le bloc (appel aussi bloc logique, bloc Oracle oupage); il correspond un nombre spcifique de bytes, dfini la cration de la base. La tailledun bloc est un multiple de la taille dun bloc du systme dexploitation; sa valeur est donnepar le paramtre db_block_size.Un ensemble de blocs contigs forme un extent, contenant un type particulier dinformations(table, index,).

Un segment est un ensemble dextents allous pour un type spcifique dinformations,stockes dans le mme tablespace; on distingue les types de segments suivants :

-segment de donnes: Chaque table non lie un cluster ou chaque cluster est stockdans un segment de donnes cr par les commandes CREATE TABLE ou CREATECLUSTER.

-segment dindex: lindex est stock dans un segment index cr par la commandeCREATE INDEX; tous les extents allous un segment index lui sont conservs aussilongtemps que lindex existe; lorsque la table associe ou lindex sont supprims, lespace estutilis pour dautres usages dans le tablespace.

-rollback segment: Chaque base de donnes contient un ou plusieurs rollbacksegments; un rollback segment contient les actions dune transaction qui pourrait tre annuleen cas dincident; il est utilis pour assurer la cohrence des lectures, pour dtruire certainestransactions ou pour restaurer la base de donnes.

-segment temporaire: il est utilis pour mmoriser temporairement des informationspendant des requtes de tris ou contenant une clause group by par exemple; il est stock dansun tablespace cr cet effet.

-segment de dmarrage: il est cr la cration de la base; il contient les dfinitionsdes objets du dictionnaire de donnes et est charg louverture de la base.

Caractristiques physiques dun lment de mmorisationLes caractristiques physiques des tables, index, clusters, rollback segments et tablespacessont dfinies par la clause Storage clause contenant les paramtres suivants:

-INITIAL integer K/M: taille en bytes du premier extent-NEXT integer K/M: taille en bytes du prochain extent

ORGANISATION PHYSIQUE DUNE BASE DE DONNEES


----------------------------------------------------------------------------------------------------------------------------------------


-MINEXTENTS integer nb dextents allous la cration (minimum 2 pour les rollback segments)

-MAXEXTENTS integer nb maximum dextents-PCTINCREASE integer % daugmentation entre 2 extents (par defaut 50)-OPTIMAL integer K/M taille optimale dun rollback segment

exemple:CREATE TABLE dept(deptno number(2), dname varchar2(14), loc varchar2(13))

STORAGE (initial 100K next 50K minextents 1 maxextents 50 pctincrease 5)

Les commandes CREATE TABLE ou CREATE CLUSTER contiennent galement lesparamtres suivants:

-PCTFREE integer: % despace rserv dans chaque bloc pour des modifications(update) ultrieures (10% par dfaut)

-PCTUSED integer: % minimum despace utilis dans un bloc (40% par dfaut)pour insrer de nouveaux enregistrements aprs des suppressions ; (la somme PCTFREE +PCTUSED dot tre infrieure 100).

-INITRANS integer: nb initial dentres transactions alloues un bloc (1-255);chaque transaction qui modifie un bloc demande une entre dans le bloc

-MAXTRANS integer: nb maximum de transactions concurrentes qui peuventmodifier un bloc allou une table (1-255)Les caractristiques des diffrents segments sont fournies par la vue DBA_SEGMENTS.

II- LES TABLES

Les tables, indpendantes ou faisant partie d'un cluster, sont mmorises dans les segments dedonnes des fichiers Datafile. La figure suivante prsente l'organisation physique de cesfichiers et la structure des informations l'intrieur d'un bloc physique.

Un bloc physique comprend trois parties:-ENTETE: informations gnrales sur la structure du bloc (type dinformation,

propritaire, date de cration,)-DONNEES: zone de mmorisation des donnes (enregistrements)-DISPO: partie du bloc destine mmoriser les modifications d'enregistrements en

minimisant le nombre de chanages ; sa dimension est dfinie partir des paramtresPCTFREE et PCTUSED.Selon lactivit transactionnelle sur la table, il faut:

- augmenter PCTFREE si beaucoup de modifications augmentent la longueur desdonnes.Une valeur leve de PCTFREE privilgie les oprations de mise jour et est adapte unebase active, elle implique la rservation d'un espace plus importante; une valeur faible dePCTFREE est adapte une base stable et favorise les interrogations.

- augmenter PCTUSED pour favoriser les performances en balayage complet de latable.Une valeur leve de PCTUSED permet une occupation plus efficace de l'espace, augmente lecot des mises jour.

- La somme PCTFREE + PCTUSED doit tre


----------------------------------------------------------------------------------------------------------------------------------------


FICHIER DATAFILE

SEGMENTTEMPORAIRESEGMENTS DEDONNEES

SEGMENTSD'INDEX

ROLLBACKSEGMENTS

EXTENT1

EXTENT2

EXTENTN

BLOCK 1 BLOCK 2 BLOCK N

EN_TETE DISPO DONNEES

ENREG. 1 ENREG 2 ENREG N


----------------------------------------------------------------------------------------------------------------------------------------


Dimensionnement dune table

En-tte de blocLa taille de l'en-tte de bloc est donn par la formule:

EN_TETE= KCBH +UB4 + KTBBH + (INITRANS-1)*KTBIT + KDBH

o KCBH, UB4, KTBBH, KTBIT, KDBH sont fournies dans la vue V$TYPE_SIZE (cfAnnexe E) et INITRANS est le nombre initial d'entres alloues la table

La taille DB_BLOCK_SIZE du bloc est donne dans la vue V$PARAMETER

DonnesL'espace disponible en dehors de l'en-tte (DISPO +DONNEES) est

LIBRE = DB_BLOCK_SIZE - EN_TETE

et lespace allou aux donnes dans un bloc est donc

DONNEES = CEIL(LIBRE * (1-PCTFREE/100)) - KDBT

o KDBT est donn dans la vue V$TYPE_SIZE

Structure d'un enregistrement

Longueur colonne: 1 octet si longueur 250 octets

En-tte d'enregistrement : EN_TETE_LIGNE=UB1*3 (donn par V$TYPE_SIZE)Pour un enregistrement chan, len-tte-ligne contient linformation de chanage sous laforme dune adresseChaque colonne de chaque enregistrement est prcde dune zone LG_COL contenant lalongueur de la colonneLG_COL: champ contenant la longueur de la colonne

1 si taille col < 2503 sinon

COL : taille de la colonne

Taille des colonness selon le type de donnes:

VARCHAR2, CHAR, LONG: 1 octet par caractreDATE: 7 octetsNUMBER(x,y): 1 + (longueur moyenne de x)/2 + 1*

* si ngatifNULL 1 octetROWID: 6 octets

EN_TETE_LIGNE

LG_COL COL LG_COL COL


----------------------------------------------------------------------------------------------------------------------------------------


Taille moyenne dun enregistrement:

LG_ENREG= EN_TETE_LIGNE+ (LG_COLi+COLi)taille totale des colonnes en incluant les longueurs

Nombre d'enregistrements par bloc:

NB_ENREGS_BLOC =FLOOR(DONNEES / LG_ENREG)

Nombre de blocs ncessaires pour mmoriser les donnes de la table

NB_BLOCS = CEIL (NB_ENREGS /NB_ENREGS_BLOC)

o NB_ENREGS est le nombre estim d'enregistrements dans la table

III- LES INDEX

Un index est une structure de donnes supplmentaire qui permet loptimiseur dacclrerles recherches dans une table; il est organis en B-arbre, cest dire de manire mettre lemme temps (mme nombre daccs disque) pour atteindre nimporte quel enregistrement dela table.Un index est cr implicitement la cration dune table avec les contraintes PRIMARYKEY et UNIQUE ou explicitement par la commande CREATE INDEX ; il peut tre construitsur une ou plusieurs colonnes.

CREATE INDEX nom_index ON nom_table(col1[,col2])Storage clause

Cration dun index

Un index augmente la performance des requtes qui slectionnent un petit nombredenregistrements (moins de 25%) des enregistrements de la table.Le choix des colonnes indexer peut se faire partir des critres suivants:

-colonne frquemment utilise dans une clause WHERE-colonne frquemment utilise dans une jointure-colonne ayant une bonne slectivit , cest dire colonne dont peu denregistrements ont la mme valeur-colonne rarement modifie-colonne naparaissant pas dans une clause WHERE avec des fonctions ou des oprateurs

Un index peut tre compos , cest dire construit sur plusieurs colonnes, pour augmenterla slectivit par exemple.


----------------------------------------------------------------------------------------------------------------------------------------


Il ne faut pas oublier quun index, sil peut augmenter la performance des ordres SELECT,diminue les performances des ordres INSERT, UPDATE, DELETE et occupe une place nonngligeable dans la base.

Un index est compos de deux parties:- les blocs suprieurs contiennent des informations qui pointent sur les blocs infrieurs

(feuilles)- les feuilles contiennent une valeur du champ index et le ROWID de

lenregistrement correspondant

Dimensionnement dun index

Un bloc index est, comme un bloc table, compos:-dun en-tte comportant une partie fixe et une partie variable-dun espace PCTFREE-dun espace de stockage des cls

EN_TETE_INDEX DISPO DONNEES

ROWID INDEX COLUMNS DATA

Blake RowidClark RowidFord Rowid

AdamsAllen

BlakeClarkFord

M

B MIT

BJ

BlakeClarkFord

JamesJones

Martin MillerScott

TurnerWard

Bloc Feuille

H_INDEX


----------------------------------------------------------------------------------------------------------------------------------------


En-tte de blocEN_TETE_INDEX = 113 + 24*INITRANSPour un index INITRANS = 2 par dfaut

Espace disponible pour les donnes dans un blocDONNEES = (DB_BLOCK_SIZE - EN_TETE_INDEX) *(1 - (PCTRFREE/100))

Taille moyenne d'une entre d'indexINDEX = H_INDEX + ROWID_LENGTH +F +V

H_INDEX: 2ROWID_LENGTH: 6F longueur totale (colonne + zone longueur)des colonnes de l'index de longueur infrieure ougale 127; pour ces colonnes, la taille de la zone longueur est 1V: longueur totale(colonne + zone longueur)des colonnes de l'index de longueur suprieure 127; pour ces colonnes, la taille de la zone longueur est 2

Nombre de blocs pour l'index

NB_BLOCKS_INDEX = 1.05* (NB_NOT_NULL_ROWS) / FLOOR (DONNEES / INDEX)

IV-LES CLUSTERS

Un cluster est une structure physique utilise pour stocker des tables sur lesquelles doiventtre effectues de nombreuses requtes avec opration de jointure. Un cluster ne doit pas treinstall sur une table frquemment utilise isolment.Dans un cluster, les enregistrements de plusieurs tables ayant mme valeur du champ servant la jointure (cl du cluster) sont mmoriss dans un mme bloc physique ; la valeur duparamtre SIZE de la commande CREATE CLUSTER donne le nombre maximum de clustersqui peuvent tre mmoriss dans un bloc.

Structure dun cluster

Ce cluster contient un enregistrement de la table T1 et deux enregistrements de la table T2.

EN_TETE_CLUSTER

DISPO DONNEES

CLUSTER 1 CLUSTER 2 CLUSTER 3

KEY_HEADER

Ck Sk T1 T2 T2


----------------------------------------------------------------------------------------------------------------------------------------


INDEX

empdept

empdept

empdept

empdept

Il existe deux types de clusters:- les clusters indexs- les clusters hash

Cluster indexUn index est cr sur la cl du cluster

10 20 30 40

Cluster hash

Une fonction de hachage est applique la cl du cluster et renvoie une valeur qui est utilisepour localiser lenregistrement; Oracle fournit une fonction de hachage interne qui produit unminimum de collisions dans la plupart des cas. Il est possible dutiliser une fonction dehachage particulire en la spcifiant dans la clause CREATE CLUSTER.

Cration dun cluster

Un cluster sera cr-entre des tables souvent accdes laide dun ordre SELECT comportant une

opration de jointure entre ces tables; si lopration est une qui-jointure, il sera intressant decrer un hash cluster.

-si les enregistrements ayant la mme valeur de cluster key peuvent tre mmorissdans un seul bloc physique

On choisira un cluster index si la taille de segment et le nombre de cls est difficile prvoir,un hash cluster si ces informations sont faciles prvoir.On choisira un cluster index pour les jointures, un hash cluster pour les requtes dont laslection porte sur la cl de hash.

On ne crera pas de cluster-si les oprations de jointure sont rares, si les traitements squentiels de lune des

tables sont frquents ou si la valeur de la cluster key peut tre modifie-si les tables doivent saccrotre de manire importante


----------------------------------------------------------------------------------------------------------------------------------------


Dimensionnement dun cluster

Pour un cluster il faut calculer-la taille de len-tte EN_TETE_CLUSTER dans un bloc-la taille de lespace disponible dans un bloc pour le stockage des lignes DONNEES-la taille moyenne d'un enregistrement de cluster CLUSTER

En-tteEN_TETE_CLUSTER = KCBH + UB4 + KTBBH + KTBIT*(INITTRANS-1) + KDBH

Les tailles de toutes les variables sont donnes dans V$TYPE_SIZE

Espace disponible dans un bloc, hors en-tteDISPO= DB_BLOCKSIZE - EN_TETE_CLUSTEREspace disponible pour les donnes

DONNEES=DISPO*(1-PCTFREE/100) - 4*(NB_TABLES + 1)*ROWS_IN_BLOCK

NB_TABLES: nombre de tables dans le clusterROWS_IN_BLOCK: nombres d'enregistrements dans un bloc du cluster

Espace minimum ncessaire pour stocker les enregistrements de la table Tn du cluster

Sn = ROWHEADER + Fn + VnavecROWHEADER = 4Fn: longueur totale (colonne + zone longueur) des colonnes de la table Tn de longueurinfrieure ou gale 250; pour ces colonnes, la taille de la zone longueur est 1Vn: longueur totale (colonne + zone longueur) des colonnes de la table Tn de longueursuprieure 250; pour ces colonnes, la taille de la zone longueur est 3

Taille moyenne d'un enregistrement cluster

CLUSTER = ((R1*S1) + (R2*S2) + ...+(Rn*Sn)) + KEY_HEADER + Ck + Sk +2Rt

avecRn: nombre moyen d'enregistrements de la table n associs une cl du clusterSn: taille moyenne d'un enregistrement de la table n associ une cl du clusterKEY_HEADER=19Ck: longueur de colonne pour la cl du clusterSk: taille moyenne de la valeur de la cl du clusterRt: Nombre total d'enregistrements associs une cl du cluster (R1 + R2 +...+Rn)

Nombre de cls de cluster par bloc physique

NB_KEYS_BLOCK = FLOOR( (DONNEES + 2Rt )/ (CLUSTER+ 2Rt))


----------------------------------------------------------------------------------------------------------------------------------------


Nombre de blocks pour le cluster

NB_BLOCKS = CEIL ( NB_KEYS / NB_KEYS_BLOCK)

NB_KEYS: nombre de cls de cluster estimer la dfinition des tables

V-LES ROLLBACK SEGMENTS

A la cration de la base, le rollback segment SYSTEM est cr dans la tablespace SYSTEM.Si la base dot avoir dautres tablespaces, elle dot possder au moins deux autres rollbacksegments dans le tablespace SYSTEM. Le rollback segment SYSTEM est cr avec lesparamtres par dfaut asssocis la tablespace; il ne peut pas tre dtruit.Une instance utilise toujours le rollback segment SYSTEM en complment dautres rollbacksegments, si ncessaire. Cependant, sil existe plusieurs rollback segments, Oracle essaiedutiliser le rollback segment SYSTEM uniquement pour des transactions spciales.La taille totale des rollback segments dot tre calcule partir de la taille des transactions lesplus frquentes. En gnral, des transactions courtes sont plus performantes avec plusieurspetits rollback segments alors que les transactions plus longues ,batch par exemple, serontplus efficaces avec de plus grands rollback segments.Si toutes les transactions sont courtes, les rollback segments seront assez petits pour tremmoriss en mmoire centrale; sils sont assez petits, ils pourront tre mmoriss dans laSGA selon lalgorithme LRU et le nombre d'oprations dentree/sortie sera nettementdiminu.Le principal inconvnient des petits rollback segments est daccrotre la probabilit derrreur snapshot too old (bases de donnes distribues).Quand il y a des transactions courtes et des transactions longues, les performances peuventtre optimises en affectant certains rollback segments certaines transactions laide de lacommande SET TRANSACTION USE ROLLBACK SEGMENT rollback_segment.Dans le cas gnral, la taille de chaque rollback segment dot tre environ 10% la taille de laplus grosse table tant donn que la plupart de ses instructions SQL affectent 10% ou moinsdune table; la taille optimale du rollback segment peut tre prcise par le paramtreOPTIMAL de la STORAGE clause lors de la cration du rollback segment.Lespace allou un rollback segment dot tre rparti entre des extents de mme taille; laperformance optimale au niveau des entres/sorties est observe si chaque rollback segmentest compos de 10 20 extents.Le nombre total de rollback segments est li au nombre de transactions simultanesenvisages:

Nombre de transactions simultanes: n Nombre de rollback segmentsn


----------------------------------------------------------------------------------------------------------------------------------------


VI-QUESTIONS

I/ La base de donnes "Gestion des Commandes" est constitue des tables suivantes:CUSTOMERcustid not null number(6)name not null varchar2(45)address varchar2(40)city varchar2(30)state varchar2(2)zip varchar2(9)area number(3)phone varchar2(9)repid not null number(4)creditlimit number(9,2)comments long

ORDordid not null number(4)orderdate datecommplan varchar2(1)custid number(6)shipdate datetotal number(8,2)

ITEMordid not null number(4)itemid not null number(4)prodid number(6)actualprice number(8,2)qty number(8)itemtot number(8,2)

PRODUCTprodid not null number(6)descrip varchar2(30)

PRICEprodid not null number(6)stdprice number(8,2)minprice number(8,2)startdate dateenddate date

La base de donnes devra accueillir les volumes suivants:CUSTOMER : 2000 enregistrementsORD : 50000 enregistrementsITEM : 200000 enregistrements (5 item en moyenne par ord)PRODUCT : 1000 enregistrementsPRICE : 4000 enregistrements (4 price en moyenne par product)

Des cls primaires sont dfinies pour les tables CUSTOMER,ORD et PRODUCT.Des index sont crs sur les champs CUSTOMER.name et PRODUCT.descrip.Des clusters (indexs) sont crs entre les tables ORD et ITEM d'une part, PRODUCT etPRICE d'autre part.Evaluer l'espace ncessaire pour mmoriser ces informations; le volume du tablespaceSYSTEM est valu 10M.

II/ Retrouver l'espace disponible pour tous les tablespaces de la base.

III/ Faire un tat de la base avec l'occupation de l'espace par tablespace et par propritaired'objet, fournissant les informations suivantes: type,nom, bytes, blocks et extents.

Administration d'une base de donnes Oracle7 Gestion des utilisateurs

----------------------------------------------------------------------------------------------------------------------------------------


I-LES UTILISATEURS

Le contrle daccs Oracle se fait par lassociation du nom et du mot de passe delutilisateur. Lutilisation de la base de donnes sera autorise si

* lutilisateur peut se connecter (privilge CREATE SESSION)* lutilisateur a un espace de travail suffisant sur disque (dveloppeur)

Ladministration de la scurit sur la base de donnes est ralise grce la cration desutilisateurs et la gestion de leurs droits daccs. Chaque base de donnes possde sa propreliste dutilisateurs. Le contrle des droits daccs la base de donnes se fait par rapport unensemble de caractristiques prdfinies:

* Informations dauthentification (login et password)* Tablespaces accessibles* Quotas sur les tablespaces* Tablespace par dfaut* Tablespace temporaire* Privilges et rles* Ressources SYSTEME (PROFILE)

La commande CREATE USER permet ladministrateur de* donner un nom et un mot de passe lutilisateur* lui assigner un tablespace par dfaut* lui assigner un tablespace temporaire* identifier la liste des tablespaces pour lesquels il aura des droits daccs* dlimiter ses ressources disque sur chaque tablespace* dlimiter ses ressources systme

CREATE USER user IDENTIFIED BY password / EXTERNALLY

[DEFAULT TABLESPACE tablespace_name ][TEMPORARY TABLESPACE tablespace_name ][QUOTA integer K/M / UNLIMITED ON tablespace_name ]

[PROFILE profile_name ]

Un utilisateur cr avec la clause identified EXTERNALLY se connectera la base dedonnes l'aide de son compte systme.La commande ALTER USER permet ladministrateur de

* changer le mot de passe de lutilisateur* de modifier ses droits et ses quotas sur les tablespaces* de changer le rle de l'utilisateur (un utilisateur peut appartenir plusieurs rles)

GESTION DES UTILISATEURS ET DES PRIVILEGES


----------------------------------------------------------------------------------------------------------------------------------------


La commande ALTER USER permet lutilisateur de modifier uniquement son mot de passe(identified by password).

ALTER USER USER IDENTIFIED BY password/EXTERNALLY

[DEFAULT TABLESPACE tablespace_name ][TEMPORARY TABLESPACE tablespace_name ][QUOTA integer K/M / UNLIMITED ON tablespace_name ]

[PROFILE profile_name ][DEFAULT ROLE role_name/ ALL EXCEPT role_name/NONE ]

La commande DROP USER permet ladministrateur de supprimer un utilisateuret,ventuellement tous les objets dont il est propritaire (option CASCADE).

DROP USER user_name [CASCADE ]

Toutes les caractristiques des utilisateurs sont rpertories dans le dictionnaire de donnes * USER_USERS: informations sur lutilisateur courant* ALL_USERS: informations sur tous les utilisateurs de la base* DBA_USERS: toutes les informations sur tous les utilisateurs de la base* USER_TS_QUOTAS: informations sur les quotas de lutilisateur courant* DBA_TS_QUOTAS: informations sur les quotas de tous les utilisateurs

II- LES PRIVILEGES

La gestion des privilges permet

*de donner aux utilisateurs le droit dexcuter certaines commandes*dinterdire ou dautoriser la consultation, la mise jour et la suppression des donnes*dinterdire ou dautoriser laccs des fonctions systme*dinterdire ou dautoriser laccs des commandes de changement de structure de la

base*de crer et de supprimer des privilges pour un utilisateur particulier, pour un rle

particulier ou pour tous les utilisateurs(PUBLIC)

Il y a deux types de privilges:

* le privilge SYSTEM qui donne le droit dexcuter des actions sur un certain typedobjet

*le privilge OBJET qui donne le droit daccs une table, une vue, une squence,une procdure, une fonction ou un package.


----------------------------------------------------------------------------------------------------------------------------------------


Privilges SYSTEM

Les privilges SYSTEM dfinissent les types doprations disponibles pour lutilisateur.Ces privilges sont attribus par la commande GRANT.

GRANT system_privilege TO user/role/public [WITH ADMIN OPTION]

Loption WITH ADMIN OPTION donne le droit de redistribuer les privilges reus (cetteoption est interdite si le privilge est attribu un rle). Ces privilges sont supprims par lacommande REVOKE.

REVOKE system_privilege FROM user/role/public

La liste des privilges SYSTEMest fournie en annexe B.

Privilges OBJET

Ils donnent le droit daccs une table, une vue,une squence, une procdure, une fonction ouun package. Ils sont diffrents selon les types dobjet.

Ils sont attribus par la commande GRANT et supprims par la commande REVOKE.

GRANT object_privilege ON object_name TO user/role/public[WITH GRANT OPTION]

Recevoir un privilge avec la clause WITH GRANT OPTION permet de redistribuer leprivilge reu dautres utilisateurs, galement avec la clause WITH GRANT OPTION.Retirer les privilges un utilisateur se rpercute en cascade sur les autres utilisateurs.On peut attribuer des privilges et un role avec la clause WITH GRANT OPTION.

Privilge Droit Table Vue Squence Procdure FonctionPackage

ALTER Modifier objets OUI OUIDELETE Supprimer lignes OUIEXECUTE Excuter OUIINDEX Crer index OUIINSERT Insrer lignes OUI OUIREFERENCES Rfrencer une

colonne dans tableOUI

SELECT Lire lignes OUI OUI OUIUPDATE Modifier lignes OUI OUI

Ces privilges sont supprims l'aide de la commande REVOKE.


----------------------------------------------------------------------------------------------------------------------------------------


III- LES RLES

Un rle est un ensemble de privilges donns des utilisateurs ou dautres rles permettantde grer plus facilement les droits daccs aux donnes; ils permettent de dfinir des groupesdutilisateurs ayant les mmes privilges.Il existe des rles prdfinis la cration de la base:

* EXP_FULL_DATABASE: possibilit dutiliser EXPORT* IMP_FULL_DATABASE: possibilit dutiliser IMPORT* RESOURCE:CREATE CLUSTER, PROCEDURE, SEQUENCE, TABLE, TRIGGER* CONNECT:* DBA: administrateur de base de donnes (tous les privilges with admin option + EXP_FULL_DATABASE et IMP_FULL_DATABASE rles)

Ces trois derniers roles sont crs pour la compatibilit avec les versions prcdentesdOracle.

Un role est cr par la commande CREATE ROLE et supprim par DROP ROLE.

CREATE ROLE role_name IDENTIFIED BY password

Les privilges SYSTEM attribus un role sont dfinis par la commande GRANT; un role estdonn un utilisateur par la commande GRANT; un utilisateur peut appartenir plusieursrles, le nombre de rles tant limit par le paramtre dinitialisation (fichier init.ora)MAX_ENABLED_ROLES.Dans ce cas, l'utilisateur doit possder un role par dfaut dfini par la commande ALTERUSER;il peut changer de rle, sil en a reu le droit, avec la commande

SET ROLE role_name IDENTIFIED BY password

IV- LES PROFILES

Un profile est dfini par un ensemble de paramtres qui permettent de limiter lesconsommations de ressources dun utilisateur: temps CPU, oprations dentres-sorties, tempsdinnocupation, temps doccupation, espace mmoire, sessions courantes.Les profiles permettent de restreindre les grosses consommations de ressources desutilisateurs, dtre sr que les utilisateurs sont dconnects lorsquils ont quitt leur poste detravail, de regrouper les utilisateurs ayant les mmes fonctions et les mmes charges detravail.Il existe un profile DEFAULT, assign par dfaut tous les utilisateurs, initialement sanslimites.Un profile est dfini par la commande CREATE PROFILE et peut tre affect un utilisateurpar les commandes CREATE USER ou ALTER USER.

CREATE PROFILE profile_name LIMITSESSIONS_PER_USER integer/unlimited/defaultCPU_PER_SESSIONCPU_PER_CALL


----------------------------------------------------------------------------------------------------------------------------------------


CONNECT_TIMEIDLE_TIMELOGICAL_READS_PER_SESSIONLOGICAL_READS_PER_CALLCOMPOSITE_LIMITPRIVATE_SGA integer K/M / UNLIMITED /DEFAULT

Ces limites de ressource sont prises en compte de faon permanente si le paramtred'environnement RESOURCE_LIMIT = TRUE ; elles peuvent tre temporairement prises encompte l'aide de la commande ALTER SYSTEM SET RESSOURCE_LIMIT=TRUE.

Le dictionnaire de donnes contient toutes les informations sur chaque utilisateur et chaqueprofil; ces informations sont conserves dans les vues suivantes:

- ALL_USERS, USER_USERS, DBA_USERS- USER_TS_QUOTAS, DBA_TS_QUOTAS- USER_RESOURCE_LIMITS, DBA_PROFILES, RESOURCE_COST- V$SESSION,V$SESSTAT, V$STATNAME

V- LE SCHEMAS

A chaque utilisateur est associ un schma: ensemble des objets accessibles cet utilisateur:tables, index, vues, squences, synonymes, clusters, database links, procdures et packages.A chaque schma correspond un espace logique de stockage dans un tablespace de la base dedonnes; il ny a pas de relation entre un schma et un tablespace: un tablespace peut contenirplusieurs schmas et un schma peut tre situ sur plusieurs tablespaces.Un schma est dfini par la requte CREATE SCHEMA qui garantit la cration de plusieurstables vues et droits en une seule opration.

CREATE SCHEMA AUTHORIZATION schema_nameCREATE TABLE commandeCREATE VIEW commandeGRANT commande

VI-QUESTIONS

I/ Crer les tablespaces APPLI,TEMP et RBS avec les mmes caractristiques que letablespace

II/ Crer les utilisateurs suivants:

Utilisateur Tablespace par dfaut Tablespace temporaireADMIN APPLI TEMPDEVEL TEMPUTIL1 TEMPUTIL2 TEMP

Vrifier que tous les utilisateurs ont t crs dans les bons tablespaces


----------------------------------------------------------------------------------------------------------------------------------------


III/ Modifier les quotas des utilisateurs de la manire suivante:

Utilisateur Quota assigner TablespaceADMIN Aucun SYSTEM

Illimit APPLIDEVEL Aucun SYSTEM

100K UTIL1 Aucun TousUTIL2 Aucun TousVrifiez ces modifications

IV/ Connectez-vous sous les comptes ainsi crs; que se passe-t-il?

V/ Crer les utilisateurs suivants avec les privilges indiqus

Utilisateur/ Tablespace Tablespace Privilgesmot de passe par dfaut temporaire

ADMIN_APPLI/ APPLI TEMP CREATE SESSIONADMIN_APPLI CREATE TABLE

CREATE VIEWCREATE SYNONYMCREATE ROLE

ADMIN_SYS/ APPLI TEMP CREATE SESSIONADMIN_SYS CREATE USER, ALTER USER

CREATE ANY TABLE,ALTER ANY TABLE,DROP ANY TABLESELECT ANY TABLECREATE ANY INDEX

Tous ces privilges seront attribus avec la clause WITH ADMIN OPTION

En vous plaant sous le compte SYSTEMa/ Donner aux utilisateurs ADMIN,DEVEL,UTIL la possibilit de se connecter la baseb/ Donner ADMIN la possibilit de crer des tables, des vues et des synonymes

VII/ Charger votre rpertoire les fichiers /u/profs/jcg/dept.sql et /u/profs/jcg/emp.sqlSans changer les privilges SYSTEM des utilisateurs,A partir du fichier dept.sql, crer la table dept de faon ce quelle appartienne ADMINA partir du fichier emp.sql, crer la table emp de faon ce quelle appartienne DEVEL

VIII/ Dans quels tablespaces ont t cres ces deux tables?

IX/ Donner UTIL1 la possibilit de visualiser en une seule requte le nom des employs(emp.ename) et leur lieu de travail (dept.loc) (sans pouvoir visualiser les autres champs destables emp et dept).


----------------------------------------------------------------------------------------------------------------------------------------


X/ Donner UTIL2 la possibilit de mettre jour, supprimer et dinsrer des enregistrementsdans la table emp.

XI/ Donner UTIL2 la possibilit de mettre jour uniquement la colonne LOC de la tabledept.

XII/ Ecrire les requtes permettant de vrifier ces possibilits

XIII/ Vrifier dans le dictionnaire de donnes la liste des privilges de UTIL1 et UTIL2.

XIV/ Quelles conclusions pouvez-vous tirer sur les "rles" des diffrents utilisateurs

XV/ Attribuer lutilisateur les caractristiques suivantes:Tablespace par dfaut: Tablespace temporaire: tempquota sur : 200K

XVI/ Connectez vous sur un compte adquat et crez les rles R1 et R2.

XVII/ Sans lui donner explicitement les privilges, faites en sorte que puisse crerdes tables, des vues et/ou des synonymes.

XVIII/ Vrifier que peut effectivement crer des tables et des synonymes

XIX/ Sans vous dconnecter du compte , faites en sorte quil ne puisse plus crer desynonymes mais toujours crer des tables

XX/ Sans vous dconnecter du compte , faites en sorte quil ne puisse plus crer detables mais toujours crer des synonymes

XXI/ Sans vous dconnecter du compte , rtablissez la situation de dpart

XXII/ Donner le rle R1 par dfaut

XXIII/ Rechercher dans le dictionnaire de donnes, la liste des rles existants ainsi que lesprivilges attribus chaque rle

XXIV/ Faire en sorte que UTIL1 et UTIL2 ne puissent ouvrir que 2 sessions simultanment etne puissent pas rester connects plus de 2 minutes.

Administration d'une base de donnes Oracle7 Mcanismes transactionels

----------------------------------------------------------------------------------------------------------------------------------------


La gestion des accs concurrents a pour objectif d'assurer la srialisation de transactionsmultiples voulant accder simultanment aux mmes donnes; l'excution en parallle de cestransactions fournit le mme rsultat que leur excution en srie mais avec de meilleuresperformances.La gestion des accs concurrents est base sur les concepts d'intgrit des donnes, deconcurrence des transactions et de consistance des donnes; elle utilise essentiellement latechnique de verrouillage des donnes.

-intgrit des donnes: l'intgrit des donnes est assure si les contraintes d'intgritdfinies au moment de la cration des tables sont respectes l'issue de l'excution destransactions.

-concurrence des transactions: la gestion de la concurrence des transactions a pour butd'assurer l'intgrit des donnes lorsque plusieurs transactions accdent simultanment auxmmes donnes.

-consistance des donnes: la consistance des donnes est assure lorsque la transactionutilise, tout le temps de son excution, la valeur de ces donnes au dbut de la transaction,mme si d'autres transactions essaient de modifier tout ou partie de ces donnes.

I- TRANSACTIONS ET ACCES CONCURRENTS

Une base de donnes est dans un tat cohrent si toutes les valeurs contenues dans la basevrifient toutes les contraintes dintgrit dfinies sur la base.Une transaction est un ensemble dordres de mise jour, INSERT, UPDATE ou DELETE,qui font passer la base dun tat initial cohrent un tat final cohrent; elle se termine par unordre (explicite ou implicite) de validation (COMMIT) ou dannulation (ROLLBACK).

Transactions concurrentes: Il y a concurrence daccs une donne quand celle-ci dot tremodifie simultanment par au moins deux transactions.

a/ Lecture cohrenteLa lecture cohrente assure quune transaction non encore valide naffecte en rien lavisualisation des donnes pour lensemble des utilisateurs:

- lutilisateur initiateur de la transaction visualisera la donne aprs sa modification- les autres utilisateurs visualiseront la donne avant sa modification

b/ Lecture incohrente ou impropreLa mme transaction utilise des donnes valides et des donnes non validesexemple 1:T1 T2Dbut transaction

Dbut transactionUPDATE comptesSET solde = 25000WHERE num_compte = 7;

MECANISMES TRANSACTIONNELS


----------------------------------------------------------------------------------------------------------------------------------------


SELECT soldeFROM comptesWHERE num_compte = 7;

ROLLBACK;

exemple 2:T1 T2

Dbut transactionDbut transaction

UPDATE comptesSET solde = solde - 200WHERE num_compte = 7;

SELECT sum(solde)FROM comptesWHERE num_compte in (7,16);

UPDATE compteSET solde =solde + 200WHERE num_compte = 16;COMMIT;

Dans les deux cas T2 lit une valeur impropre.

c/ Lectures non reproductibles

Dans une mme transaction, deux requtes identiques ne donnent pas le mme rsultat,celui_ci ayant t modifi entre temps par une autre transaction

T1 T2Dbut transaction

Dbut transactionSELECT PointsFROM resultatWHERE num_cours = 5and num_etudiant = 7;

UPDATE resultatSET Points =Points+2WHERE num_cours = 5and num_etudiant = 7;

SELECT PointsFROM resultatWHERE num_cours = 5and num_etudiant = 7;COMMIT;La lecture de points nest pas reproductible.


----------------------------------------------------------------------------------------------------------------------------------------


d/ Perte de mise jourUne transaction peut modifier des donnes non valides

T1 T2Dbut transaction

Dbut transactionSELECT nb_places_dispoINTO dispoFROM volWHERE num_vol = 10AND date_vol = 1-MAR-96;

SELECT nb_places_dispoINTO dispoFROM volWHERE num_vol = 10AND date_vol = 1-MAR-96;

IF dispo >= 3 THEN enregistrer la rservation ;UPDATE volSET nb_places_dispo = nb_places_dispo - 3WHERE num_vol =10AND date_vol = 1-MAR-96;END IF;

IF dispo >= 3 THEN enregistrer la rservation ;UPDATE volSET nb_places_dispo = nb_places_dispo - 3WHERE num_vol =10AND date_vol = 1-MAR-96;END IF;

COMMIT;COMMIT;

La valeur de nb_places_dispo est errone.

Par la commandeSET TRANSACTION ISOLATION LEVEL SERIALIZABLE/READ COMITTED,Oracle permet deux niveaux d'isolation des transactions les unes par rapport aux autres.

READ COMITTED: mode de fonctionnement par dfaut d'Oracle; il vite les lecturesincohrentes ou les pertes de mise jour mais pas les lectures non rptitives.SERIALIZABLE: cette option empche une transaction de modifier une donne mise jourpar une autre transaction non valide; on vite ainsi les anomalies de lecture non rptitive.Ce mode est pnalisant car il limite le fonctionnement en parallle des transactions.


----------------------------------------------------------------------------------------------------------------------------------------


II- LES VERRROUS ORACLE

Pour grer les concurrences daccs aux donnes, Oracle utilise des mcanismes deverrouillage et la notion de transaction.Il nexiste pas de concurrence daccs aux donnes en LECTURE: un utilisateur qui lit unedonne ninterferera pas avec une transaction; une transaction ninterferera pas avec uneopration de lecture de la mme donne.Il existe une concurrence daccs en MISE A JOUR: la premire transaction qui accde ladonne est prioritaire et positionne un verrou sur les ressources accdes(table ou ligne).Deux types de verrous:

Les verrous exclusifs: la premire transaction qui verrouillera la ressource de faon exclusivesera la seule pouvoir la modifier.Les verrous partags: ils assurent le partage des ressources en fonction du type doprationeffectu sur ces ressources.

Les verrous sont tous maintenus sur les ressources jusqu la fin de la transaction; ils sontlibrs quand la transaction est valide ou annule.

Oracle dtecte le phnomne dattente mutuelle (DEADLOCK); il rsoud automatiquementles DEADLOCK en annulant toujours lordre qui les a provoqus (STATEMENT LEVELROLLBACK).Pour grer la lecture cohrente, Oracle utilise automatiquement les images avant de toutesles donnes modifies. Un utilisateur peut grer le mcanisme de lecture cohrente au niveaude la transaction en utilisant explicitement lordre:

SET TRANSACTION READ ONLY

Dans ce cas, seules les oprations de lecture sont autorises, les autres utilisateurs peuventmodifier les ressources, les donnes valides par dautres transactions ne seront pas visibles.Lordre SET TRANSACTION READ ONLY est obligatoirement la premire instruction de latransaction; les seules autres instructions autorises dans la transaction sont SELECT (sans laclause FOR UPDATE), COMMIT, ROLLBACK ou une non-DML instruction (SET ROLE,ALTER SYSTEM, LOCK TABLE).

Les diffrentes catgories de verrous

Les verrous DDL(data dictionary locks)Ils sont poss sur les structures des objets(tables) et sont utiliss pour viter les modificationsde structure pendant les requtes:

Le verrou exclusif: est pos sur lobjet si aucun autre type de verrou (DDL et DML) nest pasdja acquis.Le verrou partag: est pos sur lobjet si lun des ordres suivants est utilis:AUDIT, NOAUDIT, GRANT, REVOKE, COMMENT,CREATE(REPLACE)(TABLE/VIEW/SYNONYM/FUNCTION,PROCEDURE/PACKAGE).


----------------------------------------------------------------------------------------------------------------------------------------


Le verrou de parsing: pos sur chaque objet rfrenc dans un ordre SQL et utilis pourdterminer si lanalyse stocke dans la zone de partage des ordres SQL ne serait pas obsoltedu fait dun changement de structure de lobjet accd.

Les verrous DMLTout ordre modifiant une donne positionne:

-un verrou de type exclusif sur la ligne contenant la donne-un verrou de type exclusif ou partag sur la table

Les cinq verrous DML, du plus libral au plus restrictif, sont:

- RS: Row Share- RX: Row eXclusive- S: Share- SRX: Share Row eXclusive- X: eXclusive

ROW SHARE (RS)

Un verrou de type ROW SHARE (RS)interdit l'accs, aux autres utilisateurs, desenregistrements slectionns pour une mise jour ultrieure. Il

-verrouille les enregistrements concerns et attend la mise jour-autorise la visualisation de tous les enregistrements de la base, y compris ceux de la table concerne par la transaction en cours-autorise linsertion, la mise jour et la suppression de tous les enregistrements non verrouills, y compris dans la table concerne par la transaction en cours.-est compatible avec les verrous RS,RX,S et SRX-permet de se prmunir contre la pose dun verrou X-se pose de manire explicite parSELECT....FROM table.....FOR UPDATE OF colonneLOCK TABLE table IN ROW SHARE MODE [NOWAIT]

Loption NOWAIT rend le contrle la transaction si la table fait dja lobjet duneinstruction LOCK de la part dune autre transaction; si cette clause est omise, la transactionest mise en attente jusqu la libration des verrous installs sur la table.Un verrou de type ROW SHARE permet des accs concurrents la table; il interdit dautrestransactions de placer un verrou EXCLUSIVE sur la table.

ROW EXCLUSIVE (RX)

Un verrou de type ROW EXCLUSIVE (RX) est mis en place automatiquement par Oracleavant l'excution d'un ordre INSERT,UPDATE,DELETE; il permet l'utilisaeur de modifiercertains enregistrements tout en laissant d'autres utilisateurs modifier d'autres enregistrementsde la mme table. Il

-verrouille les enregistrements concerns et effectue la mise jour dans la table-autorise la visualisation de tous les enregistrements de la base, y compris ceux de la table concerne par la transaction en cours-autorise linsertion, la modification et la suppression de tous les enregistrements non verrouills, y compris dans la table concerne par la transaction en cours-est compatible avec les verrous RS et RX


----------------------------------------------------------------------------------------------------------------------------------------


-permet de se prmunir contre la pose de verrous S, SRX et X-se pose de manire implicite dans les ordres INSERT, UPDATE, DELETE-se pose de manire explicite pour utiliser le verrou XLOCK TABLE table IN ROW EXCLUSIVE MODE [NOWAIT]

Un verrou de type ROW EXCLUSIVE a le mme effet que ROW SHARE et interdit en plusle verrouillage en mode SHARE par dautres transactions.

SHARE (S)

Un verrou de type SHARE (S) est employ lorsque la transaction utilise la table eninterrogation uniquement et exige que cette table ne soit pas modifie par dautrestransactions. Il

-empche toutes les insertions, modifications et suppressions denregistrements dans la table concerne si un autre verrou de type SHARE est dja positionn sur la table-autorise la visualisation dans le but de verrouiller certains enregistrements dans la table concerne par la transaction en cours-est compatible avec un autre verrou RS ou S-permet de se prmunir contre la pose de verrous RX, SRX et X-se pose de manire explicite parLOCK TABLE table IN SHARE MODE [NOWAIT]

exemple:

LOCK TABLE dept IN SHARE MODE

UPDATE empSET sal = sal *1.1WHERE deptno in (SELECT deptno FROM dept WHERE loc = DALLAS)

UPDATE budgetSET total = total * 1.1WHERE deptno in (SELECT deptno FROM dept WHERE loc = DALLAS)

COMMIT

Plusieurs transactions peuvent installer un verrou de type SHARE sur la mme table en mmetemps.

SHARE ROW EXCLUSIVE (SRX)

Un verrou de type SHARE ROW EXCLUSIVE (SRX) est employ pour visualiser unetable entire; il permet aux autres transactions de visualiser les lignes mais leur interdit deverrouiller la table en mode SHARE ou de faire des mises jour. Il

-empche toutes insertions, modifications et suppressions denregistrements dans la table concerne par la transaction en cours


----------------------------------------------------------------------------------------------------------------------------------------


-autorise la visualisation dans le but de verrouiller certains enregistrements dans la table concerne par la transaction en cours-est compatible avec un autre verrou RS-permet de se prmunir contre la pose de verrous RX, S, SRX, X-se pose de manire explicite parLOCK TABLE table IN SHARE ROW EXCLUSIVE MODE [NOWAIT]

EXCLUSIVE (X)

Un verrou de type EXCLUSIVE est employ lorsque la transaction exige un accs immdiat la table pour raliser une opration de mise jour; il autorise les interrogations mais interdittoute autre action.

-empche toutes insertions, modifications et suppressions denregistrements dans la table concerne par la transaction en cours-autorise la visualisation de tous les enregistrements de la base, y compris dans la tableconcerne par la transaction en cours-nest compatible avec aucun autre verrou-se pose de manire explicite par

LOCK TABLE table IN EXCLUSIVE MODE [NOWAIT]

Ce mode est trs contraignant pour les autres utilisateurs; le dblocage de la table doit tre trsrapide; un verrouillage en mode EXCLUSIVE doit tre suivi rapidement d'un COMMIT oud'un ROLLBACK.

III-QUESTIONS

I/Montrer que les trois cas dincohrence, lecture impropre, lecture non reproductible et pertede mise jour ne peuvent pas se produire avec les verrous poss automatiquement par Oracle.Pour cela vous simulerez deux transactions essayant de mettre simultanment jour lesmmes donnes en ouvrant deux transactions sqlplus

-lune par lutilisateur ADMIN, propritaire de la table dept-lautre par lutilisateur DEVEL, propritaire de la table emp

Les deux utilisateurs auront les privilges dinterrogation et de mise jour sur les deux tables.

II/Donner aux deux utilisateurs DEVEL et ADMIN le privilge LOCK ANY TABLE.En utilisant uniquement la table admin.dept, montrer les diverses possibilits offertes par lesdiffrents types de verrous.ADMIN verrouillant la table dept successivement dans les modes ROW SHARE, ROWEXCLUSIVE, SHARE, SHARE ROW EXCLUSIVE et EXCLUSIVE , examinez, danschaque cas, les diverses possibilits pour DEVEL de poser des verrous sur cette table oudeffectuer des mises jour sur un enregistrement que ADMIN aura verrouill ou nonauparavant ex :les transactions essaieront deffectuer la mise jour suivante:update admin.dept set loc = NEW YORK where deptno=20.

Administration d'une base de donnes Oracle7 Optimisation des traitements

----------------------------------------------------------------------------------------------------------------------------------------


I-L'OPTIMISATION DES TRAITEMENTS

Lobtention de bonnes performances dune application dpend dune bonne dfinition desstructures(logiques et physiques) de mmorisation de linformation et de lefficacit dutraitement des ordres SQL.

Lallocation approprie de ressources mmoire peut avoir un large impact sur lesperformances; ces ressources sont les zones de traitement SQL et PL/SQL, le dictionnaire dedonnes et le buffer; une bonne dfinition de ces ressources entrainera une amlioration desperformances par la rduction de lanalyse des ordres SQL ou PL/SQL et de la pagination.

La rpartition des donnes sur les disques permettra de diminuer le nombre dentres/sorties.Il est conseill de mmoriser sur des disques diffrents:

- les fichiers data files et les fichiers Redo Log- les tables de donnes- les tables et les index

Une amlioration des performances sera galement obtenue par une dfinition approprie dela taille des rollback segments, de larchitecture des serveurs partags et des buffers Redo Loget des zones mmoire rserves aux oprations de tri.

Loptimisation des requtes SQL peut tre obtenue en

* crant des index appropris* crant des clusters pour optimiser les oprations de jointure* choisissant un mode de traitement des requtes* comparant plusieurs solutions SQL pour la mme requte

La cration dindex ou de cluster est tudie dans le chapitre ORGANISATION PHYSIQUEDUNE BASE DE DONNEES.

II- LES PHASES DE TRAITEMENT DES REQUTESLe traitement de tout ordre SQL Oracle ncessite plusieurs phases successives: PARSE,BIND,DESCRIBE, DEFINE,EXECUTE et FETCH.

PARSE consiste transmettre au serveur la chane de caractres constituant lordre SQL; leserveur dcompose cette chane afin didentifier les objets de la base de donnes qui vont tresollicits. A ce moment-l, les droits de lutilisateur sur les objets concerns sont contrls.Un plan dexcution est ensuite choisi en fonction des connaissances statistiques du serveursur les objets mais aussi des ventuels hint destination de loptimiseur.BIND identifie les zones mmoire, taille et type correspondant aux variables de lordre SQL(variables de la clause WHERE, variables contenant les valeurs insrer pour lINSERT ouencore les variables de remplacement pour lUPDATE).

OPTIMISATION DES TRAITEMENTS

Administration d'une base de donnes Oracle7 Optimisation des traitements

----------------------------------------------------------------------------------------------------------------------------------------


DESCRIBE rcupre dans le dictionnaire de donnes les descriptions prcises d

administration+oracle7

Documents