Oracle Database/Tables

Oracle Database

• Introduction
• Interfaces de gestion
• Gestion des utilisateurs
• Objets stockés
  • Tablespaces
  • Tables
  • Vues
  • Vues matérialisées
  • Index
  • Procédures stockées
  • Fonctions
  • Packages
  • Trigger
  • Séquences
• PL/SQL
  • Considérations générales
    • Oracle ignore le type booléen
    • Oracle ignore l'autoincrement
  • Quelques requêtes utiles
    • Dictionnaire de données
    • Paramètres système
    • Utiliser les dates
    • Modifier une ligne
    • Analyse d'une table
  • Utilisation de fonctions
    • Fonction UNPIVOT
    • Fonction PIVOT
    • Fonction LISTAGG
    • Fonction TRIM
    • Fonction LENGTH
    • Fonction SUBSTR
    • Fonction DECODE
• Sécurité
• Net Services
• Serveurs partagés
• Monitoring des performances
• Maintenance proactive
• Gestion des annulations
• Conflits de verrouillage
• Sauvegardes et restaurations
• Bases de données multimédia
• Bases de données spatiotemporelles
• Deferrable constraint
• Débogage
• Désinstaller

Lister les tables

Pour obtenir la liste des tables du tablespace courant :

SELECT owner, table_name FROM all_tables;

Créer des tables

En reprenant l'exemple du livre SQL :

CREATE TABLE client1 (nom VARCHAR(10), prenom VARCHAR(10), adresse VARCHAR(20) );

Table créée.

En passant par un clic droit sur les tables, Nouvelle table..., SQL Developer permet de générer puis exécuter cette démarche :

CREATE TABLE client1
( id INT NOT NULL 
, nom VARCHAR2(50) 
, prenom VARCHAR2(50) 
, adresse VARCHAR2(255) 
, CONSTRAINT client1_PK PRIMARY KEY (ID) ENABLE
) TABLESPACE Wikibooks;

Dans cette commande, on a précisé le tablespace dans lequel rattacher la nouvelle table avec le mot clé TABLESPACE dans la clause de création. Mais SQL Developer le permet en le sélectionnant dans l'interface graphique.

Modifier la structure des tables

Exemple de renommage :

ALTER TABLE client1 RENAME to client2

Ajout d'une contrainte sur les valeurs du premier champ :

ALTER TABLE client1 CHECK id > 1;

Ajout d'une clé primaire :

ALTER TABLE client1 ADD CONSTRAINT client1_pk PRIMARY KEY (id);

Retrait d'une clé primaire :

ALTER TABLE client1 ADD PRIMARY KEY (id) DISABLE;

Ajout d'une clé étrangère :

ALTER TABLE client1
ADD CONSTRAINT fk_client2
  FOREIGN KEY (client2_id)
  REFERENCES client2(id);

Supprimer des tables

DROP TABLE client1;

Insérer des lignes

INSERT INTO client1 (id, nom, prenom, adresse) VALUES (1, 'Croche', 'Sarah', 'Petaouchnoc');

1 ligne inséré [sic].

Plusieurs lignes :

INSERT ALL
 INTO client1 (id, nom, prenom, adresse) VALUES (2, 'Pelle', 'Sarah', 'Clochemerle')
 INTO client1 (id, nom, prenom, adresse) VALUES (3, 'Porte', 'Sarah', 'Cuges-les-Bains')
 SELECT 1 FROM DUAL;

2 lignes inséré [sic].

Table crée

ID	PRENOM	NOM	ADRESSE
1	Sarah	Croche	Petaouchnoc
2	Sarah	Pelle	Clochemerle
3	Sarah	Porte	Cuges-les-Bains

Lire une table

Pour accéder à sa structure :

desc client1;

Nom     NULL     Type          
------- -------- ------------- 
ID      NOT NULL NUMBER(38)    
NOM              VARCHAR2(10)  
PRENOM           VARCHAR2(10)  
ADRESSE          VARCHAR2(20) 

Si la table n'existe pas, l'erreur qui apparait est ORA-00923: FROM keyword not found where expected.

Pour son contenu :

SELECT * from client1;

ID   NOM        PRENOM     ADRESSE
-    ---------- ---------- --------------------
1    Croche      Sarah     Petaouchnoc
2    Pelle       Sarah     Clochemerle

Le nombre de tirets correspond à la taille du champ.

Mettre à jour des lignes

UPDATE client1 SET adresse = 'Cuges-les-Bains' WHERE id = 1;

Supprimer des lignes

DELETE client1 WHERE ID = 2;

Partitionner une table

Le partitionnement Oracle sert à diviser les données d'une table volumineuse dans plusieurs plus petites afin d'en augmenter les performances.

Range

Exemple :

CREATE TABLE t_range 
( t1      VARCHAR2(10) NOT NULL,
  t2      NUMBER       NOT NULL,
  t3      NUMBER  
) 
PARTITION BY RANGE (t2) 
( PARTITION part1 VALUES LESS THAN (1),
  PARTITION part2 VALUES LESS THAN (11),
  PARTITION part3 VALUES LESS THAN (MAXVALUE)
);

Hash

Exemple :

CREATE TABLE t_hash 
( t1      VARCHAR2(10) NOT NULL,
  t2      NUMBER       NOT NULL,
  t3      NUMBER  
PARTITION BY HASH (t2)
PARTITIONS 4
;

List

Exemple :

CREATE TABLE t_list 
( ort     VARCHAR2(30) NOT NULL,
  t2      NUMBER,
  t3      NUMBER  
)
PARTITION BY LIST(ort) 
( PARTITION part_nord VALUES IN ('Hamburg','Berlin'),
  PARTITION part_sued VALUES IN ('Muenchen', 'Nuernberg'),
  PARTITION part_west VALUES IN ('Koeln','Duesseldorf'),
  PARTITION part_ost VALUES IN ('Halle'),
  PARTITION part_def VALUES (DEFAULT)
);

Interval

Exemple :

CREATE TABLE t_interval 
( buchungs_datum  DATE NOT NULL,
  buchungs_text   VARCHAR2(100),
  betrag          NUMBER(10,2)
)  
PARTITION BY RANGE (buchungs_datum)
INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))
( PARTITION p_historie VALUES LESS THAN (TO_DATE('2014.01.01', 'YYYY.MM.DD')),
  PARTITION p_2014_01  VALUES LESS THAN (TO_DATE('2014.02.01', 'YYYY.MM.DD')),
  PARTITION p_2014_02  VALUES LESS THAN (TO_DATE('2014.03.01', 'YYYY.MM.DD'))
);

Schémas

Un schéma est un ensemble de permissions pour des tables ou procédures stockées. Le mot clé AUTHORIZATION permet de donner des droits à un utilisateur :

CREATE SCHEMA AUTHORIZATION root
  CREATE TABLE table1...
  CREATE TABLE table2...
;

Synonymes

Un synonyme est un alias d'un nom de table (ou d'autres objets). Il peut être utilisé pour la sécurité en masquant le nom du propriétaire de l'objet vers lequel il pointe, ou bien pour uniformiser les noms d'objets distants dans les bases de données distribuées.

CREATE SYNONYM table1 FOR client1;
SELECT * from table1;