TP N° 1 : Création d’une base de données avec Oracle XE

et prise en main de SQL Plus et SQL Developer

Première Année ISIC

Ce TP constitue un premier contact avec l’SGBD Oracle pour la création d’une première base de données.

Objectifs :

➢ Installer Oracle XE
➢ Se connecter à Oracle via le client SQLPLUS et le client SQL Developer
➢ Exécuter quelques commandes de base sous SQLPLUS
➢ Créer des utilisateurs et affectation de rôles
➢ Créer des tables et insérer des données

Compte rendu

Le compte rendu doit être rendu sous format PDF par email adressé à [Link]@[Link].

1. Installation du SGBD Oracle et Oracle SQL Developer

Pour ce TP nous utiliserons une version gratuite d’Oracle, appelée Oracle Express elle est disponible
pour le téléchargement à l’adresse suivante :
▪ [Link]
Pour ce TP nous aurons besoin également d’un client à interface pour accéder à la base de données et
exécuter des commandes SQL. Pour se faire, télécharger et installer Oracle SQL Developer :
▪ [Link]
Après avoir accepté la licence il faut passer à choisir la bonne version à télécharger selon le type du
système d’exploitation que vous utilisez.
Eventuellement, vous aurez besoin de créer un compte Oracle pour télécharger ces logiciels.
Etapes d’installation
 Accepter la licence puis cliquer sur « next »

Choisir un mot de passe pour votre base de donnés :

Cliquer sur « Install »

2. Prise en main de SQL plus

Oracle est un SGBD Relationnel muni de nombreuses fonctionnalités (celles classiques des SGBDR, mais
beaucoup de modules supplémentaires), dans cette partie nous aurons besoin d’exécuter quelques
 commandes simples en mode console avec un outil appelé SQL plus c’est une interface permettant de
lancer et exécuter des commandes sur le serveur Oracle.

2.1. Connexion à une base de données avec SQL Plus

Ouvrir l’invite de commande en tant qu’administrateur sous windows :

Faire un clic droit puis « Exécuter en tant qu’administrateur »

Nous allons se connecter avec un compte administrateur, ce compte est : SYS avec un rôle SYSDBA.
Dans l’invite de commande tapez la commande :

sqlplus / as sysdba

Puis vérifier que vous êtes bien connectés :

4

L'accès à SQL Plus, nous connectera à CDB par défaut. Dans la base de données XE, vous effectuerez
normalement toutes les opérations dans le seul PDB pris en charge par XE (nommé : XEPDB1).
Pour passer à PDB, exécuter la commande suivante :

ALTER SESSION SET CONTAINER = XEPDB1;

On sort de SQL avec l’une des commandes :

QUIT
Ou

EXIT.

Et vérifier que vous êtes bien déconnecté :

On se connecte maintenant avec la commande :

sqlplus sys/password@localhost/xepdb1 as sysdba

password est la mot de passe que vous avez saisie lors de l’installation
Vérifier que vous êtes bien connectés :
 Vérifier que vous êtes bien connectés sur XEPDB1 avec la commande:

SHOW CON_NAME;

2.2. Afficher le nom de l’utilisateur

Pour afficher le nom d’utilisateur en cours, tapez la commande :

show user

2.3. Création d’un utilisateur

Créez un utilisateur TP1DAB qui aura les droits nécessaires pour se connecter, créer et gérer les tables

CREATE USER TP1DB IDENTIFIED BY "TP1DB";

Donner les autorisations nécessaires pour cet utilisateur :

GRANT CONNECT, RESOURCE TO TP1DB;

 CONNECT est un rôle prédéfini qui permet la connexion au serveur. RESOURCE est un rôle prédéfini
qui permet de créer et utiliser tous les objets ORACLE : tables, vues, index, procédures, triggers, etc.

2.4. Connexion avec le nouveau compte utilisateur

Déconnectez-vous de l’utilisateur SYS.

Avec la commande, Connectez-vous en tant que TP1DB

sqlplus login/password@localhost/xepdb1

Donc, tapez la commande :

sqlplus TP1DB/TP1DB@localhost/xepdb1

Puis exécuter la commande suivante pour vérifier l’utilisateur connecté :

show user

2.5. Afficher les droits de l’utilisateur

Pour afficher les droits de l’utilisateur connecté, nous pouvons faire une sélection sur la table
user_role_privs avec :

select GRANTED_ROLE from user_role_privs where USERNAME='TP1DB';

 2.6. Changer votre mot de passe
Pour changer votre mot de passe vous pouvez utiliser la commande :

ALTER USER login IDENTIFIED BY nouveau_mdp ;

Déconnectez-vous et essayer de se connecter avec l’ancien mot de passe, et vérifiez que vous avez une
obtenu une erreur indiquant que le mot de passe est incorrect :

2.7. Le spool : sauvegarde dans un fichier les commandes exécutées

La commande spool permet de générer un fichier texte qui contient toutes les commandes exécutées dans
l’invite de commande.

Pour lancer la sauvegarde des commandes :

SPOOL c:\nom_fichier.txt

A partir de là, toutes les commandes seront sauvegardées dans le fichier c:\nom_fichier.txt , jusqu’à ce
que l'utilisateur lance la commande : SPOOL OFF

Testez la commande SPOOL :

Vérifier dans le contenu du fichier généré :

 2.8. Création d’une table et insertion de données
Cette partie vous montra à travers quelques commandes simples, comment créer, sous Oracle, vos
8
premières structures de bases de données, et comment insérer vos premières données.

Syntaxe de création d’une table :

CREATE TABLE [schéma.]nomTable

( colonne1 type1 [DEFAULT valeur1] [NOT NULL]

[, colonne2 type2 [DEFAULT valeur2] [NOT NULL] ]

[CONSTRAINT nomContrainte1 typeContrainte1]…) ;

Exemple :

CREATE TABLE [Link]

(comp CHAR(4),

nrue NUMBER(3),
rue CHAR(20),

ville CHAR(15) DEFAULT 'Paris',

nomComp CHAR(15) NOT NULL);

Créer une table Etudiant :

CREATE TABLE [Link](nom varchar2(16), age NUMBER);

Ou tout simplement avec :

CREATE TABLE ETUDIANT(nom varchar2(16), age NUMBER);

Nous créons ainsi une table, nommée Etudiant et constituée de deux colonnes. Précisons que SQLplus ne
distingue pas majuscules et minuscules, mais il est plus lisible de saisir les mots-clés SQL en majuscules.

Vérifier que le table est bien crée :

 Afficher le contenu de la table ETUDIANT :

SELECT * FROM ETUDIANT ;

Le résultat indique que la table est vide.

2.9. Insérez des données dans la table Etudiant :

Pour insérer des données dans une table on utilise la commande suivante :

INSERT INTO nomTable (colonne1, colonne2…)

VALUES (valeur1, valeur2, …);

Insérer une ligne dans la table ETUDIANT :

INSERT INTO ETUDIANT VALUES ('SAMIRI', 23);

Nous allons obtenir l’erreur ci-dessous :

Vous ne pouvez pas insérer de données car vous avez un quota de 0 sur le tablespace. Pour résoudre ce
problème, exécutez la commande suivante depuis le compte SYS :

GRANT UNLIMITED TABLESPACE TO <username>;

- Se déconnecter et se connecter avec l’utilisateur SYS

- Vérifier que vous êtes bien connecté en tant que SYS
- Donner un quota illimité à l’utilisateur TP1DB
10

Se déconnecter et se connecter avec l’utilisateur TP1DB puis ré-exécuter

INSERT INTO ETUDIANT VALUES ('SAMIRI', 23);

Vérifier que la ligne est bien insérée :

Insérer d’autres étudiants dans la table.

INSERT INTO ETUDIANT VALUES ('KARIMI', 24);

INSERT INTO ETUDIANT VALUES ('ELMAROUANI', 37);
INSERT INTO ETUDIANT VALUES ('CHAMI', 41);

2.10. Afficher la liste de tous les étudiants :

Nous pouvons afficher le contenu d’une table avec la commande SELECT

SELECT * FROM NOM_TABLE ;

Ainsi, pour afficher le contenu de la table ETUDIANT :

 SELECT * FROM ETUDIANT;

3. Prise en main de SQL Developer

11 Laissez l’invite de commande ouvert et ouvrir SQL Developer, puis se connecter à la base de données :

Cliquer sur « Nouveau » :

Cliquer sur « Connexion à la base de données » puis « OK »:

Saisir les informations de connexion :

- Donner un nom à la connexion

- Saisir le login « TP1DB » et le mot de passe
- Pour se connecter à PDB sélectionner le nom du service et saisir « XEPDB1 »
- Laisser les autres champs par défaut
12

Cliquer sur « tables » pour afficher la liste des tables de la base de données :

Cliquer sur l’icône SQL pour exécuter des commandes SQL :

Afficher le contenu de la table ETUDIANT avec une requête SELECT :

 On constate que la table ne contient aucune ligne. Ceci, par ce que nous n’avons pas validé les insérations
effectuées.

Pour valider les insertions ou les modifications, il faut exécuter la commande

COMMIT ;

Revenir à l’invite de commande et valider les insertions :

13

Revenir à SQL Developer et ré-exécuter la commande SELECT, on constate que les données maintenant
sont présents dans la table :

Vous pouvez également visualiser le contenu d’une table via l’onglet « Données » :

Pour afficher la structure des colonnes :

Nous pouvons créer une table avec l’interface d’un assistant :

14

Saisir le nom de la table, les colonnes et leurs types :

Après avoir créé la table, insérer des lignes :

Valider l’insertion (commit) :

 Vérifié que les données sont bien insérées :

15

Revenir à l’invite de commande et afficher le contenu de la table Cours :

Annexe Syntaxe SQL

1- Création d’une table :

CREATE TABLE [schéma.]nomTable

( colonne1 type1 [DEFAULT valeur1] [NOT NULL]
[, colonne2 type2 [DEFAULT valeur2] [NOT NULL] ]
[CONSTRAINT nomContrainte1 typeContrainte1]…) ;

Exemple :

CREATE TABLE [Link]

(comp CHAR(4),
nrue NUMBER(3),
rue CHAR(20),
ville CHAR(15) DEFAULT 'Paris',
nomComp CHAR(15) NOT NULL);

2- Contraintes d’intégrité :

Quatre types de contraintes sont possibles :

CONSTRAINT nomContrainte
• UNIQUE (colonne1 [,colonne2]…)
• PRIMARY KEY (colonne1 [,colonne2]…)
• FOREIGN KEY (colonne1 [,colonne2]…) REFERENCES [schéma.]nomTablePere (colonne1 [,colonne2]…)
[ON DELETE { CASCADE | SET NULL }]
• CHECK (condition)

- La contrainte UNIQUE impose une valeur distincte au niveau de la table (les valeurs nulles font
exception à moins que NOT NULL soit aussi appliquée sur les colonnes).
- La contrainte PRIMARY KEY déclare la clé primaire de la table. Les colonnes clés primaires ne
peuvent être ni nulles ni identiques.
- La contrainte FOREIGN KEY déclare une clé étrangère entre une table enfant (child) et une table
père (parent). La directive ON DELETE dispose de deux options : CASCADE propagera la
 suppression de tous les enregistrements fils rattachés à l’enregistrement père supprimé, SET NULL
positionnera seulement leur clé étrangère à NULL.
- La contrainte CHECK impose un domaine de valeurs ou une condition simple ou complexe entre
colonnes (exemple : CHECK (note BETWEEN 0 AND 20), CHECK (grade='Ingénieur d’Etat' OR
grade='Ingénieur Principal')).
Exemple :

Table Compagnie

CREATE TABLE Compagnie(

comp CHAR(4), nrue NUMBER(3),
rue CHAR(20), ville CHAR(15) DEFAULT 'Paris',
nomComp CHAR(15) NOT NULL,
CONSTRAINT pk_Compagnie PRIMARY KEY(comp));

16 Table Pilote

CREATE TABLE Pilote(

brevet CHAR(6), nom CHAR(15),nbHVol NUMBER(7,2), compa CHAR(4),
CONSTRAINT pk_Pilote PRIMARY KEY(brevet),
CONSTRAINT nn_nom CHECK (nom IS NOT NULL),
CONSTRAINT ck_nbHVol CHECK (nbHVol BETWEEN 0 AND 20000),
CONSTRAINT un_nom UNIQUE (nom),
CONSTRAINT fk_Pil_compa_Comp FOREIGN KEY (compa) REFERENCES Compagnie(comp));

3- Type de données

3.1. Type caractères :

 3.3. Date/Heure

17

3.3. Type numérique

4- Insertions d’enregistrements (INSERT)

INSERT INTO [schéma.] { nomTable | nomVue | requêteSELECT }

[(colonne1, colonne2…)]
VALUES (valeur1 | DEFAULT, valeur2 | DEFAULT…);

Exemple :

Exemples Commentaire
INSERT INTO Compagnie Toutes les valeurs sont renseignées dans
VALUES ('SING', 7, 'Camparols', 'Singapour',
'Singapore AL'); l’ordre de la structure de la table.
INSERT INTO Compagnie DEFAULT explicite.
VALUES ('AC', 124, 'Port Royal', DEFAULT,
'Air France');

INSERT INTO Compagnie NULL explicite

VALUES ('AN1', NULL, 'Hoche', 'Blagnac',
'Air Nul1');

INSERT INTO Compagnie(comp, nrue, rue, nomComp) DEFAULT implicite

VALUES ('AF', 8, 'Champs Elysées', 'Castanet Air');

INSERT INTO Compagnie(comp, rue, ville, nomComp) NULL sur nrue implicite
VALUES ('AN2', 'Foch', 'Blagnac', 'Air Nul2');

CREATE TABLE Pilote L’insertion du pilote initialise la date de

(brevet VARCHAR(6), nom VARCHAR(20), dateNaiss
DATE,
naissance au 5 février 1965 (à zéro heure,
nbHVol NUMBER(7,2), zéro minute et zéro seconde), ainsi que la
 dateEmbauche DATE, compa VARCHAR(4), date d’embauche à la date (heures, minutes,
CONSTRAINT pk_Pilote PRIMARY KEY(brevet));
INSERT INTO Pilote
secondes) du moment par la fonction
VALUES ('PL-10', 'Christian Soutou', '05-02- SYSDATE
1965', 900, SYSDATE,
'AF');

18