Oracle DB 10g - Aide informatique n°1

2016
Oracle DB 10g
ADMINISTRATION WINDOWS
OLIVIER DEHECQ
DEHECQ Olivier – http://aide.informatique1.fr
Page 1
Table des matières
1
Les bases de l’architecture Oracle ....................................................................................................... 4
2
Installation .............................................................................................................................................. 7
3
Création d’une base de données (assistant) ......................................................................................11
4
Le listener (p60) ...................................................................................................................................12
5
Les statuts d’une base de données (p90) ..........................................................................................15
6
La gestion de l’instance (p146)...........................................................................................................17
7
Gestion des fichiers de contrôle et de journalisation ........................................................................19
8
Gestion des tablespaces et des fichiers de contrôle (p171) ............................................................23
9
Flashback (p327 -) ...............................................................................................................................27
10
Créer une base de données manuellement ...................................................................................28
11
Gestion des tables et des index p243 ............................................................................................32
12
La gestion des utilisateurs (217) ....................................................................................................35
13
Index .................................................................................................................................................40
DEHECQ Olivier – http://aide.informatique1.fr
2
Signalétique
Nota, astuce :
Contient une partie serveur web qui traite les réponses statiques.
Important, à retenir :
Ceci est une chose importante
Commande MS-DOS
C:\> c:\tomcat5.5\bin\startup.bat
Commande UNIX
# /tomcat5.5/bin/startup.sh
Commande SQL
# /tomcat5.5/bin/startup.sh
Chemin de fichier, dossier, emplacement sur le disque
Fichier web.xml
Exemple de contenu de document
<Host name="localhost" appBase="webapps" unpackWARs="true" autoDeploy="true" />
Contenu du fichier web.xml
<welcome-file>index.html</welcome-file>
Contenu du fichier server.xml
port
"8080"
port d’écoute du connecteur
Autre contenu de fichier :
<role rolename="RUserHelloWorld"/>
Spécifique aux documents xml :
Balise
Nom de propriété
Valeur
Commentaire
DEHECQ Olivier – http://aide.informatique1.fr
3
1
Les bases de l’architecture Oracle
Oracle est un Système de Gestion de Bases de Données Relationnelles (SGBDR). Il prend en compte le
SQL, utilise aussi des tables et vues système. Oracle est un logiciel. Il faut installer des clients Oracle
pour se connecter aux bases de données Oracle.
On peut acheter les licences par utilisateur ou par processeur. A titre d’exemple, Oracle 11gR2
standard va jusqu’à 6 cœur et coute 17500€ par cœur sur le serveur sur lequel le moteur est installé.
Si vous faites de la haute disponibilité, une machine virtuelle contenant le moteur Oracle installé en
version standard pouvant basculer entre 2 hyperviseurs physiques ayant chacun 2 cœurs, cela coute
4 x 17500€. S’il y a plus de 6 cœurs, il faut une licence Enterprise (35000€ par cœur). Si vous
installez au moins une fonctionnalité non comprise dans la version standard, il faut une licence
Enterprise.
Oracle coûte donc cher, et il ne faut donc pas « jouer » avec.
1.1
Présentation générale
Gammes :


Standard : limitée à 6 processeurs
Entreprise : au-delà de 6 processeurs ou au moins une fonctionnalité « non-standard »
installée
 Personnel : uniquement sous Windows
 XE : gratuit, pas en production, 20 à 25 connexions simultanées
Oracle est spécialisé dans les grands comptes, est indépendant de l’OS (seule l’installation est
différente, la base est identique sous Windows ou Unix) ; beaucoup de possibilités de paramétrage ;
principe de sauvegarde simple et efficace ; un seul outil d’administration qui est centralisé et permet
toutes les taches d’administration en mode web (la DBConsole).
Les éléments d’Oracle :





1.2
Oracle database : la base de données
Oracle application server : gère la base de données
Grid control : outils d’administration (en fait, la DBConsole)
Outils pour les développeurs
Les clients Oracle, avec des composants gratuits qu’on peut ajouter (driver ODBC, SQL*Plus,
assistants de connexion, etc.)
La Base de Données (p15)


(1+) Datafiles : fichiers utilisateurs
(1+) Control files : contiennent la structure de la BD, permettent de vérifier sa cohérence, font
référence aux autres fichiers.
 (2+ groupes) Redo log files : fichiers de journalisation, permettent de faire des rollback (des
retours en arrière). Contient les modifications apportées à la BD. Permet de récupérer
l’instance après un arrêt anormal (pensez à faire des Backups RMAN de votre DB)
DB_NAME : nom de la base de données
Mode ARCHIVELOG : active un archivage des logs pour restaurer la BD facilement. Très utile pour les
BD qu’on ne peut pas arrêter. A faire en complément de backups RMAN.
Un schéma contient :
Tables, vues, synonymes (simplification des requêtes, passe outre le schéma mais penser aux droits),
index, séquence, programmes PL/SQL (procédures, fonctions, triggers, packages, jobs).
DEHECQ Olivier – http://aide.informatique1.fr
4
1.3
Le Serveur (p19-25)
Gère l’allocation des ressources. Une instance peut ouvrir plusieurs BD, mais une seule à la fois !
ORACLE_SID : variable d’environnement, définit l’instance utilisée à un instant t
Utilisateurs:



SYS : plus haut niveau. Propriétaire du dictionnaire, démarre et paramètre l’instance
SYSTEM : propriétaire de tables complémentaires
SYSDBA et SYSOPER permettent de faire certaines manip (sauvegarde, démarrage/arrêt, etc.)
Init<%DB_NAME%>.ora (exemple : initINSTANCE.ora) : très important au niveau de l’instance. Il permet
de faire le lien avec les BD montables et de connaitre la taille allouée à l’ensemble de la mémoire
(=SGA), contient les paramètres d’archivelog et autres paramétrages de l’instance.
1.3.1
SGA (System Global Area) – une par instance:
Les 3 zones mémoire de la SGA :



1.3.2
Database buffer cache : les données sont stockées temporairement dans cette zone avant leur
écriture sur le disque
Redo log buffer cache : enregistrement des modifications insert/update/delete rapportés à la BD.
Si un des membres d’un groupe manque, la BD continue de fonctionner.
Shared pool : pour le fonctionnement du moteur
o Library cache : zone mémoire dédiée aux requêtes (cache des requêtes)
o Dictionnary cache : zone mémoire dédiée au DICT (équivalent de master)
Les processus
PMON : en cas de plantage de process utilisateur, se charge du rollback et libère les verrous
SMON : récupère l’instance après un arrêt anormal
DBWR : (DBWriter) récupère les données du Database Buffer Cache et les écrit dans le Data Files
LGWR : (LogWriter) récupère les données du Redo Log Buffer Cache et les écrit dans le Redo Log File
CKPT : Checkpoint : permet l’écriture des logs du LGWR
Autres : processus pour les requêtes notamment
1.3.3
PGA (Program Global Area)
Aussi une zone mémoire. Permet l’exécution de certaines requêtes (exemple : tri).
1.3.4
Le listener
C’est un module d’écoute. C’est un service qui permet d’utiliser TCP/IP pour accéder à la BD. Il
écoute par défaut sur le port 1521. Il est associé à 1+ BD.
Listener.ora : fichier de configuration du listener, contient la liste des listeners
DEHECQ Olivier – http://aide.informatique1.fr
5
1.4
Sur les clients
Tnsnames.ora : fichier de configuration du client Oracle. Liste les instances/bases auxquelles le client
peut accèder.
SqlNet : protocole permettant une communication avec le serveur Oracle (protocole propriétaire
Oracle. Contient aussi son fichier de paramétrage : sqlnet.ora
1.5
L’administrateur de base de données




1.6
DBA de développement : travaille avec les dev’ (souvent un dev’ à la base)
DBA de production : joignable 24/24, pas de droit à l’erreur (le pompier)
DBA de recette : s’occupe des tests de migration
Le chef des DBA : gère toute l’équipe
Le schéma de fonctionnement d’Oracle DB 10g
DEHECQ Olivier – http://aide.informatique1.fr
6
2
Installation
Les variables %DB_NAME% et %ORACLE_SID% sont les mêmes lors de l’installation.
Nom de l’installeur : OUI (Oracle Universal Installer).
Installation par défaut de la version entreprise. Attention aux prérequis
Après l’installation, se connecter à l’instance en mode web (la GRID | databasecontrol). Une page web
par instance.
2.1.1
L’arborescence
C:\oracle\production\10.2.0\
.\admin\
.\orcl\
.\oradata\
.\X fichiers .ctl
.\Y fichiers redo.log
.\Z fichiers .dbf
.\db_1\
.\database\
.\NETWORK\ADMIN\
.\flash_recovery_area\
contient un dossier par instance (contient la trace)
contient les fichiers de bases de données
fichiers de contrôle)
fichiers de redo)
fichiers de données)
emplacement d’installation (oracle_home)
contient initorcl.ora (données un lien)
contient listener.ora, sqlnet.ora et tnsname.ora
corbeille (une par instance)
Plusieurs programmes ont aussi été installés.
2.1.2
Les services
OracleDBConsoleORCL (un par instance, permet d’accéder à la grid)
OracleJobScheduler
(un par instance, désactivé par défaut)
Oracle…iSQLPlus
(un par installation, permet d’utiliser iSQL+)
Oracle…TNSListener
(port 1521 par défaut, un par installation normalement)
OracleServiceORCL
(un par instance, moteur base de données)
Les services liés à Oracle DB 10g
2.1.3
La base de registre
HKLM / Software / Oracle / KeyOracl… :
ORACLE_SID
nom de l’instance utilisée par défaut
ORACLE_HOME emplacement d‘installation
DEHECQ Olivier – http://aide.informatique1.fr
7
2.2
SQLPlus
La fenêtre de SQL*Plus
SQL> DESC all_tables;
liste des colonnes de la table all_tables
SQL> SELECT owner,table_name FROM all_tables;
liste des tables
SQL> ED
ouvre un éditeur qui contient la dernière requête, puis enregistrer et quitter
SQL> /
exécute la requête de la fenêtre ED
SQL> SPOOL c:\fic.sql
met en cache les requêtes à venir
SQL> SELECT … ;
SQL> SPOOL OF
sauvegarde le spool en cache
SQL> SELECT ‘SELECT * FROM’,
TABLE_NAME,
’;’ FROM ALL_TABLES WHERE TABLE_NAME LIKE ‘c%’;
Affiche ‘select * from T…’ pour toutes les tables commençant par T
@NomDuScript
Exécute un script PL/SQL
2.3
Via un shell
La ligne de commande
C:\> SET ORACLE_SID = ORCL
Positionner la valeur de l’instance par défaut
C:\>sqlplus system/password
Ouvre SQLPlus en ligne de commande ; dépend de la valeur d’ORACLE_SID
SQL> CONNECT system/password@instance
Changer d’instance, se fait dans sqlplus
SQL> CONNECT sys/password AS SYSDBA;
Se connecter en tant que sys
DEHECQ Olivier – http://aide.informatique1.fr
8
Groupe ora_dba : groupe Windows ayant le droit de SYSDBA (Administrateur en fait partie)
SQL> CONNECT / AS SYSDBA
Permet de se connecter avec un compte membre de oradba (groupe Windows)
C:\> SQLPLUS /NOLOG
Permet de se connecter à l’instance sans être connecté à l ‘instance
SQL> SET SQLPROMPT "PROD> "
PROD>
Permet de changer le prompt
2.4
Grid : console d’administration Web (p76)
http://localhost:1158/em/
Service : OracleDBConsole%instance%
un par instance
EMCTL : programme de gestion des services
C:\> SET ORACLE_SID = ORCL
C:\> EMCTL stop dbconsole
Permet d’arrêter le service OracleDBConsole
Sur la page d’accueil d’Entreprise Manager, on peut voir les propriétés principales, arrêter l’instance,
etc.
Onglet Administration : la plupart des paramètres, les objets de la BD, les utilisateurs (droits, etc.). On
peut toujours afficher le code SQL
Administration > Configuration de la BD > Tous les paramètres d’initialisation

Les paramètres d’init.ora
Administration > Objets de base de données > Tables

Afficher les tables de scott : schéma = SCOTT
SELECT * FROM SCOTT.EMPL

2.5
Accéder à la table EMPL du schéma SCOTT
Alertes et traces (p87)
C:\oracle\production\10.2.0\admin\NomDeLinstance
 Alert_orcl.log
 xxx.trc
2.6
fichier d’alertes
fichiers de traces
Le dictionnaire de données (p30)
: Contient la liste des tables systèmes, les utilisateurs, leurs privilèges et rôles, les objets, les
contraintes, les paramètres, les ressources physiques allouées à la base.
DICT
SELECT TABLE_NAME FROM DICT WHERE TABLE = TABLE_NAMES;
Créé au moment de la création de la base, et mis à jour au fur et à mesure. C’est le fichier system.dbf
qui contient le dictionnaire.
Les champs des tables peuvent être en majuscules ou minuscules.
Les vues statiques :
Informations issues de tables.
 USER_xxxx
 ALL_xxx
 DBA_xxx
liste des objets de l’utilisateur qui est connecté
tous les objets auxquels l’utilisateur connecté a accès
informations sur les objets de la BD (accessible à DBA)
DEHECQ Olivier – http://aide.informatique1.fr
9
liste des utilisateurs
SELECT * FROM ALL_USERS;
Les vues dynamiques :
Informations issues de fichiers de contrôles. Ne sont accessibles qu’au DBA, commencent par V%
et donnent des infos sur l’état actuel de la Base de données.
DESC V%DATABASE;
SELECT DBID, NAME FROM V%DATABASE;
donne des infos sur la BD à laquelle on est connecté
base à laquelle on est connecté
DEHECQ Olivier – http://aide.informatique1.fr
10
3
Création d’une base de données (assistant)
3.1.1
Les étapes
Oracle > Outils de configuration ... > Assistant configuration d’une base de données








Créer une base de données
Choisir le modèle
Ajouter le nom (nom de BD limité à 8 caractères)
Configuration mail, sauvegarde planifiée (sera fait plus tard)
Définir les mots de passe pour SYS et SYSTEM
Sélection du système de fichiers (système de fichiers classique par défaut)
Emplacement des fichiers
Définition de la corbeille (emplacement et taille), et activation ou non de l’archivage (archive
log)
 Schéma, scripts qui s’exécuteront à la fin de la création de la base
 Paramétrages (valeurs standards ok)
Récapitulatif :


Trois fichiers de contrôle,
Fichiers de données : system.dbf et sysaux.dbf pour l’administration, user.dbf et temp.dbf
pour l’utilisateur
 Groupes de fichiers de journaux : 3 groupes, 1 fichier par groupe (valeurs par défaut)
Créer la base ; terminer ; ok.
3.1.2
Implications
Nouvelle adresse de Database Control dans le menu démarrer
Cliquer dessus
Nouveaux services : OracleDBConsoleTest + OracleEngine + JobScheduler
ORACLE_SID est modifié pour correspondre à la base nouvellement installée (ici Test)
3.1.3
Programmes
SqlPlus en ligne de commande :
C:\> Set oracle_sid = orcl
C:\> sqlplus system/password
SQL> SELECT name FROM v$database;
SQL> CONNECT system/password@test
Arborescence:
Fichier de paramétrages initiaux de la base « BASE » :
%Oracle_Home%\database\initBASE.ora
DEHECQ Olivier – http://aide.informatique1.fr
11
4
Le listener (p60)
Permet à 2 machines de communiquer :


Un serveur peut écouter un serveur
Un serveur peut écouter un client
OracleNet doit être installé et configuré sur le client ET sur le serveur.
Principe de fonctionnement
4.1.1
Configuration
Pour configurer le listener (coté serveur) :

listener.ora (à la main)
Assistant graphique NetManager (Oracle > Outils de configuration >Net Manager)
Un fichier listener.ora par serveur. Le listener par défaut s’appelle ‘listener’
Emplacement du listener : %Oracle_Home%\NETWORK\ADMIN\listener.ora
Fichier de configuration du listener :
SID_LIST_LISTENER =
sid_list_nom_du_listener (informations du service listener)
(SID_LIST =
(SID_DESC =
descripteur d’ecoute 1
(SID_NAME = PLSExtProc)
peut écouter sur toutes les bases
(ORACLE_HOME = C:\oracle\product\10.2.0\db_1)(PROGRAM = extproc))
(SID_DESC =
descripteur d’écoute 2
(GLOBAL_DBNAME = INSTANCE1)(SID_NAME = MABASE1))
)
LISTENER =
nom_du_listener (ports d’écoute du listener)
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
extproc est utilisé par pl/sql
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.1)(PORT = 1521)) infos d’écoute TCP
(ADDRESS = (PROTOCOL = IPC)(KEY = MABASE1))
écoute pour mabase1
)
)
4.1.2
Utilitaire lsnrctl
C:\> lsnrctl start|stop <listener>
listener est le nom du service listener à démarrer | arrêter
Arrêt du listener
DEHECQ Olivier – http://aide.informatique1.fr
12
4.1.3
Configuration d’un nouveau listener avec Oracle Net





Configuration d’un processus d’écoute
Ajouter
Nom : LISTENER2
Protocole sélectionné : TCP
Port utilisé : 1522
Cependant, à l’heure actuelle, ce listener est incapable de communiquer avec une instance
Ajouter une base de données au listener2 :





Dans NetManager, sur LISTENER2, « Service de base de données »
Ajouter le SID de la base de données : Test
Puis Menu du haut > Fichier > Enregistrer la configuration réseau
Le fichier listener.ora a été mis à jour
Le listenerLISTENER peut écouter n’importe quelle base de données.
SID_LIST_LISTENER =
sid_list_nom_du_listener (informations du service listener)
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
peut écouter sur toutes les bases
(ORACLE_HOME = C:\oracle\product\10.2.0\db_1)(PROGRAM = extproc))
)
SID_LIST_LISTENER2 =
(SID_LIST =
(SID_DESC =
descripteur d’écoute 2 : que la base mabase1
(GLOBAL_DBNAME = INSTANCE1)(SID_NAME = MABASE1))
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.1)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = MABASE1))
)
)
LISTENER2 =
listener2
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.1)(PORT = 1522)) infos d’écoute TCP
)
LISTENER2 est ajouté dans listener.ora
DEHECQ Olivier – http://aide.informatique1.fr
13
4.2
Configuration du client p66
%ORACLE_HOME%\NETWORK\ADMIN\tnsnames.ora : fichier de config, présent que sur le client.
ALIAS_TCP =
alias auquel on se connecte
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.1)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = INSTANCE)
nom de l’instance
)
)
tnsnames.ora
@IP et port
Sur le client on fait : CONNECT login/mdp@alias
4.2.1
Modification de tnsnames.ora
Le but est de pouvoir connecter la base MABASE1 sur le port 1522.
TEST =
alias auquel on se connecte
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.1)(PORT = 1522))
)
(CONNECT_DATA =
(SERVICE_NAME = INSTANCE1)
nom de l’instance
)
)
On modifie le tnsnames.ora
4.2.2
@IP et port
Méthode qui fonctionne (p60-71)
1.
2.
3.
4.
Créer le listener avec Net Manager
Créer une BD, lui configurer un listener
Net Manager, ajouter la BD au listener créé
Configurer Tnsnames.ora et listener.ora
DEHECQ Olivier – http://aide.informatique1.fr
14
5
Les statuts d’une base de données (p90)
On ne démarre pas de BD, mais l’instance
La base de données peut prendre plusieurs états intermédiaires :
 Montée en mémoire mais fermée
 Montée en mémoire et ouverte (accessible)
Les états :
NOMOUNT :
BD pas montée, instance démarrée
MOUNT :
BD montée, instance démarrée
OPEN :
BD ouverte, démarrée, instance démarrée
Vues relatives à l’instance : V$instance, v$sga, v$option, etc.
Vues relatives à la BD : v$database
Montage BD : utilisation du paramètre control_files du fichier d’init pour localiser les fichiers et les
ouvrir (sans les vérifier).
5.1
Démarrage
STARTUP : démarrer l’instance
SQL> STARTUP [NOMOUNT|MOUNT nombdd |OPEN nombdd]
[RESTRICT]
seuls les DBA peuvent se connecter (si
définir un fichier de paramètres autre que l’initBASE.ora
[PFILE]
startup
BD OPEN)
= startup open
Pour modifier un niveau de disponibilité d’une BDD :
ALTER DATABASE nombdd MOUNT|NOMOUNT|OPEN
La base passé par les états nomount puis mount puis open lors de son ouverture quand est est
down
5.2
Arrêt (p96)
Fermer la base, démonter la base, arrêter l’instance
SHUTDOWN [NORMAL|IMMEDIATE|TRANSACTIONNAL|ABORT]
Normal
dès que les utilisateurs se déconnectent,
Immediate
immédiat (rollback effectué avant)
Transactionnal dès
Abort
arrêt de l’instance
que la transaction est finie, arrêt de l’instance
immédiat (pas de rollback effectué, pour une restauration seulement)
Il faut être connecté en SYS AS SYSDBA pour effectuer les opération d’arrêt / démarrage
5.3
Accéder à l’instance même arrêtée
C:\> sqlplus/nolog
ouvre SQLplus sans se connecter
ou
C:\> sqlplus sys AS SYSDBA
connecté à l’instance non démarrée
DEHECQ Olivier – http://aide.informatique1.fr
15
5.4
Lancer un script au démarrage
Script d’arrêt de base :
Dans c:\scripts\, créer un fichier texte ‘arret.sql’ :
SHUTOWN IMMEDIATE
PAUSE
Dans c:\scripts\, créer un fichier texte ‘arret.cmd’ :
SET ORACLE_SID = INSTP
SQLPLUS SYS/oracle AS SYSDBA @c:\scripts\arret.sql
On n’a plus qu’à lancer c:\scripts\arret.bat pour arrêter l’instance INSTP
Script de démarrage de base :
Dans c:\scripts\, créer un fichier texte ‘demarrage.sql’ :
STARTUP OPEN INSTP
PAUSE
Dans c:\scripts\, créer un fichier texte ‘demarrage.cmd’
SET ORACLE_SID = INSTP
SQLPLUS SYS/oracle AS SYSDBA @c:\scripts\demarrage.sql
On n’a plus qu’à lancer c:\scripts\demarrage.bat pour démarrer l’instance INSTP
5.4.1
Chasser les erreurs
En cas d’erreur ORA-12560 : enlever les espaces dans SET ORACLE_SID=INSTP
DESC V$INSTANCE;
SELECT STATUS FROM V$INSTANCE;
SELECT OPEN_MODE FROM V$DATABASE
5.5
lister les infos affichable de l’instance
même lorsque la base est démontée
seulement lorsque la DB est ouverte, RO/RW ..
Gérer le service de l’instance (p101)
Service de l’instance : gérer > services
C:\> ORADIM –SHUTDOWN –SID INSTP -SHUTMODE IMMEDIATE
C:\> ORADIM –STARTUP –SID INSTP
arrêter l’instance instp
démarrer l’instance instp
Tout est loggé dans les fichiers d’alertes et de traces (p87)
5.6
Faire du ménage dans les BDD et les listeners
Démarrer > Oracle > Outils de config et de migration > assistant création de BD
Supprimer les bases de données (autres que ORCL)
Démarrer > Oracle > Outils de config et de migration > Net Manager
Supprimer les listeners (autres que LISTENER)
DEHECQ Olivier – http://aide.informatique1.fr
16
6
La gestion de l’instance (p146)
6.1
Gestion des paramètres d’initialisation
Deux types de paramètres :


Paramètres dynamiques : peuvent être modifiés quand l’instance fonctionne
Paramètres statiques : ne peuvent pas être modifiés quand l’instance est démarrée. Il faut
donc modifier le paramètre dans le fichier et relancer l’instance.
Les paramètres sont visibles dans la vue V$PARAMETER
6.2
Modification des paramètres dynamiques, dans SQLPLUS
Au niveau de la session :
ALTER SESSION SET paramètre=valeur
Au niveau du système :
ALTER SYSTEM SET paramètre=valeur [DEFERRED][SCOPE=MEMORY|SPFILE|BOTH]
Deferred :
pour les prochaines sessions

 Scope=memory :
 Scope=spfile :
Exemple:
modifié pour la session actuelle
modifié dans le fichier de paramètres
Modification d’un paramètre de la mémoire
ALTER SYSTEM SET SGA_TARGET = 400M SCOPE = SPFILE;
6.2.1
Le fichier init.ora
Le fichier %ORACLE_HOME%\database\initBASE.ora contient les paramètres de l’instance
Ce fichier va chercher ses informations dans %ORACLE_HOME%\dbs\initBASE.ora
Ne pas modifier le fichier %ORACLE_HOME%\dbs\init.ora à la main !
Ces paramètres sont aussi accessibles via :
Instance démarrée : OEM(GRID) > Administration > Tous les paramètres d’initialisation
Dans le fichier init.ora :


orcl.paramètre
*.paramètres
modifie les paramètres de cette base dans l’instance
modifie les paramètres de toutes les bases dans cette instance
DEHECQ Olivier – http://aide.informatique1.fr
17
6.2.2
Pfile et Spfile (p90)
SPFILE (fichier initialisation) utilise le fichier PFILE (fichier de paramètres)
Seuls les SYSDBA ou SYSOPER peuvent créer un fichier PFILE.
Créer un PFILE à partir du SPFILE (p119) :
SQL> CREATE PFILE = 'c:\initorcl.ora' FROM SPFILE;
DEHECQ Olivier – http://aide.informatique1.fr
18
7
Gestion des fichiers de contrôle et de journalisation
On ne travaille pas directement sur les Data Files, on travaille sur les tablespaces
(organisation logique).
7.1
La gestion des fichiers de contrôle (p158)
Une BD est toujours constituée au moins d’un fichier de contrôle. Il est conseillé d’en avoir
plusieurs. Utilisé pour connaitre l’emplacement des autres fichiers.
Il est possible de rajouter des fichiers de contrôle dans le fichier init.ora
Ils contiennent un numéro de séquence utilisé pour vérifier la cohérence de la BD.
7.2
Les fichiers de journalisation p (160)
Ils contiennent l’historique des modifications (DML) des données apportées à la BD. Servent
à restaurer en cas d’erreur serveur ou utilisateur.
Ils sont organisés en groupes. Il y a au moins 2 groupes de fichiers, contenant au moins un
fichier (membre) chacun. Ecrit dans les groupes les uns après les autres, quand le tout a été
écrit, le 1er fichier est de nouveau écrasé. Les membres d’un même groupe sont écrits en
miroir (ajoute de la sécurité).
On est donc limité au niveau du retour arrière. Dans ce sens, on crée donc un 3 e groupe
qu’on rajoute après. La recommandation Oracle est que le temps de rotation soit de 20 à
30 minutes.
On rajoute aussi des fichiers au sein des groupes.
DEHECQ Olivier – http://aide.informatique1.fr
19
7.3
Ajouter un fichier de contrôle
L’objectif est de créer un fichier de contrôle sur un autre disque.
7.3.1
Avec SQL PLUS
C:\> SET ORACLE_SID=ORCL
C:\> SQLPLUS SYS AS SYSDBA
SQL> SELECT name FROM V$CONTROLFILE;
-- liste des 3 fichiers de contrôle
SQL> CONNECT SYS AS SYSDBA
SQL> alter system set control_files = 'C:\oracle\product\10.2.0\oradata\ORCL\control01.ctl',
'C:\oracle\product\10.2.0\oradata\ORCL\control02.ctl',
'C:\oracle\product\10.2.0\oradata\ORCL\control03.ctl', 'D:\oracle\control04.ctl' scope=spfile;
SQL> shutdown immediate
-- arrêt de l’instance
SQL> HOST COPY C:\oracle\product\10.2.0\oradata\ORCL\control01.ctl D:\oracle\control04.ctl;
SQL> startup
-- démarrage de la base
SQL> SELECT name FROM v$controlfile;
-- liste les 4 fichiers de contrôle
Ne fonctionne que si on a démarré en utilisant un SPFILE
DEHECQ Olivier – http://aide.informatique1.fr
20
7.4
Gérer les groupes de fichiers de journalisation
7.4.1
Récupérer les informations en interface web
Utilisateur SYS en SYSDBA
Administration > Stockage > Groupes de fichiers de journalisation
Le groupe qui est utilisé a le statut « CURRENT »
On peut visualiser le contenu de chaque groupe
7.4.2
Modifier les paramètres avec SQLPlus
SQL> DESC V$LOG ;
SQL> SELECT * FROM V$LOG ;
Le groupe utilisé actuellement a le statut “CURRENT”
SQL> SELECT * FROM V$LOGFILE ;
Informations sur les membres de tous les groupes
Ajout d'un fichier de journalisation à un groupe (augmente la sécurité du groupe) :
SQL> Alter database add logfile member
'C:\oracle\product\10.2.0\oradata\ORCL\log12.log' To Group 1;
N’est possible que si le groupe est inactif (« INACTIVE »)
Ajout d'un groupe de fichiers de journalisation :
Augmente le temps de rotation des journaux. La taille est de 50Mo
SQL> Alter databaseaddlogfile group 4
'C:\oracle\product\10.2.0\oradata\ORCL\log4.log' size 50M;
Forcer le passage d'un groupe à un autre :
SQL> alter system switch logfile;
Attendre que le groupe qui était « current » avant passé en inactive.
Supprimer un fichier d'un groupe :
Le groupe doit être inactif (select * fromV$log)
SQL >alter database drop logfilemember
'C:\oracle\product\10.2.0\oradata\ORCL\log12.log'
Il faut ensuite le supprimer physiquement.
Supprimer un groupe :
Le groupe doit être inactif (select * fromV$log)
SQL> alter database drop logfile group 4
Déplacer un fichier de journalisation :
Le fichier doit appartenir aux groupes de journalisation
SQL> shutdownimmediate
Arrêt de la base
SQL> host move C:\oracle\product\10.2.0\oradata\ORCL\log4.log C:\log\log4.log
Déplacement du fichier
SQL>startup mount
Monter la base, sans l’ouvrir
SQL> alter database rename file 'C:\oracle\product\10.2.0\oradata\ORCL\log4.log' TO
'C:\log\log4.log';
Modification de l'emplacement dans les fichiers de contrôle:
DEHECQ Olivier – http://aide.informatique1.fr
21
SQL>alter database open
Ouvrir la base
NB: pour supprimer un fichier de journalisation il est possible de devoir préalablement effectuer
un COMMIT pour valider le changement de groupe
Vérifier le Numéro SCN en cours :
SQL>select * from V$LOG_HISTORY


la colonne FIRST_CHANGE correspond au 1er Numéro SCN du group
la colonne NEXT_CHANGE correspond au plus grand Numéro SCN du group
DEHECQ Olivier – http://aide.informatique1.fr
22
8
Gestion des tablespaces et des fichiers de contrôle (p171)
Les objets de la base de données sont stockés (logiquement) au sein de tablespace
On définit des tablespaces différents pour les utilisateurs, de façon à ce que leurs objets soient
stockés dans des fichiers logiques différents. Quand on crée un tablespace, on peut lui associer un ou
plusieurs fichiers.
5 types de tablespaces qui ont été créés en même temps que la création de la BD :





SYSTEM :
(obligatoire) stocke le dictionnaire de données (contient DICT)
SYSAUX :
(obligatoire) enregistre les objets associés aux schémas
TEMP : swap de la PGA
UNDO :
il sert aux retours arrière (ROLLBACK, FLASHBACK, RECOVER notamment)
USERS :
contient les données utilisateurs
Les tablespaces peuvent être du type :


BIGFILE : un seul fichier de données
SMALLFILE : plusieurs fichiers de données (sur plusieurs disques c’est mieux)
Le tablespace SYSTEM doit toujours être ONLINE
Conseil :ajouter un tablespace pour les tables + ajouter un tablespace pour les index
Objectif : granularité (sauvegarde, stockage, disponibilité, etc.)
8.1
Tablespaces permanents
SQL> CREATE [BIGFILE|SMALLFILE] TABLESPACE nom DATAFILE specifications;
Spécifications :
SIZE valeur [K|M|G|T]
AUTOEXTEND [ON|OFF]
NEXT valeur [K|M|G|T]
MAXSIZE valeur [K|M|G|T]
COMPRESS|NOCOMPRESS
FLASHBACK
BLOCKSIZE
Taille des blocs :


Taille du fichier
Peut grossir ou non
Pas d’incrémentation
Taille maxi
compression auto ou non
participe ou non aux opérations de FLASHBACK DATABASE
Taille des blocs (plus les blocs sont gros, moins il y a de processus d’écriture)
Sous Windows : 2/4/8/16 Ko
Sous Linux : 2/4/8/16/32 Ko
Creation d’un tablespace :
SQL> CREATE TABLESPACE Toto DATAFILE 'C:\oracle\products\10.2.0\oradata\orcl\toto.dbf' SIZE
100M;
DEHECQ Olivier – http://aide.informatique1.fr
23
SQL> SELECT * FROM V$TABLESPACE;
8.2
Tablespaces non permanents
CREATE TEMPORARY TABLESPACE …
CREATE UNDO TABLESPACE …
créer un tablespace temporaire
créer un tablespace d’annulation
On ne peut faire des recover (restauration à une date/heure antérieure) sur une base de données que
si :


Le mode ArchiveLog est activé
Le tablespace d’annulation a été défini
DEHECQ Olivier – http://aide.informatique1.fr
24
8.3
TP - Configuration et Tablespaces
8.3.1
Modifier et vérifier la prise en compte de paramètres depuis SQLPlus
Créer le PFILE (éditable) correspondant au SPFILE (paramètres) de l’instance en cours :
SQL> CREATE PFILE='c:\initBASE.ora' FROM SPFILE;
Ouvrir le fichier c:\initBASE.ora avec le WordPad
…
*.utl_file_dir=’C:\’
*.shared_servers=10
Pas de retour chariot, sauvegarder
SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP PFILE='c:\initorcl.ora'
Démarre en prenant en compte le PFILE
SQL> SELECT NAME, VALUE FROM V$PARAMETER WHERE NAME=’utl_file_dir’ OR NAME=’SHARED_SERVERS;
Affiche les valeurs des paramètres
SQL> CREATE SPFILE FROM PFILE='c:\initorcl.ora';
Mettre les données du PFILE dans le fichier de configuration par défaut
8.3.2
Modifier les paramètres de connexion depuis Net Manager ou
TNSNames .ora
DEDIE =
(DESCRIPTION = (ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.2.100.6)(PORT = 1521)) )
(CONNECT_DATA =
(SERVER = DEDICATED) (SERVICE_NAME = INSTANCE1) ) )
PARTAGE =
(DESCRIPTION = (ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.2.100.6)(PORT = 1521)) )
(CONNECT_DATA =
(SERVER = SHARED)
(SERVICE_NAME = INSTANCE1) ) )
8.3.3
Les Tablespaces
Avec SQLPlus
SQL> CREATE BIGFILE TABLESPACE TS_SQL DATAFILE
'C:\oracle\product\10.2.0\oradata\orcl\TS_SQL.dbf’ SIZE 10M
Créer un tablespace de taille fixe de 10Mo
SQL> CREATE SMALLFILE TABLESPACE TS_SQL DATAFILE
'C:\oracle\product\10.2.0\oradata\orcl\TS_SQL.dbf’ SIZE 2M AUTOEXTEND
Avec Enterprise Manager (interface web)
Administration > Espace disque logique








Créer
Nom : TS_STORAGE
AJOUTER
Nom du fichier : ts_storage1.dbf, fixe 5Mo
CONTINUER
AJOUTER
Nom : Ts_storage2.dbf, 2Mo extension automatique incrément 2Mo, illimité
OK
Administration > Espace disque logique



Créer
Nom : TS_LOCAL, géré localement
AJOUTER
DEHECQ Olivier – http://aide.informatique1.fr
Page 25


Nom du fichier : ts_local.dbf, fixe 5Mo
OK
La taille des blocs :
SQL> ALTER SYSTEM SET db_16k_cache_size=16384 SCOPE=BOTH;
Ajoute la possibilité de créer des block de 16ko
SQL> CREATE TABLESPACE TS_16K 'C:\oracle\product\10.2.0\oradata\orcl\ts_16k.dbf’ SIZE
5M BLOCKSIZE=16k;
Trouver les Tablespaces correspondants aux Data Files :
SQL> select f.ts#,f.name,t.name from v$datafile f inner join v$tablespace t on
t.ts#=f.ts# where t.name=’USERS’;
…
SQL> alter DATABASE DATAFILE 'C:\oracle\product\10.2.0\oradata\orcl\USERS01.dbf'
AUTOEXTEND ON;
DEHECQ Olivier – http://aide.informatique1.fr
26
9
Flashback
(p327+)
9.1
De données (p328)
On peut faire un flashback des données en modifiant la table de la façon suivante :
SQL> ALTER TABLE matable ENABLE ROW MOVEMENT
On peut ensuite revenir en arrière après un DELETE * FROM matable; puis COMMIT; en faisant
SQL> FLASHBACK TABLE matable TO TIMESTAMP SYSTIMESTAMP – INTERVAL ‘5’ MINUTE;
Il faut que le flashback soit activé sur le tablespace qui contient ‘matable’ (activé par défaut)
9.2
De table (p330)
On peut faire un flashback de tables en modifiant le système de la façon suivante :
SQL> ALTER SYSTEM SET RECYCLEBIN=ON SCOPE=BOTH
On peut ensuite revenir en arrière après un DROP TABLE matable; en faisant
SQL> FLASHBACK TABLE matable TO BEFORE DROP;
Forcer la suppression définitive :
SQL> DROPTABLE matable PURGE;
9.3
De base de données (p333)
N’est possible qu’en mode ARCHIVELOG c’est une restauration
On peut ensuite faire un RECOVER de la base de données en revenant à une date/heure précises
Pour activer le flashback de BDD, à partir d’un shutdown :
SQL> STARTUP NOMOUT
L’instance seulement doit être démarrée
SQL>
SQL>
SQL>
SQL>
ALTER
ALTER
ALTER
ALTER
DATABASE
DATABASE
DATABASE
DATABASE
FLASHBACK ON;
MOUNT;
ARCHIVELOG;
OPEN;
Pour effectuer un flashback :




à une date antérieure
SQL> RECOVER DATABASE UNTIL TIME 01-JAN-2001:04:32:00
Recover complet de la base de données :
SQL> RECOVER DATABASE
Recover de tablespaces particuliers :
SQL> RECOVER TABLESPACE ts_one, ts_two
Recover de datafiles particuliers :
SQL> RECOVER DATAFILE 'data1.db'
DEHECQ Olivier – http://aide.informatique1.fr
Page 27
10
Créer une base de données manuellement
Objectif : créer une base « ENI »
10.1
Créer l’arborescence :
Dans .\oradata\
créer un répertoire « eni »
Dans .\admin\
créer un répertoire « eni » et y placer les mêmes répertoires que dans
.\admin\orcl\ (les répertoires doivent être vidés)
Créer un fichier pfile de la configuration actuelle :
SQL> CREATE PFILE=’c:\oracle\product\10.2.0\db_1\database\initENI.ora’ FROM SPFILE;
10.2
Modifier le fichier initENI.ora :


10.3
Remplacer « orcl » par « eni » (si on a remplacé orcl par eni au niveau des arborescences).
( !) le fichier d’undo doit avoir la même valeur que le paramètre undo_tablespace
Créer le service associé à l’instance (p117)
C:\> SET ORACLE_SID=ENI
C:\> ORADIM -NEW -SID ENI -SRVC ORACLESERVICEENI -SYSPWD PASSWORD -STARTMODE AUTO
-SRVCSTART SYSTEM -PFILE C:\oracle\product\10.2.0\db_1\database\initENI.ora -SHUTMODE IMMEDIATE
Le service OracleServiceENI a été ajouté dans le gestionnaire de service.
Si erreur lors de la création :
Suppression du service base avec:
C:\> oradim -delete -SID ENI
Démarrage de l'instance :
C:\> sqlplus /nolog
SQL> connect / as sysdba
SQL> startup nomount
Si le fichier initENI.ora est dans C:\oracle\product\10.2.0\db_1\database, OU SINON
SQL> startup nomount pfile=C:\ENI\initENI.ora
On peut maintenant se connecter à une instance inactive
C:\> SQLPLUS / as sysdba
Utilise le fait que l’utilisateur soit membre du groupe « Admin Dba »
DEHECQ Olivier – http://aide.informatique1.fr
28
10.4
Créer la base de données
(!) Le fichier initENI.ora doit être présent
SQL> STARTUP NOMOUNT
SQL> create database ENI
user sys identified by <password>
user system identified by <password>
controlfile reuse
maxdatafiles 32
maxinstances 1
character set WE8ISO8859p15
national character set al16utf16
logfile
group 1 'C:\oracle\product\10.2.0\oradata\ENI\log1.log' size 50M,
group 2 'C:\oracle\product\10.2.0\oradata\ENI\log2.log' size 50M,
group 3 'C:\oracle\product\10.2.0\oradata\ENI\log3.log' size 50M
maxlogfiles 8
maxlogmembers 2
noarchivelog
extent management local
datafile 'C:\oracle\product\10.2.0\oradata\ENI\system_01.dbf' size 1 G reuse
autoextend on maxsize 2 G
sysaux datafile 'C:\oracle\product\10.2.0\oradata\ENI\sysaux_01.dbf'
size 256 M reuse autoextend on maxsize 512 M
default tablespace USERS
datafile 'C:\oracle\product\10.2.0\oradata\ENI\users_01.dbf'
size 256 M reuse autoextend on maxsize 1 G
extent management local autoallocate
default temporary tablespace TEMP
tempfile 'C:\oracle\product\10.2.0\oradata\ENI\temp_01.dbf'
size 256 M reuseautoextend on maxsize 1 G
undo tablespace UNDOTBS1
datafile 'C:\oracle\product\10.2.0\oradata\ENI\undotbs_01.dbf'
size 256 M reuse autoextend on maxsize 1 G ;
Les fichiers se créent
10.5
Création des vues du dictionnaire de données
SQL> connect sys/password as sysdba
SQL> @C:\oracle\product\10.2.0\db_1\RDBMS\ADMIN\CATALOG.SQL
C:\oracle\product\10.2.0\db_1\RDBMS\ADMIN\ est l’emplacement des scripts SQL prévus par
Oracle
10.6
Package PL/SQL d'administration
SQL> @C:\oracle\product\10.2.0\db_1\RDBMS\ADMIN\CATPROC.SQL
10.7
Utilisation de SQLPLUS sans message d'erreur
SQL> @C:\oracle\product\10.2.0\db_1\sqlplus\admin\PUPBLD.SQL
10.8
Transformation du PFILE en SPFILE
C:\> set ORACLE_SID=ENI
C:\> sqlplus / as sysdba
SQL> create spfile from pfile='C:\oracle\product\10.2.0\db_1\database\initENI.ora';
Le fichier SPFILEENI.ORA est dans C:\oracle\product\10.2.0\db_1\database, il n’y a donc pas
besoin d’indiquer le chemin du SPFILE dans initENI.ora
DEHECQ Olivier – http://aide.informatique1.fr
29
10.9
Paramétrage du listener
Modifier le fichier listener.ora
SID_LIST_LISTENER2 =
(SID_LIST =
(SID_DESC =
(GLOBAL_NAME = ENI)
(SID_NAME = ENI) ) )
LISTENER2 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.2.100.6)(PORT = 1521))
) )
Vérifier que le service a bien été créé. Si besoin, exécuter la commande :
C:\> lsnrctl stop LISTENER2
C:\> lsnrctl start LISTENER2
Modifier le port dans listener.ora et dans tnsnames.ora
10.10
Paramétrage du client
Modifier le fichier listener.ora :
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.2.100.6)(PORT = 1524))
) )
Modifier le fichier tnsnames.ora :
ENI =
(DESCRIPTION =
(ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.2.100.6)(PORT = 1524)))
(CONNECT_DATA = (SERVICE_NAME = ENI))
)
Arrêter puis relancer le listener avec la commande lsnrctl, et vérifier que le service est bien lancé
(console gestion des services Windows)
Se connecter avec :
C:\> SET ORACLE_SID=ENI
C:\> SQLPLUS system/password@ENI
Connecté
En cas de problème de connexion, essayer avec le mot de passe « oracle »
10.11
Sécurisation du listener
C:\> LSNRCTL LISTENER
C:\> set password
C:\> save config
C:\> exit
DEHECQ Olivier – http://aide.informatique1.fr
30
10.12
Configuration de Database Control (Administration Web)
Database Control = console d'administration web
C:\> set ORACLE_SID=ENI
 En 10G r1:
SQL> emca

En 10G r2 / 11G:
SQL> emca –configdbcontroldb -repos create
10.13
Correction des problèmes :
En cas d'échec de la création du repository :
Connectez-vous en SYS ou SYSTEM sous SQLPLUS
C:\> sqlplus /nolog
SQL> connect / as sysdba;
SQL> drop user sysman cascade;
SQL> drop user mgmt_view cascade;
SQL> drop role mgmt_user;
SQL> drop public synonym mgmt_target_blackouts;
SQL> drop public synonym SETEMVIEWUSERCONTEXT;
SQL> Exit
Puis recréer la base (10.4)
En cas de problème d'inscription mot de passe DBSNMP :
Mettre le mot de passe entre " "
En cas d'erreur provenant d'un manque de ressource :
Vérifier les paramètres suivants dans le Pfile :
*.job_queue_process = 10
*.shared_pool_size > 80M
En cas d'erreur d'instance ou de base non démarrées sur la console web
Arrêter/démarrer le service Windowsdbconsole :
C:\> set oracle_sid=ENI
C:\> emctl start dbconsole
C:\> emctl stop dbconsole
C:\> emctl status dbconsole
Permet d'obtenir le numéro du port de la console web
DEHECQ Olivier – http://aide.informatique1.fr
31
11
Gestion des tables et des index p243
11.1
Vue d’ensemble
Quand on fait un create table, on va dans le tablespace par défaut de l’utilisateur (par défaut, c’est la
tablespace de la base de données).
On peut diviser les données sur plusieurs disques, et donc diviser les tables et les index.
Il est conseillé d’avoir 1 tablespace pour les tables, et 1 tablespace pour les index.
 Les tables et les index sont des segments.
Le stockage du contenu des tables est organisé en extensions (extend).
Il existe 2 types d’index :
-
11.2
Index B-TREE : à 2 colonnes. Une pour les index, dans l’ordre ; l’autre pour la valeur
(« dupond » par exemple)
Index BITMAP : la valeur (« dupont » par exemple), puis à la suite les enregistrements pour
lesquels on retrouve cette valeur
Gestion des tables
PCTFREE et PCTUSED
Il faut donc supprimer ou compresser les données pour pouvoir écrire de nouveau.
DEHECQ Olivier – http://aide.informatique1.fr
32
Quand on créé une table, on peut spécifier le tablespace de destination. (p 247)
CREATE TABLE nom (colonnes)
[TABLESPACE nomTablespace]
[PCTFREE valeur]
[PCTUSED valeur]
[NOCOMPRESS|COMPRESS]
11.2.1
La vue des informations de la table
DESC DBA_TABLES (p259) : valeur du PCTFREE, du tablespace …
TABLE_NAME, OWNER, TABLESPACE_NAME, PCT_FREE, BLOCKS (nombre de bloc sous la HWM), NUM_ROWS
(et page 252 pour analyser les données).
1.1.1
HWM (High Water Mark)
La high water mark
HWM : emplacement du bloc le plus loin qui ai contenu des données. Oracle va chercher des infos du
premier bloc jusqu’au HWM, même lorsque des données ont été supprimées. Du coup le mieux c de
compresser les données pour réajuster le HWM.
11.2.2
Informations sur une table créée
Lancer une analyse de la table voulue
SQL> ANALYZE TABLE table_test COMPUTE STATISTICS;
Récupérer les informations de l’analyse
SQL> SELECT tablespace_name, pct_free, num_rows, blocks from DBA_TABLES where
table_name LIKE 'table_%';
DEHECQ Olivier – http://aide.informatique1.fr
33
11.3
TP4 - Création de tables (avancé)
SQL> CREATE TABLE Table1 (money integer) TABLESPACE TS_SQL PCTFREE 20 PCTUSED 50;
SQL> CREATE TABLE Table2 (dinero integer) TABLESPACE TS_LOCAL PCTFREE 20 PCTUSED 50;
SQL> ALTER TABLE TABLE1 MOVE TABLESPACE TS_LOCAL;
SQL> ALTER TABLE TABLE2 MOVE TABLESPACE TS_SQL;
SQL> analyze table TABLE1 compute statistics;
SQL> SELECT tablespace_name, pct_free, num_rows, blocks from DBA_TABLES where
table_name='TABLE1';
SQL> CREATE TABLE TABLE3 (numero INTEGER, nom CHAR(200), prenom CHAR(200));
Créer un fichier C:\boucle.sql
DECLARE
vN INTEGER;
BEGIN
FOR vN IN 1..1000 LOOP
insert into TABLE3 (numero) values vN;
END LOOP;
END;
/
SQL> @C:\boucle.sql;
SQL> COMMIT;
SQL> SELECT * FROM TABLE3;
SQL> analyze table table3 compute statistics;
SQL> select table_name,num_rows,pct_free,blocks, empty_blocks from dba_tables where
table_name='TABLE3';
SQL> UPDATE TABLE3 set nom='a', prenom='b';
SQL> analyze table table3 compute statistics;
SQL> select table_name,num_rows,pct_free,blocks, empty_blocks from dba_tables where
table_name='TABLE3';
Réduire la fragmentation (p257) :
SQL> ALTER TABLE TABLE3 MOVE;
Réorganise le stockage physique d’une table sans la supprimer
Libérer de l’espace au-dessus de la HWM (p255) :
SQL> ALTER TABLE TABLE3 DEALLOCATE UNUSED;
DEHECQ Olivier – http://aide.informatique1.fr
Page 34
12
La gestion des utilisateurs (217)
SYS peut créer/modifier/supprimer les utilisateurs
SYSTEME a juste un accès en lecture à ces infos, et à toutes les tables.
Le tablespace USERS correspond au fichier USERS.DBF (desc dba_data_files)
Il n’y a pas nécessairement de correspondance entre le nom du tablespace et celui du datafile.
Un nouvel utilisateur, si on ne lui précise pas de tablespace, écrira dans le tablespace par défaut de la
BD : USERS. stocké dans USERS.DBF. Sur USERS, le flashback est activé par défaut.
Le tablespace par défaut de SYSTEM est SYSTEM. On ne peut pas activer le flashback sur le
tablespace SYSTEM !!
12.1
Utilisateurs
Créer :
CREATE USER <login> IDENTIFIED {BY <password>|EXTERNALLY}
[DEFAULT TABLESPACE nom_tablespace]
[QUOTAS] {valeur [K|M]} ON tablespace[,...]
[ACCOUNT {LOCK|UNLOCK}]
[PASSWORD EXPIRE]
l’utilisateur doit changer le mot de passe
[PROFILE nom]
nom du profil
EXTERNALLY : identification Windows seulement





Il faut ajouter des privilèges pour lui autoriser à se connecter : CREATE SESSION
Supprimer un utilisateur : DROP USER <login> [CASCADE]
Se connecter : CONNECT scott/tiger
Modifier : ALTER USER <login> PARAMETRE VALEUR;
Voir les utilisateurs : SELECT * FROM ALL_USERS
C:\> SET ORACLE_SID=ORCL
C:\> SQLPLUS SYS/oracle AS SYSDBA
SQL> CREATE USER Albert IDENTIFIED BY pwd;
SQL> DROP USER Albert
Modifier :
ALTER USER <login> [IDENTIFIED BY <password>|EXTERNALLY] […]
Supprimer :
DROP USER <login> [CASCADE]
12.2
Les vues importantes
ALL_USERS : liste des utilisateurs mais pas beaucoup d’infos
DBA_USERS : pour les DBA, pleins d’infos sur les utilisateurs
DBA_TS_QUOTAS : quotas des utilisateurs
DEHECQ Olivier – http://aide.informatique1.fr
Page 35
12.3
Donner des privilèges (p225)
Introduction :


Privilège système : ordre SQL en général (CREATE, UPDATE, SELECT, INSERT, DELETE …)
Privilège objet : accéder à un objet d’un autre utilisateur
Chaque ordre SQL a généralement un privilège système associé qui porte le même nom que
l’ordre SQL. Par exemple, l’ordre SQL CREATE TABLE possède un privilège système associé
CREATE TABLE (donne le droit de créer une table dans son propre schéma).
Certains privilèges systèmes reprennent le nom de l’ordre SQL avec le mot clé ANY. Dans ce
cas, le privilège système permet d’exécuter l’ordre dans n’importe quel schéma de la base de
données. Par exemple, le privilège système CREATE ANY TABLE donne le droit de créer une table
dans n’importe quel schéma de la base de données.
1. Créer un utilisateur :
SQL> CREATE USER Alfonse IDENTIFIED BY oracle DEFAULT TABLESPACE TS_SQL QUOTA 10M ON
USERS;
2. Lui donner le droit de se connecter :
SQL> GRANT CREATE SESSION TO Alfonse;
Pouvoir donner des privilèges aux autres utilisateurs :
Il faut avoir le privilège SYSDBA pour donner des privilèges aux autres utilisateurs
Accorder des privilèges Système (p226) :
GRANT nom_privilège [,…]
TO { nom_utilisateur|PUBLIC } [,…]
[WITH ADMIN OPTION];
 PUBLIC est un rôle par défaut donné à tous les utilisateurs
 La clause WITH ADMIN OPTION donne au bénéficiaire le droit de transmettre le privilège
système.

GRAND ANY PRIVILEGE
donne tous les droits (DDL+DML) + le droit de les transmettre
Retirer des droits :


REVOKE nom_privilege [,…] FROM {nom_utilisateur|PUBLIC} [,…];
SYSOPER : schéma SYSTEME par défaut. Droits d’administration classique mais ne voit pas
les schémas des autres utilisateurs (DBA_USERS), peut arrêter l’instance et la base.
SYSDBA : peut tout faire
Ce sont aussi des noms de privilèges
12.3.1
Privilèges Objets (p227-228)
Les droits accordables sur les objets des autres schémas sont :
SELECT/INSERT/UPDATE/DELETE/EXECUTE
Utiliser un autre schéma que le sien, et synonymes :
On doit faire schema.objet : SELECT * FROM Albert.Clients : procédure lourde !
Il peut être interessant d’utiliser les synonymes : cli pour Albert.clients (p230)
CREATE PUBLIC SYNONYM Cli FOR Albert.Clients
Permet de faire : SELECT * FROM Cli
DEHECQ Olivier – http://aide.informatique1.fr
36
Attribuer un privilège objet à un utilisateur (p228) :
GRANT {nomPrivilege[(listeColonnes)][,…] | ALL [PRIVILEGES]}
ON [nomSchema.]nomObjet
TO {nomUtilisateur|PUBLIC} [,…]
[WiTH GRANT OPTION];
Révoquer un privilège (p229) :
REVOKE {nomPrivilege[(listeColonnes)][,…] | ALL [PRIVILEGES]}
ON [nomSchema.]nomObjet
FROM {nomUtilisateur|PUBLIC} [,…]
12.4
Connexion
Si on ne veut pas avoir à spécifier l’alias TNS dans la chaîne CONNECT, il faut avoir défini la variable
ORACLE_SID=MABASE


12.5
CONNECT / : utilise la connexion via le système d’exploitation (utilisation
CONNECT login/password : connexion base de données
des groupes)
Les rôles
Créer un rôle (p231) :
CREATE ROLE nom [IDENTIFIED {BY motDePasse|EXTERNALLY}|NOT IDENTIFIED]
Attribuer des privilèges au rôle :
GRANT nomPrivilège [,…] TO nomRole [,…] [WITH ADMIN OPTION]
Affecter des utilisateurs au rôle (p232) :
GRANT nomRole [,…] TO {nomUtilisateur|PUBLIC|nomRole} [,…] [WITH ADMIN OPTION]
12.6
Utilisation des profils (p221)



Utilisé pour limiter les ressources de l’utilisateur : durée de session, ressources allouées,
stratégies de mots de passe
Profil DEFAULT attribué par défaut aux utilisateurs, pas de limites dessus
Pour modifier les profils de tout le monde, on fait des limitations sur DEFAULT
Création d’un profil :
CREATE PROFILE nom LIMIT …
[PASSWORD_VERIFY_FUNCTION nom|NULL|DEFAULT] complexité du mot de passe
Modification d’un profil :
ALTER PROFILE nom LIMIT […];
p224
DROP PROFILE nom [CASCADE];
p225
Suppression d’un profil :
12.7
Résumé
Utilisateur, rôle, objet : Créer, modifier, supprimer : CREATE/ALTER/DROP
Privilège, rôle : Affecter, supprimer :
SQL> GRANT role_privilege [ON objet] TO utilisateur
SQL> REVOKE role_privilege [ON objet] FROM utilisateur
DEHECQ Olivier – http://aide.informatique1.fr
37
12.8
TP 5 –Gestion des utilisateurs
SQL> CONNECT SYS/oracle AS SYSDBA
12.8.1
Créer les comptes utilisateurs
SQL> CREATE USER Marie IDENTIFIED BY password DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE
TEMP ACCOUNT UNLOCK QUOTA UNLIMITED ON USERS;
SQL> CREATE USER Jean IDENTIFIED BY password DEFAULT TABLESPACE USERS TEMPORARYTABLESPACE
TEMP ACCOUNT UNLOCK QUOTA UNLIMITED ON USERS;
SQL> CREATE USER Paul IDENTIFIED BY externe DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE
TEMP ACCOUNT UNLOCK;
SQL> CREATE USER Pierre IDENTIFIED BY password DEFAULT TABLESPACE TS_SQL TEMPORARY
TABLESPACE TEMP ACCOUNT UNLOCKQUOTA UNLIMITED ON TS_SQL QUOTA UNLIMITED ON USERS;
SQL> CREATE USER Karine IDENTIFIED BY password DEFAULT TABLESPACE TS_SQL TEMPORARY
TABLESPACE TEMP ACCOUNT UNLOCK QUOTA UNLIMITED ON TS_SQL;
12.8.2
Donner le droit de connexion
SQL>
SQL>
SQL>
SQL>
12.8.3
GRANT
GRANT
GRANT
GRANT
CREATE
CREATE
CREATE
CREATE
SESSION
SESSION
SESSION
SESSION
TO
TO
TO
TO
Marie;
Jean;
Paul;
Karine;
Donner les privilèges sur le schéma SCOTT
Par les rôles :
SQL> CREATE ROLE roleAR12;
SQL> GRANT CREATE TABLE, CREATE VIEW TO roleAR12;
Permet de connaitre les tables dans le schema scott;
SQL> SPOOL c:\requete1.sql
SQL> SELECT ‘GRANT SELECT ON SCOTT.’, TABLE_NAME, ‘TO roleAR12;’ FROM DBA_TABLES
WHERE OWNER=’SCOTT’;
SQL> SPOOL OF;
Puis modifier le fichier c:\requete1.sql et l’exécuter
SQL> @c:\requete1.sql
12.8.4
Exécuter la fonction de complexité de mots de passe
Modifier la fonction .\db_1\RDMBS\ADMIN\utlpwdmg.sql et la renommer en c:\complexite.sql
Attention, par défaut, le profil DEFAULT est modifié
Attention à la valeur de CREATE OR REPLACE FUNCTION verify_function dans le fichier sql, la modifier
SQL> @c:\complexite
SQL> CREATE PROFILE profil_od LIMIT SESSIONS_PER_USER 2 IDLE_TIME 3
PASSWORD_VERIFY_FUNCTION verify_function;
DEHECQ Olivier – http://aide.informatique1.fr
Page 38
12.8.5
Modifier les profils des utilisateurs
SQL>
SQL>
SQL>
SQL>
SQL>
12.8.6
ALTER
ALTER
ALTER
ALTER
ALTER
USER
USER
USER
USER
USER
Marie PROFILE profil_od;
Jean PROFILE profil_od;
Paul PROFILE profil_od;
Pierre PROFILE profil_od;
Karine PROFILE profil_od;
Accorder le rôle aux utilisateurs
SQL> GRANT roleAR12 TO marie, jean, paul, pierre, karine;
DEHECQ Olivier – http://aide.informatique1.fr
39
13
Index
Liste des vues
nom
description
Reference doc
V$DATABASE
Infos sur la base ouverte
P9, 12, 13
V$SGA
Infos sur la SGA
P12
V$INSTANCE
Infos sur l’instance
P12, 13
V$OPTION
Infos sur les options
P12
V$PARAMETER
Valeurs des paramètres de init.ora
P14, 19
V$TABLESPACE
Infos sur les tablespace
P19
V$DATAFILE
Infos sur les datafile
P19
ALL_USERS
Infos sur les utilisateurs (résumé)
P8, 28
DBA_USERS
Infos sur les utilisateurs (détail)
P28, 29
DBA_TS_QUOTAS
Infos sur les quotas utilisateurs
P28
DBA_TABLES
Infos sur les tables
P26, 27, 31
ALL_TABLES
Infos sur les tables
P7
Reference RI
P359
DEHECQ Olivier – http://aide.informatique1.fr
Page 40