Notions à savoir pour le laboratoire
Un script SQL est un fichier comportant une ou plusieurs commandes SQL.
Pour exécuter un script, il faut utiliser la commande «start \chemin\»
Exemple
start J:\
Un paramètre :
Il est possible d’ajouter un ou plusieurs paramètres. Ils sont identifiés par &1, &2
Exemple du fichier
SELECT * FROM EMP WHERE name = '&1' AND ID=&2 ;
REM Notez que &1 est entouré d’apostrophe, puisque name est de type VARCHAR2.
Exemple de l'appel :
start C:\enonceSQL\ SCOTT 1321
Pour ajouter des commentaires dans le script, utiliser la commande «REM» Pour ajouter un message affiché à l’écran, écrire : PROMPT
Exemple :
1- Créer le fichier C:\
2- Avec Notepad++ (ou autre éditeur texte), écrire les 2 lignes suivantes et sauvegarder le fichier
PROMPT Nombre d’éléments dans la table/vue &1
SELECT COUNT(*) FROM &1;
3- Dans SQLPLUS, écrire : start C:\ v$session
Cette vue permet de consulter les tablespaces existantes dans la base de données.
Que vous retourne la requête suivante ?
SELECT TABLESPACE_NAME, CONTENTS, EXTENT_MANAGEMENT, ALLOCATION_TYPE, BIGFILE, STATUS FROM DBA_TABLESPACES;
Impression écran ici…
Voir un autre exemple utilisant cette vue
SELECT tablespace_name, contents, status FROM dba_tablespaces;
En consultant la structure de la table (DESCRIBE), quelle colonne permet de savoir si le tablespace est géré localement ou par le dictionnaire de données ?
En étant connecté avec SCOTT, pouvez-vous exécuter la requête de l’exemple précédent ? Pourquoi ?
Par logique et déduction, comment pouvez-vous changer la requête afin qu’elle puisse être exécuté avec Scott ?
Écrire la requête modifiée :
Cette vue possède des informations sur les fichiers de données.
En utilisant DBA_DATA_FILES, écrire l’énoncé SQL permettant d’arriver au résultat précédent. Ce résultat doit être affiché sur une ligne.
Notes :
- Pour raccourcir la longueur d’une colonne lors de l’affichage, vous pouvez utiliser SUBSTR dans la requête.
- Portez attention au alias de colonnes
Vue contenant quelques informations sur les tablespaces
Exemple :
SELECT ts#, name, bigfile FROM v$tablespace;
Vue sur les fichiers des tablespaces.
Exemple :
SELECT ts#, SUBSTR(name, 0, 35), enabled FROM v$datafile;
Permet d’avoir des informations sur l’espace disponible à l’intérieur d’un tablespace.
Quelles sont les colonnes de cette vue ?
_______________________________________________________________________________
_______________________________________________________________________________ _______________________________________________________________________________
Permet de lire certaines propriétés de la base de données.
Certaines propriétés :
- DEFAULT_TBS_TYPE, - DEFAULT_TEMP_TABLESPACE, - DEFAULT_PERMANENT_TABLESPACE.
Exemple de modification de propriétés pour les tablespaces en général : ALTER DATABASE DEFAULT TABLESPACE nomTablespace;
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE nomTablespace;
Le tablespace d’annulation, étant différent, peut être modifié en utilisant ALTER SYSTEM. ALTER SYSTEM SET UNDO_TABLESPACE = nomTablespace SCOPE = BOTH;
Faire un script () qui permet d’afficher le nom et l’espace libre pour chaque tablespace de la base de données.
Le résultat doit être ordonné par grandeur de l’espace libre (ascendant)
Indice : Consulter la table DBA_FREE_SPACE
Exemple de résultat :
Votre énoncé SQL (provenant du script):
Faire un script () permettant de savoir quels sont les objets appartenant à un certain usager (&1).
On doit également y voir le type des objets, et seulement les types TABLE et INDEX doivent être retournés.
De plus, comme certains usagers possèdent énormément d’objets, le script doit pouvoir limiter le nombre de lignes retournées (&2).
Indice : Au dernier cours, nous avons vu les vues statiques préfixées par USER_, ALL_ et DBA_. Par exemple la vue USER_TABLES. Il existe également une vue permettant de retourner tous les objets, pas seulement les tables. Vous pouvez cherchez dans le livre, ou sur Google pour le nom de cette vue.
Indice 2 : Le mot clé de cet énoncé est « objet ».
Votre énoncé SQL (provenant du script):
_______________________________________________________________________________
_______________________________________________________________________________
_______________________________________________________________________________ _______________________________________________________________________________
Vous pouvez utiliser les scripts si cela peut vous aider lors de la conception de vos énoncés SQL.
L’exercice consiste à ajouter deux nouveaux tablespaces permanents gérés localement et disponibles en lecture et écriture.
Création du premier tablespace
• nom : INVENTAIRE
• Gestion des extensions : locale et avec des tailles gérées par Oracle
• nombre de fichiers : 2
• nom des fichiers : «» et «»
• taille initiale des fichiers 2Mo chaque
• le fichier «» doit être de taille fixe
• le fichier «» doit avoir une expansion automatiquement. Chaque expansion devra ajouter 1Mo au fichier. La taille du fichier ne devra pas dépasser 10Mo.
• Les fichiers doivent être dans le répertoire par défaut des fichiers de données de votre base de données.
Écrire l’énoncé SQL :
_______________________________________________________________________________
_______________________________________________________________________________
_______________________________________________________________________________
_______________________________________________________________________________
_______________________________________________________________________________
_______________________________________________________________________________
_______________________________________________________________________________
_______________________________________________________________________________ _______________________________________________________________________________ Quel est l’énoncé permettant de vérifier que le tablespace et ses fichiers sont créés ? La requête doit produire le résultat suivant :
Énoncé SQL :
_______________________________________________________________________
_______________________________________________________________________
_______________________________________________________________________ _______________________________________________________________________
Création du deuxième tablespace
• nom : PRODUCTION
• nombre de fichiers : 1 de type BIGFILE
• nom du fichier : «»
• Taille fixe : 200 meg
• Le fichier doit être dans le répertoire par défaut des fichiers de données.
Écrire l’énoncé SQL :
_______________________________________________________________________________
_______________________________________________________________________________
_______________________________________________________________________________
_______________________________________________________________________________
_______________________________________________________________________________
_______________________________________________________________________________ _______________________________________________________________________________
Faites une vérification via les vues permettant de vous assurer que le tablespace a été créé.
Cet exercice consiste à ajouter un nouveau tablespace temporaire à votre base de données.
Le tablespace devra avoir les caractéristiques suivantes :
• nom : TEMP2
• nombre de fichier : 1
• nom du fichier : «»
• taille fixe: 100 meg
Énoncé SQL :
_______________________________________________________________________________
_______________________________________________________________________________
_______________________________________________________________________________ _______________________________________________________________________________
Modifier les tablespaces par défaut de la base de données.
Écrire les énoncés permettant de mettre le tablespace permanent INVENTAIRE et le tablespace temporaire TEMP2 comme tablespace par défaut de la base de données.
Énoncés SQL :
_______________________________________________________________________________
_______________________________________________________________________________
_______________________________________________________________________________ _______________________________________________________________________________
