Database Management: Views, Tablespaces, Synonyms, and Sequences

Vues

Architecture ANSI/SPARC

Définit les niveaux d'abstraction pour les systèmes de gestion de bases de données (SGBD) (vers 1975) :
- Niveau Interne (ou Physique) : Traite du stockage des données et des méthodes d'accès.
- Niveau Conceptuel : Aussi connu sous le nom de MCD (Modèle Conceptuel de Données) ou MLD (Modèle Logique de Données) ; définit l'arrangement des informations dans la base de données.
- Niveau Externe : Définit les vues utilisateur.

Indépendance

Indépendance Physique : Séparation entre les détails du stockage physique et l'accès aux données depuis les applications utilisant les données.
- Un SGBD avec indépendance physique permet aux administrateurs de bases de données de modifier l'organisation physique (par exemple, ajouter des index, réorganiser le stockage des fichiers) sans affecter les applications utilisateur.
Indépendance Logique : Capacité de modifier la conception des données (ajouter de nouvelles classes, associations, etc.) sans modifier les programmes utilisateur.
Impact sur les utilisateurs :
- Indépendance Physique : Aucun impact sur les programmes utilisateur lorsque le stockage, l'indexation ou le partitionnement change.
- Indépendance Logique : Aucun impact si correctement gérée avec des vues lorsque le schéma, les relations ou les nouvelles colonnes changent.

Exemple Pratique d'Utilisation des Vues

Scénario : Une application utilise une requête SQL sur une table Clients(nom, email, adresse).
Problème : Si la table est divisée en Clients(id, nom, email) et Adresses(client_id, adresse), l'application pourrait échouer.

Solution : Créer une vue Vue_Clients pour reconstruire la structure originale.

CREATE VIEW Vue_Clients AS
SELECT c.nom, c.email, a.adresse
FROM Clients c
LEFT JOIN Adresses a ON c.id = a.client_id;

Les vues assurent l'indépendance logique, maintenant la compatibilité malgré les changements structurels.

Définition et Caractéristiques des Vues

Une vue est une perspective partielle ou spécifique des données d'une ou plusieurs tables.
C'est une fenêtre dynamique sur la base de données.
La définition est donnée par une instruction SELECT qui indique les données à afficher.
Les utilisateurs peuvent interroger la base de données à travers la vue, interagissant avec les données comme si elles provenaient d'une vraie table.
Seule la définition de la vue est stockée, pas les données elles-mêmes.
C'est une table virtuelle sans fichier physique présentant la vue.
Le contenu d'une vue peut changer au fil du temps si les tables sous-jacentes évoluent.
Le contenu est recalculé chaque fois que la vue est utilisée.

Raisons d'Utiliser les Vues

Effet Macro : Remplacer les requêtes complexes par des requêtes plus simples.
Confidentialité : Restreindre l'accès aux données sensibles en créant des vues qui incluent seulement les colonnes nécessaires.
Contraintes d'Intégrité : Appliquer des contraintes au niveau logique, assurant la cohérence des données à travers plusieurs tables.
Augmenter l'Indépendance Logique : Ajuster les vues pour refléter les changements de structure de la base de données sans impacter les applications.

Création de Vues

La commande CREATE VIEW est utilisée pour créer une vue.

CREATE [OR REPLACE] VIEW view_name (col1, col2...) AS
SELECT ...
WITH { READ ONLY | CHECK OPTION [ CONSTRAINT constraint ] };

La spécification du nom de colonne est optionnelle ; par défaut, les noms des colonnes résultantes de l'instruction SELECT sont utilisés.
Si les colonnes de résultat SELECT sont des expressions sans noms, les noms de colonne doivent être spécifiés pour la vue.
L'instruction SELECT peut contenir toutes les clauses sauf ORDER BY. Les vues ne garantissent pas l'ordre des résultats.
Il est recommandé d'utiliser ORDER BY dans la requête qui utilise la vue, pas dans la définition de la vue elle-même.

Exemples de Vues

Exemple 1 : Restriction de la table Produit
```
CREATE VIEW Vue_Produits AS
SELECT * FROM Produits
WHERE Quantite_Disponible > 100;
```
- Il est préférable de spécifier les noms de colonne au lieu d'utiliser * pour éviter les erreurs lorsque la table Produits est modifiée.
```
SELECT * FROM Vue_Produits;
```

Modification des Données à Travers les Vues

Les opérations DELETE, INSERT et UPDATE peuvent être effectuées à travers les vues sous certaines conditions :
- DELETE : L'instruction SELECT définissant la vue ne doit pas inclure de jointures, GROUP BY, DISTINCT ou des fonctions de groupe.
- UPDATE : En plus des conditions de DELETE, les colonnes modifiées doivent être des colonnes liées (non calculées) de la table sous-jacente.
- INSERT : En plus des conditions de UPDATE, toutes les colonnes NOT NULL de la table sous-jacente doivent être présentes dans la vue.
```
UPDATE Vue_Produits
SET Quantite_Disponible = 200
WHERE ID_Produit = 2;
```
- Cette mise à jour est propagée à la table Produits sous-jacente.
Exemple 2 : Vue Simple
Exemple 3 : Vue avec Opération Numérique
Exemple 4 : Vue en Lecture Seule
- Les vues créées avec WITH READ ONLY peuvent être interrogées mais pas modifiées en utilisant les opérations DML (UPDATE, DELETE, INSERT).
- La vue user_updatable_columns montre quelles colonnes peuvent être mises à jour, insérées ou supprimées.
Exemple 5 : Vue avec Contrainte
- Les vues créées avec WITH CHECK OPTION CONSTRAINT empêchent les mises à jour qui ne respectent pas les conditions de la clause WHERE.

Suppression et Renommage des Vues

Suppression d'une Vue
```
DROP VIEW view_name;
```
- Cela ne supprime pas les données sous-jacentes.
Renommage d'une Vue
```
RENAME old_name TO new_name;
```

Vues Système

ALL_CATALOG : Liste toutes les tables, vues et autres objets dans la base de données auxquels l'utilisateur connecté a accès.
USER_VIEWS : Fournit des informations sur les vues appartenant à l'utilisateur connecté, y compris le nom de la vue et la requête utilisée pour la créer.
ALL_VIEWS : Fournit des informations sur toutes les vues accessibles à l'utilisateur connecté, quel que soit le propriétaire.

Tablespaces

Définition

Oracle stocke les données logiquement dans des tablespaces et physiquement dans des fichiers de données.
Tablespace :
- Appartient à une seule base de données à la fois.
- Composé d'un ou plusieurs fichiers de données.
- Divisé en unités de stockage logiques appelées segments.
Fichier de Données :
- Appartient à un seul tablespace et une seule base de données.
- Agit comme un référentiel central pour les données des objets de schéma (tables, index, vues, procédures stockées, etc.).

Structure

Chaque table a plusieurs segments.
Chaque segment est formé de plusieurs extents.
Un extent est un ensemble de blocs contigus.
Un bloc de données Oracle est la plus petite unité d'E/S logique.
Le paramètre DB_BLOCK_SIZE définit la taille d'un bloc logique.
La taille d'un bloc logique peut être entre 2 Ko et 32 Ko (Valeurs : 2 Ko, 4 Ko, 8 Ko, 16 Ko et 32 Ko).

Vues pour l'information Tablespace

DBA_TABLESPACE : Fournit des informations sur les tablespaces dans la base de données.
DBA_SEGMENTS : Affiche les paramètres de stockage et l'utilisation de l'espace dans les segments.
DBA_EXTENTS : Affiche le nombre de blocs et la taille des extents.

Tablespaces par défaut

Lors de la création de la base de données, plusieurs tablespaces sont créés par défaut :
- SYSTEM : Contient le dictionnaire de données.
- SYSAUX : Utilisé pour les composants Oracle.
- UNDO : Utilisé pour les données d'annulation.
- TEMP : Un tablespace temporaire pour les opérations de tri.
Un tablespace par défaut (généralement nommé USERS) peut être créé pour les objets créés sans spécifier de tablespace.
La vue DATABASE_PROPERTIES fournit des informations sur les tablespaces par défaut de la base de données.

Types de Tablespaces

Tablespace Permanent
Tablespace Temporaire
Tablespace UNDO

Création d'un Tablespace Permanent

Attributs :
- SMALLFILE : Tablespace sur plusieurs fichiers (jusqu'à 1022 fichiers, max 32 Go au total).
- BIGFILE : Tablespace sur un seul grand fichier (max 128 To).
- AUTOEXTEND : Active l'extension automatique des fichiers.
- ONLINE : Tablespace est immédiatement utilisable.
- ENCRYPT : Chiffre les données du tablespace.
- COMPRESS : Compresse le stockage des segments dans le tablespace.
```
CREATE TABLESPACE mon_tablespace
DATAFILE '/chemin/vers/mon_tablespace.dbf'
SIZE 100M
AUTOEXTEND ON
NEXT 10M
MAXSIZE 500M;
```

Création d'un Tablespace Temporaire

Le tablespace temporaire ne peut pas être créé avec une taille de bloc différente de la taille de bloc de la base de données (DB_BLOCK_SIZE).
TABLESPACE GROUP : Crée et assigne un tablespace temporaire à un groupe de tablespaces temporaires.
- Avantages :
  - Permet d'attribuer la base de données par un groupe de tablespaces temporaires, augmentant les performances.
  - Permet à une application d'utiliser plusieurs tablespaces temporaires simultanément pour le tri.
```
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE group_name_or_tablespace_name;
```

Création d'un Tablespace UNDO

Lorsqu'un utilisateur exécute une requête DML, Oracle fait une copie des blocs originaux dans le tablespace UNDO avant de les écraser.
Si l'utilisateur annule la transaction, Oracle récupère les blocs originaux du tablespace UNDO.
Le tablespace UNDO ne peut pas être créé avec une taille de bloc différente de la taille de bloc de la base de données (DB_BLOCK_SIZE).
```
ALTER SYSTEM SET UNDO_TABLESPACE = tablespace_name;
```

Suppression d'un Tablespace

Syntaxe :
```
DROP TABLESPACE tablespace_name
[INCLUDING CONTENTS]
[AND DATAFILES];
```
- INCLUDING CONTENTS : (Optionnel) Supprime le contenu du tablespace, y compris les tables, les index et autres objets. Si non spécifié, le tablespace ne sera pas supprimé s'il contient des objets.
- AND DATAFILES : (Optionnel) Supprime également les fichiers de données associés au tablespace. Si non spécifié, les fichiers de données peuvent rester sur le disque.

Synonyms

Définition

Un synonyme est un alias pour un objet de base de données ou un schéma, agissant comme un raccourci.
L'objet peut être une table, une vue, une séquence, une procédure, une fonction ou un package.
Les synonymes peuvent être publics ou privés.
- Synonyme Public : Accessible depuis tous les schémas utilisateur.
- Synonyme Privé : Accessible seulement depuis le schéma dans lequel il a été créé.
Les administrateurs créent des synonymes pour :
- Masquer les vrais noms et emplacements des objets.
- Simplifier les noms d'objets.
- Éviter de préfixer les requêtes avec le nom du propriétaire.

Création de Synonyms

Syntaxe :
```
CREATE [PUBLIC] SYNONYM synonym_name
FOR [schema.]object_name;
```
- PUBLIC : (Optionnel) Si spécifié, le synonyme est public et accessible à tous les utilisateurs ; sinon, il est privé.
- schema.object_name : L'emplacement et le nom de l'objet ; si le schéma n'est pas spécifié, le synonyme pointe vers un objet dans le schéma de l'utilisateur courant.

Exemples

Synonymes Publics

CREATE PUBLIC SYNONYM SITE FOR INDUSTRIE;
CREATE PUBLIC SYNONYM SOCIETE FOR INDUSTRIE;
CREATE PUBLIC SYNONYM AGENCE FOR INDUSTRIE;

Synonymes Privés

CREATE SYNONYM TAUX FOR SLAIRE;
CREATE SYNONYM PRIMES FOR SALAIRE;

Visualisation des Synonyms

Interrogez la vue DBA_SYNONYMS avec owner <> 'PUBLIC' ou = 'YourUser' pour voir les synonymes existants.
De plus, les vues ALL_SYNONYMS et USER_SYNONYMS fournissent des informations sur les synonymes disponibles.

Suppression des Synonyms

Syntaxe :
```
DROP SYNONYM synonym_name;
```

Sequences

But

Générer automatiquement des nombres séquentiels uniques.
Utile pour :
- Créer des valeurs de clé primaire.
- Coordonner les valeurs de clé dans plusieurs lignes ou tables.

Création de Séquences

Syntaxe :

CREATE SEQUENCE sequence_name
[INCREMENT BY ( 1 | value )]
[START WITH value ]
[ MAXVALUE value | NOMAXVALUE ]
[ MINVALUE value | NOMINVALUE ]
[ CYCLE | NOCYCLE ]
[ CACHE value | NOCACHE ];

Paramètres :
- INCREMENT BY (1 | value) : Le pas d'incrémentation (entier positif ou négatif ; la valeur par défaut est 1).
- START WITH value : Le premier nombre à générer (>= MINVALUE pour ascendant, <= MAXVALUE pour descendant).
- MAXVALUE value | NOMAXVALUE : Valeur limite maximale (ou pas de maximum). $10^{27}-1$ pour ascendant, 1 pour descendant.
- MINVALUE value | NOMINVALUE : Valeur limite minimale (ou pas de minimum). La valeur par défaut est 1 pour ascendant, $10^{27}-1$ pour descendant.
- CYCLE | NOCYCLE :
  - CYCLE : Force la séquence à redémarrer depuis MINVALUE lorsque MAXVALUE est atteint (ascendant) ou depuis MAXVALUE lorsque MINVALUE est atteint (descendant).
  - NOCYCLE : (Par défaut) La séquence s'arrêtera lorsque MAXVALUE ou MINVALUE sera atteint.
- CACHE value | NOCACHE : Pré-génère les prochaines valeurs de séquence (nombre de valeurs stockées dans le cache), améliorant le temps de réponse.

Exemple

sql CREATE SEQUENCE SEQ_DEPT START WITH 10 INCREMENT BY 10 MAXVALUE 2000 NOCYCLE;

Manipulation de Séquences

Pseudo-colonnes : NEXTVAL et CURRVAL
- sequence_name.NEXTVAL : Incrémente la séquence et renvoie une nouvelle valeur.
- sequence_name.CURRVAL : Renvoie la valeur actuelle de la séquence dans la session courante.

Exemples avec SELECT :

Exemple 1 :

SELECT SEQ_DEPT.NEXTVAL FROM SYS.DUAL;
-- NEXTVAL => 10

Exemple 2 :

SELECT SEQ_DEPT.CURRVAL FROM SYS.DUAL;
-- CURRVAL => 10

Exemple 3 :

SELECT SEQ_DEPT.NEXTVAL FROM SYS.DUAL;
-- NEXTVAL => 20

Exemples avec VALUES

INSERT INTO DEPT VALUES(SEQ_DEPT.NEXTVAL , ‘ informatique’, ‘sfax’);

Exemple avec UPDATE

UPDATE EMP SET DEPTNO=SEQ_DEPT.NEXTVAL WHERE DEPTNO=10;

Modification des Séquences

Syntaxe :

ALTER SEQUENCE sequence_name
[INCREMENT BY n]
[ MAXVALUE n| NOMAXVALUE ]
[ MINVALUE n| NOMINVALUE ];

Exemple :

ALTER SEQUENCE SEQ_DEPT
INCREMENTED BY 5;
MAXVALUE 1000;
SELECT SEQ_DEPT.NEXTVAL FROM SYS.DUAL;
-- NEXTVAL => 45

Suppression des Séquences

Syntaxe :
```
DROP SEQUENCE sequence_name;
```
Exemple :
```
DROP SEQUENCE SEQ_DEPT;
```

Recherche de Séquences

Interrogez les vues USER_SEQUENCES ou ALL_SEQUENCES.
Exemple :
```sql
SELECT SQUENCENAME, MINVALUE, MAXVALUE, INCERMENTEDBY
FROM USERSEQUENCES WHERE SEQUENCENAME = ‘