Les Tables

Les tables sont des segments physiques. Elles contiennent les données utilisateur. Elles sont composées de colonnes typées. Elles présentent des contraintes d’intégrite (unique, clé primaire, clé étrangère, ‘check’).
Décrites dans le dictionnaire dans USER_TABLES, ALL_TABLES et DBA_TABLES.

exemple

SQL> CREATE TABLE salaires
( no_emp NUMBER , salaire NUMBER)
TABLESPACE USERS
STORAGE (INITIAL 50K NEXT 50K MINEXTENTS 1 MAXEXTENTS 25 PCTINCREASE 0)

SQL> describe salaires
Name Null? Type
——————————- ——– —-
NO_EMP NUMBER
SALAIRE NUMBER

on peut vérifier les caractéristiques de la table dans le dictionnaire par une commande SQl ou avec des outils graphiques comme Enterprise Manager ou SQLDeveloper :

SQL> select TABLE_NAME, TABLESPACE_NAME, INITIAL_EXTENT,
NEXT_EXTENT, BLOCKS from user_tables
where TABLE_NAME=’SALAIRES’

TABLE_NAME TABLESPACE_NAME INITIAL_EXTENT     NEXT_EXTENT     BLOCKS
----------     ---------------        --------------    -----------    ------
SALAIRES    USERS                  53248              53248

Il peut être judicieux comme ici de préciser la clause STORAGE au niveau de chaque table plutôt qu’au niveau du tablespace, les volumes pouvant être très différents d’une table à l’autre….

SQL> CREATE TABLE mini_emp (empno NUMBER CONSTRAINT pk_emp PRIMARY KEY,
ename VARCHAR2(10) CONSTRAINT upper_ename CHECK (ename = UPPER(ename)),
hiredate DATE DEFAULT SYSDATE,
deptno NUMBER(2) CONSTRAINT fk_deptno REFERENCES scott.dept(deptno) )

on voit ici un certain nombre de contraintes d’intégrité qui faciliteront les développements

Les clusters

Les clusters sont des segments spéciaux qui contiennent plusieurs tables fusionnées, suivant en général une colonne de jointure. On peut les voir comme des jointures physiques.

rem : un certain nombre de tables du dictionnaire sont organisées en cluster.
Voir dba_clusters

Les indexs

Ce sont des accélérateurs, externes aux tables. Peuvent être créés / détruits à tout moment. Se remplissent dynamiquement au fur et à mesure des mises à jour de la table indexée. Organisés en B-TREE. Description dans dba_indexes.

On peut créer autant d’index qu’on veut sur une table (mais il ne faut pas en abuser…)

rem : un index n’est efficace que si la colonne indexée est utilisée dans la clause WHERE d’un SELECT !!

SQL> create [unique] index i1 on t1(col1)

rem : un index peut ne pas être unique. Une clé primaire dans une table est représentée sous forme d’index unique par Oracle (qui lui donne alors un nom ‘technique’ commencant par ‘SYS’.

exemple de requete sur le dictionnaire :

sql> select index_name, index_type,
table_name, uniqueness
from user_indexes

INDEX_NAME	INDEX_TYPE	TABLE_NAME	UNIQUENES
SYS_C00178427	NORMAL	T_TEST	UNIQUE
SYS_C00178434	NORMAL	T1	UNIQUE
PK1	NORMAL	EMP	UNIQUE
I_EMP_DEPTNO	NORMAL	EMP

Toutes les informations concernant les tables (description, paramètres de stockage, contraintes, index, …) peuvent être visualisées dans la console :  Onglet ‘Administration’, rubrique ‘Schema’, Table / Visualiser Table

NONUNIQUE

AUcun de ces objet n’est un ’segment’. C’est à dire qu’il ne consomment pas de blocs dans les fichiers de la base. Ils sont simplement définis dans le dictionnaire de données.

Les vues

Une vue est une fenetre sur une table. Elle ne contient pas de données. Stockée dans le DD sous forme de ’select nommé’.
La mise à jour d’une vue est en fait la mise à jour de la table ‘A TRAVERS’ la vue. Il n’y a pas de duplication de données.
Une vue peut porter sur plusieurs tables, éventuellemnts distantes !

Les vues sont décrites dans la vue dba_views du dictionnaire de données.

create view emp_10 as select empno numero, ename nom
where deptno = 10

l’une des colonnes de dba_views est de type LONG. Si on veut visualiser la totalité des informatiions sous SQL*Plus, utiliser la commande ‘SET LONG 2000′ par exemple pour ne pas tronquer l’affichage !

il existe des vues paramétrées qui peuvent rendrent de grands services pour restreindre certains type d’accès

Si on veut restreindre l’accès à certaines périodes horaires par exemple :

CREATE VIEW emp_ouvrable
AS SELECT * FROM EMP
WHERE TO_CHAR(SYSDATE,’HH’) BETWEEN ‘08′ AND ‘17′

en dehors de la période 8H - 17H le predicat est faux et la vue ne renvoie aucune données !

Si on veut restreindre l’accès a certains postes de travail :

CREATE VIEW EMP_RESTREINTE
AS SELECT * FROM EMP
WHERE USERENV(’TERMINAL’) IN (’PC1′, ‘PC3′)

seuls les postes clients dont l’identification réseau est ‘PC1′ ou ‘PC3′ seront autorisés !

Les Synonymes

Un synonyme est un nom logique (un alias) référencant un objet d’un schema.

Un synonyme peut être privé (visible seulement dans le schéma du propriétaire) ou public (visibles par tous).

Syntaxe :

SQL> create synonym nom_syn for [propr.]objet
SQL> create public synonym nom_syn for [propr.]objet

rem : il faut posseder le privilège ‘CREATE PUBLIC SYNONYM’ ou être DBA pour créer ou détruire un synonyme public.

Un synonyme public doit être unique dans la base.

Le fait que le synonyme soit public ne veut pas dire que l’objet pointé est accessible par tout le monde, il faut en plus donner des droits si nécessaire!

SQL> CONNECT SCOTT/TIGER
SQL> GRANT SELECT ON EMP TO DUPONT
SQL> CONNECT DUPONT/JEAN
SQL> CREATE SYNONYM EMP FOR SCOTT.EMP
SQL> SELECT * FROM EMP…

Les liens de base de données (database links)

Les database links sont des référence à des comptes utilisateurs distants.
Ceci permet au sein d’une même session SQL d’accéder à différents objets de bases réparties sur le réseau. On peut par exemple définir un database link compta_bordeaux qui référence le schéma compta qui se trouve sur la base distante localisée sur le serveur de bordeaux. Ensuite on pourra accéder à une table ou vue distante via ce database link.

Une description des database links se trouve dans la vue USER_DB_LINKS du dictionnaire.

SQL> CREATE DATABASE LINK compta_bordeaux CONNECT TO compta IDENTIFIED BY xyz USING ‘la_base_de_bordeaux’ ;
SQL> SELECT * FROM balance@compta_bordeaux ;
– on peut le rendre transparent grace aux synonymes
SQL> CREATE SYNONYM balance FOR balance@compta_bordeaux ;
SQL> SELECT * FROM balance ;

Les séquences

Une séquence est un compteur programmable stocké en mémoire par Oracle et utilisable de manière partagé.
Il est en général utilisé pour fournir des no de clé d’enregistrements.
Pour créer une séquence :

SQL> create sequence nom_seq start with no_debut
increment by saut
maxvalue valeur_max cycle|nocycle cache|nocache

Pour utiliser une séquence :
la valeur courante de la séquence est donnée par nom_seq.currval et la suivante par nom_seq.nextval.

SQL> create sequence seq_no_cli
start with 1000 increment by 100;
SQL> insert into clients (no, nom) values (seq_no_cli.nextval, ‘Martin’);

pour vérifier rapidement la valeur à suivre :

SQL> select seq_no_cli.nexval from dual

Les programmes stockés (procédures, fonctions ou packages) sont des programmes codés en PL/SQL, nommés, et stockés dans un schéma de la base.
Nécessite le privilège ‘Create procedure’ ou ‘create any procedure’ suivant qu’on est dans son propre schéma ou dans un autre schéma.

Il existe des outils comme ‘TOAD’ (de chez Quest Software) ou ‘SQL*developper’ (Oracle) qui simplifient la vie du développeur et du DBA pour la gestion de ces objets.

Ces programmes stockés doivent être valides. Dans certains cas notamment quant les objets référencés (tables, vues,etc) dans les programmes ont été modifiés, les procédures peuvent devenir invalides.
Après résolution du problème, les programmes doivent être recompilés :

SQL> ALTER  PROCEDURE | PACKAGE | FUNCTION COMPILE
Procedures

Exemple de procédure de crédit d’un compte (qui prend en entrée le no du compte et le montant à créditer)

SQL> create or replace procedure bidon
is
begin
null;
end;

SQL> CREATE PROCEDURE credit (no IN NUMBER, montant IN NUMBER)
AS
BEGIN
UPDATE compte SET solde = solde + montant
WHERE no_compte= no;
END;

Pour executer une procédure :

SQL> execute nom_procedure (ses_parametres);
ou
SQL> BEGIN
nom_proc (ses_parametres)
END;
/

Attention !  pour voir le résultat d’une procédure à l’écran, il fuat que la sortie écran soit valide, avant de lancer son exécution (sinon on ne voit rien!!!) :

SQL> SET SERVEROUTPUT ON

Fonctions

Une fonction est une procédure qui retourne une (ou des) valeur(s).

SQL> CREATE FUNCTION lit_solde (no IN NUMBER)
RETURN NUMBER
IS solde NUMBER (11,2);
BEGIN
SELECT balance INTO solde FROM compte
WHERE no_compte = no;
RETURN (solde);
END;

no est un paramètre d’entrée et solde la valeur du solde du compte fournie en sortie

Packages

Un package est un ensemble encapsulé de procédures et ou de fonctions, ainsi que de données dépendant fonctionnellement.

Il est constitué de 2 parties : la specification qui déclare les objets publics du package et le corps (body) qui définit ces objets.
Pour créer ces 2 entités on utilisera respectivement les ordres :
create package et create package body

Triggers

Un trigger est un morceau de code PL/SQL, stocké dans la base, déclenché lors de l’occurrence d’un événement particulier. Il permet notamment de synchroniser des opérations entre plusieurs tables.
La plupart du temps les triggers sont déclenchés par la modification du contenu d’une table.
La liste des événements déclencheurs apparaît ci-après :

Type d’ordre ordre déclencheur : DELETE, INSERT, UPDATE, CREATE, ALTER, DROP, SERVERERROR, LOGON, LOGOFF, STARTUP, SHUTDOWN

exemple
– trigger déclenché lors d’une insertion
– ou d’une modification de la table client
SQL> CREATE OR REPLACE TRIGGER aff_discount BEFORE INSERT OR UPDATE ON clients FOR EACH ROW WHEN (new.no_cli > 0)
DECLARE evol_discount number;
BEGIN evol_discount := :new.discount - :old.discount;
DBMS_OUTPUT.PUT_LINE(’ evolution : ‘ || evol_discount); END;
/ — FOR EACH ROW signale qu’une modification de 4 lignes
– par un seul UPDATE déclenche 4 fois le trigger.
– Si on ne souhaite qu’un seul déclenchement ,
– on omet simplement la clause FOR EACH ROW.

Les  ‘DIRECTORY’  Oracle sont des objets Oracle qui servent d’interfaces entre la base de données et le système de fichiers du système hote.
Ils permettent notamment d’accéder à des fichiers physiques extérieurs à la base (tables externes, E/S en PLSQL, export/import datapump, etc.)..
En d’autres termes c’est un nom logique qui pointe sur un répertoire DU SERVEUR qui héberge la base (et pas du client bien sûr).

Syntaxe :

– pour creer une directory Oracle
SQL> CREATE [ OR REPLACE ] DIRECTORY ma_dir
AS ‘repertoire_du_serveur’;
– pour supprimer une directory
SQL> DROP DIRECTORY ma_dir;

Pour créer et supprimer une DIRECTORY il faut respectivement les droits SYSTEME :
CREATE ANY DIRECTORY ou DROP ANY DIRECTORY.

rem : il n’y a pas de controle de l’existence du répertoire physique ou des droits associés…

SQL> CREATE OR REPLACE DIRECTORY ma_dir AS ‘/n_importe_quoi’;
Directory created.

Informations dans dictionnaire

SQL> SELECT * FROM DBA_DIRECTORIES;

OWNER      DIRECTORY_NAME                 DIRECTORY_PATH
---------- ------------------------------ ------------------------------
SYS        DATA_PUMP_DIR                  /oracle/10GDB/rdbms/log/
SYS        TMP                            /tmp
SYS        ADMIN_DIR                      /oracle/10GDB/md/admin
SYS        DIR_TMP                        /work/oratmp
SYS        MA_DIR                         /n_importe_quoi

 

Droits sur les DIRECTORY

Comme on le voit sur l’écran précédent une DIRECTORY appartient toujours à SYS.
Donc c’est à priori SYS qui pourra gérer les droits des DIRECTORY même si c’est SYSTEM ou un autre user qui les a créées…
Pour pouvoir utiliser une directory, un utilisateur doit posséder des droits de lecture ou d’écriture dessus :

SQL> GRANT READ, WRITE ON DIRECTORY ma_dir TO scott;

Les droits peuvent être vérifiés dans le dictionnaire dans la table DBA_TAB_PRIVS ou ALL_TAB_PRIVS:

SQL>  select grantee from dba_tab_privs
where table_name like ‘%MADIR%’;
GRANTEE
————
MARTIN

 

exemple de création et insertion dans un fichier en PL/SQL…via une DIRECTORY

On utilise le package standard UTL_FILE.
La fonction d’ouverture du fichier (ici en écriture) passe par une DIRECTORY.
C’est le premier paramètre de UTL_FILE.FOPEN…

SQL>
DECLARE
VFIC utl_file.file_type;
BEGIN
VFIC := UTL_FILE.FOPEN(’HDM_DIR’,'test.txt’,'W’);
UTL_FILE.PUTF(VFIC, ‘coucoun’);
utl_file.fclose(VFIC);
END;
/

Au niveau de l’OS l’utilisateur Oracle (propriétaire généralement des process Oracle) doit avoir les droits d’écriture dans le répertoire correspondants à la directory. Dans le cas contraire on récupère une erreur ORA-29283 :

ERROR at line 1:
ORA-29283: invalid file operation
ORA-06512: at “SYS.UTL_FILE”, line 536
ORA-29283: invalid file operation
ORA-06512: at line 4