Architecture Oracle : Les tables

SheikYerbouti

Architecture Oracle : Les tables

Retour au sommaire Architecture.

- Titre : Architecture Oracle : Les tables

- Auteur : SheikYerbouti

- Parution : 1 septembre 2004

- Mise à jour : 1 septembre 2004

- Licence : Le guide Oracle par Orafrance, Helyos et SheikYerbouti

Sommaire

- Remerciements

- Généralités

- 1. Les tables standard

- 1-1. Création d'une table

- 1-1-1. Création d'une table temporaire

- 1-1-2. Options de traitement parallèle

- 1-1-3. Collecte automatique de statistiques sur les tables

- 1-2. Modification une table

- 1-2-1. Modification des attributs physiques d'une table

- 1-2-2. Déplacement d'une table vers un nouveau segment ou un autre tablespace

- 1-2-3. Allocation manuelle de stockage pour une table

- 1-2-4. Modification de la définition d'une colonne

- 1-2-5. Ajout de nouvelles colonnes

- 1-2-6. Suppression de colonnes (à partir d'Oracle 9i)

- 1-2-7. Redéfinition d'une table en ligne

- 1-3. Suppression de table

- 1-4. Gestion des tables organisées en index

- 1-4-1. Création d'une table organisée en index

- 1-4-2. Utilisation de la compression de clé

- 1-4-3. Modification d'une IOT

- 1-4-4. Recréation d'une IOT

- 1-4-5. Analyse d'une IOT

- 1-4-6. Conversion d'une IOT en table standard (régulière)

- 1-5. Gestion des tables externes

- 1-5-1. Création d'une table externe

- 1-5-2. Modification d'une table externe

- 1-5-3. Suppression d'une table externe

- 1-6. Consultation des informations relatives aux tables

- 2. Les tables objet

- 3. Les tables XML

- 4. Syntaxe de l'instruction CREATE TABLE

- 4-1. Syntaxe 9i, 10g

- 4-2. Exemples de création de tables

- 5. Syntaxe de l'instruction ALTER TABLE

- 5-1. Syntaxe 9i, 10g

- 5-2. Exemples de modification de tables

Remerciements

Chaleureux remerciements à Developpez.com, l'équipe SGBD en général et Pomalaix et Anomaly en particulier.

Généralités

Les tables sont l'unité de stockage fondamentale d'une base de données Oracle.
Une table est composée de lignes elle-mêmes composées de colonnes.

Les types de tables

Oracle supporte trois types de tables :

- Les tables relationnelles (*)

- Les tables objets

- Les tables XML

Chacun de ces types de tables peut être défini de façon permanente ou temporaire.

Les tables relationnelles peuvent avoir une organisation de type tas (heap) ou de type index (index organized).

remarque : depuis la version 9i, un quatrième type de table est supporté, uniquement en lecture (SELECT) : les tables externes, dont seule la définition de structure est stockée dans la base.

(*) au terme relationnel, je préfère celui de "standard", considérant que toute table pouvant être l'objet d'une jointure dans une interrogation est de facto relationnelle, ce qui est le cas des autres types de tables.

Attributs de colonne

Chaque colonne d'une table est définie avec une ou plusieurs des caractéristiques suivantes :

- Un type définissant la nature de la donnée devant y être stockée ainsi qu'une valeur indiquant la taille maximum permise pour cette donnée

- Une contrainte déterminant la validité de la donnée. Certaines contraintes peuvent être définies au niveau de la colonne et d'autres au niveau de la table

- Une valeur par défaut attribuée automatiquement à l'insertion de la nouvelle ligne

Les types de données

Chaque colonne d'une table comme chaque argument d'une procédure doit être définie avec un type. Ce type détermine le domaine des valeurs que la colonne pourra stocker.

Le tableau suivant liste les divers types Oracle

Type

Description

VARCHAR2(taille [BYTE | CHAR ])

Chaîne de caractères de longueur variable dont la longueur maximum est exprimée par le paramètre taille en octets ou en caractères. La taille maximum est de 4000 octets et la taille minimum est de 1 octet ou 1 caractère. Le paramètre taille est obligatoire.BYTE indique que l'élément de base est l'octet et CHAR indique que l'élément de base est le caractère. (rappelons que certains jeux de caractères stockent chaque caractère sur plusieurs octets)

NVARCHAR2(size)

Chaîne de caractères de longueur variable. La valeur maximum du paramètre taille est déterminée par le nombre d'octet utilisé par un caractère du jeu national et ne peut excéder 4000 octets

NUMBER[(p[,s])]

Numérique de longueur totale p dont une partie décimale de s chiffres significatifs

LONG

Donnée de type caractère pouvant stocker jusqu'à 2 gigabytes

DATE

Donnée de type date située dans une plage comprise entre le 1er janvier 4712 av JC et le 31 décembre 9999 ap JC stockant l'année, mois, jour, heures, minutes et secondes

TIMESTAMP( fractional_seconds_precision)

Donnée de type date stockant l'année, le mois, le jour, l'heure, les minutes, les secondes et les fractions de secondes dont le nombre de chiffres est exprimé par le paramètre (fractional_seconds_precision) Ce chiffre peut être compris entre 0 et 9 (par défaut 6)

TIMESTAMP( fractional_seconds_precision) WITH TIME ZONE

Identique au type TIMESTAMP avec une information supplémentaire de fuseau horaire

TIMESTAMP( fractional_seconds_precision) WITH LOCAL TIME ZONE

Identique au précédent avec les particularités suivantes :la date est stockée avec le fuseau horaire de la base. A l'interrogation elle est retournée dans le fuseau horaire de la session utilisateur

INTERVAL YEAR(year_precision) TO MONTH

Période de temps exprimée en années et mois ou le paramètre year_precision indique le nombre de chiffres du champ année compris entre 0 et 9. la valeur défaut est 2

INTERVAL DAY(day_precision) TO SECOND (fractional_seconds_precision)

Période de temps exprimée enjours, heures, minutes et secondes ou :day_precision représente le nombre maximum de chiffres du champ jour, compris entre 0 et 9 (défaut 2)fractionfractional_seconds_precision représente le nombre maximum de chiffres du champ secondes compris entre 0 et 9 (défaut 6)

RAW(size)

Chaîne de caractères au format binaire dont la longueur est indiquée par le paramètre obligatoire size, qui ne peut excéder 2000 octets

LONG RAW

Chaîne de caractères au format binaire pouvant stocker jusqu'à 2 gigaoctets

ROWID

Chaîne de caractères codée en base 64 représentant l'adresse physique unique d'une ligne dans sa table

UROWID[(size)]

Chaîne de caractères codée en base 64 représentant l'adresse physique ou logique d'une ligne dans une table régulière ou organisée en index. le paramètre optionnel size représente la taille de la colonne dont le maximum autorisé est 4000 octets

CHAR[(size [BYTE | CHAR])]

Chaîne de caractères de longueur fixe déterminée par le paramètre size et dont le minimum est de 1 octet et le maximum de 2000 octets. Cette chaîne de caractères est complétée par des espaces

NCHAR(size)

Chaîne de caractères de longueur fixe déterminée par le paramètre size et dont le minimum est de 1 octet et le maximum de 2000 octets. Cette chaîne de caractères est complétée par des espaces et stocke des caractères du jeu national

CLOB

Chaîne composée de caractères mono-octet ou multi-octets dont la capacité maximum de stockage est de 4 gigaoctets (9i) et 8 teraoctets (10g)

NCLOB

Chaîne composée de caractères au format Unicode mono-octet ou multi-octets dont la capacité maximum de stockage est de 4 gigaoctets (9i) et 8 teraoctets (10g)

BLOB

Chaîne d'éléments au format binaire dont la capacité de stockage maximum est de 4 gigaoctets (9i) et 8 teraoctets (10g)

BFILE

Pointeur vers un fichier stocké en dehors de la base

XMLType

Colonne contenant des données au format XML

UriType

Colonne contenant une URL

Contrainte de colonne NOT NULL

Cette contrainte permet de s'assurer que la colonne contiendra toujours une valeur autre que NULL pendant l'insertion ou la mise à jour d'une ligne de la table. Contrairement aux autres contraintes qui peuvent être définies au niveau table (out of line constraint), la contrainte NOT NULL ne peut être définie qu'au niveau colonne (in line constraint).

Une contrainte PRIMARY KEY ajoute automatiquement la contrainte NOT NULL sur la ou les colonnes inclues dans la clé primaire.

Valeur par défaut

La définition d'une valeur par défaut sur une colonne permet d'assigner automatiquement cette valeur lors d'une insertion lorsque aucune valeur n'est indiquée pour cette colonne dans l'ordre d'insertion.

Les contraintes de niveau table

A l'exception de la contrainte NOT NULL, les autres contraintes peuvent être définies au niveau table (c'est à dire après la définition des colonnes) dont voici la liste :

Primary Key
Cette contrainte définie sur une ou plusieurs colonnes permet de s'assurer que le contenu de cette ou ces colonnes est unique et non NULL dans toute la table. En d'autres termes, cette contrainte garantit l'absence de doublon concernant les données stockées dans cette ou ces colonnes et permet de définir un identifiant unique pour la ligne.

Unique Key
Cette contrainte définie sur une ou plusieurs colonnes garantit l'unicité des informations stockées dans cette ou ces colonnes.

Check
Cette contrainte définie sur une ou plusieurs colonnes permet de valider la donnée que l'on veut stocker en vérifiant qu'elle satisfait une condition.

Foreign
Cette contrainte permet de s'assurer que l'enregistrement dans une table "enfants" ne peut se faire que si les clés existent dans la table "parents".

Ces contraintes peuvent être créées et dans la plupart des cas modifiées, activées (enable) ou désactivées (disable), déclenchées dès l'exécution de l'instruction (immediate) ou à la fin de la transaction (deferred).

Attributs de stockage

Lorsque vous demandez la création d'une table dans un tablespace géré localement (depuis Oracle 9i), Oracle utilise ses propres algorithmes de stockage.
Dans le cas où cette création s'effectue dans un tablespace géré par le dictionnaire (toutes versions Oracle), vous pouvez définir vos propres paramètres de stockage.

Attention : Ne créez jamais de table dans les schémas SYS et SYSTEM

Localisation d'une table

Avec les privilèges et les quotas suffisants, vous pouvez créer une table dans n'importe quel tablespace en ligne (ONLINE) avec la clause TABLESPACE de l'instruction CREATE TABLE. Si cette clause n'est pas spécifiée, la table sera créée dans le tablespace par défaut (attribué à la création de votre compte (CREATE USER) ou au pire, défini dans les paramètres d'initialisation de la base).

Dans le cas d'une création de table définie à l'aide d'une sous-requête (CREATE TABLE ? AS SELECT), vous pouvez utiliser l'option de parallélisme permettant à plusieurs processus de participer à la création ainsi que l'option NOLOGGING afin de limiter les entrées dans les redo log files.

Restrictions liées à la création d'une table :

- Les tables contenant des objets ne peuvent pas être importées dans une base antérieure à la version 8.

- Vous ne pouvez pas déplacer les types et les extents dans un schéma différent du schéma initial si les données liées existent toujours dans la base.

- Vous ne pouvez pas fusionner une table exportée dans une table existante qui porte le même nom dans un schéma différent.

- Vous ne pouvez pas dépasser le nombre maximum de colonnes (1000) dans une table ainsi que le nombre maximum d'attributs dans un type objet.

1 - Les tables standard

1-1 - Création d'une table

Pour pouvoir créer une table dans votre schéma vous devez avoir le privilège système CREATE TABLE. Pour pouvoir créer une table dans un autre schéma vous devez avoir le privilège système CREATE ANY TABLE. De plus vous devez avoir un quota défini dans le tablespace où vous voulez stocker la table ou bien le privilège système UNLIMITED TABLESPACE.

Voici un exemple d'instruction de création d'une table :

exemple de création de table

CREATE TABLE    hr.emp
(
empno    NUMBER(5)        PRIMARY KEY,
ename    VARCHAR2(15)    NOT NULL,
job    VARCHAR2(10),
mgr    NUMBER(5),
hiredate    DATE        DEFAULT sysdate,
sal    NUMBER(7,2),
com    NUMBER(7,2),
deptno    NUMBER(3)        NOT NULL
CONSTRAINT dept_fk REFERENCES hr.departments
(department_id)
)
TABLESPACE    tbs1
STORAGE
(
INITIAL        50K
NEXT        50K
MAXEXTENTS    10
PCTINCREASE    25
) ;

La table emp est créée dans le schéma hr.
La colonne empno est définie comme étant la clé primaire.
La colonne ename est définie comme obligatoire.
La colonne hiredate reçoit par défaut la date du jour.
La colonne deptno est obligatoire et le département doit exister dans la table departments.

La table est créée dans le tablespace tbs1 avec ses informations de stockage soit :
un extent initial de 50 Ko, les extents ultérieurs de 50 Ko également, un maximum de 10 extents possible et un taux d'accroissement de 25% pour chaque nouvel extent créé.

1-1-1 - Création d'une table temporaire

Il est possible de créer une table temporaire. l'aspect temporaire ne réside pas dans la structure même de la table puisque celle-ci demeure tant qu'elle n'est pas détruite (DROP TABLE), mais dans le temps durant lequel les informations persistent dans la table.
Une table temporaire est visible par toutes les sessions mais les données insérées ne sont visibles que par la session qui génère les ordres d'insertion. La persistance des données est relative à la clause définie au moment de la création selon 2 possibilités :

- les données sont supprimées après chaque ordre COMMIT (ON COMMIT DELETE ROWS)

- les données sont supprimées à la fermeture de la session (ON COMMIT PRESERVE ROWS)

Voici un exemple de création d'une table temporaire :

création d'une table temporaire

CREATE GLOBAL TEMPORARY TABLE tempo
(
msg    VARCHAR2(256),
deb    DATE,
fin    DATE
) ON COMMIT PRESERVE ROWS ;

Il est possible de créer des indexes sur une table temporaire dont la persistance de contenu est identique à celle des données de la table.

1-1-2 - Options de traitement parallèle

Lors d'une création de table basée sur une sous-requête ( CREATE ... AS SELECT), les deux parties de l'ordre CREATE et SELECT peuvent être traités en parallèle.

La partie CREATE peut être parallélisée si l'une des conditions suivante est vérifiée :

- la clause PARALLEL est incluse dans l'ordre CREATE TABLE

- vous avez préalablement spécifié l'instruction ALTER SESSION FORCE PARALLEL DLL

La partie SELECT peut être parallélisée si toutes les conditions suivantes sont vérifiées :

- l'ordre SQL contient un indicateur (hint) PARALLEL ou PARALLEL_INDEX ou la partie CREATE contient une clause PARALLEL ou la (les) table(s) de référence ont été déclarées avec la clause PARALLEL

- au moins une table spécifiée dans la partie SELECT requiert soit un full table scan, soit un index range scan distribué sur plusieurs partitions

Si vous parallélisez la création d'une table, tous les ordres de manipulation suivants seront également parallélisés si cela est possible.

exemple de création parallélisée d'une table

CREATE TABLE hr.emp2
PARALLEL
AS SELECT * FROM hr.emp ;

1-1-3 - Collecte automatique de statistiques sur les tables

Le package PL/SQL DBMS_STATS permet de collecter, modifier, consulter, exporter et supprimer les statistiques relatives à vos tables.
Il est possible d'activer ce package dès la création (CREATE) ou la modification (ALTER) d'une table avec la clause MONITORING.
Utilisez la clause NOMONITORING de l'ordre ALTER TABLE pour désactiver cette fonction.

1-2 - Modification une table

Vous pouvez modifier la structure d'une table à l'aide de l'instruction ALTER TABLE.
Pour cela, la table doit être contenue dans votre schéma ou vous devez posséder le privilège objet ALTER TABLE sur cette table ou le privilège système ALTER ANY TABLE.

L'ordre ALTER TABLE permet les modifications suivantes :

- modification des caractéristiques physiques (PCTFREE, PCTUSED, INITRANS, MAXTRANS ainsi que les paramètres de stockage)

- déplacement de la table vers un autre extent ou un autre tablespace

- allocation explicite d'un nouvel extent ou désallocation de l'espace inutilisé

- ajouter, supprimer(9i), renommer(9i) une ou plusieurs colonnes

- modifier certaines caractéristiques d'une colonne (type, longueur, valeur par défaut ou contrainte NOT NULL)

- modifier les attributs de logging de la table

- modifier les attributs CACHE/NOCACHE de la table

- ajouter, modifier ou supprimer les contraintes d'intégrités définies sur la table

- activer ou désactiver les contraintes d'intégrités

- modifier le degré de parallélisme de la table

- activer ou désactiver la collecte des statistiques

- renommer la table

- ajouter ou modifier des caractéristiques des tables organisées en index

- modifier les caractéristiques d'une table externe

- ajouter ou modifier des colonnes de type LOB

- ajouter ou modifier des types objets, tables imbriquées ou varrays

Remarque : Si une vue, une vue matérialisée, un trigger, un index de domaine, un index de fonction, une contrainte de contrôle (CHECK) une fonction, procédure ou package est lié à la table que vous modifiez, ces objets risquent d'être invalidés par l'opération.

1-2-1 - Modification des attributs physiques d'une table

Lorsque vous modifiez les attributs PCTFREE et PCTUSED, les nouveaux réglages s'appliquent à tous les blocs utilisés par la table, y compris ceux déjà alloués.

Lorsque vous modifiez les paramètres de transaction INITRANS et MAXTRANS, les nouveaux réglages d' INITRANS s'appliquent uniquement aux nouveaux blocs alloués alors que ceux relatifs à MAXTRANS s'appliquent à tous les blocs.

Les paramètres INITIAL et MINEXTENTS ne peuvent pas être modifiés.
Tous les autres paramètres de stockage tels que NEXT ou PCTINCREASE n'affectent que les nouveaux extents. La taille d'un nouvel extent alloué dépend des valeurs courantes de NEXT et PCTINCREASE.

Modification des attributs physiques

ALTER TABLE employees
PCTFREE 30
PCTUSED 60;

1-2-2 - Déplacement d'une table vers un nouveau segment ou un autre tablespace

L'instruction ALTER TABLE ... MOVE permet le déplacement des données d'une table non partitionnée vers un nouveau segment ou un autre tablespace sur lequel vous possédez les privilèges suffisants. Cette instruction permet également de modifier tous les paramètres de stockage, y compris ceux qui ne sont pas modifiable par l'instruction ALTER TABLE.

L'exemple suivant déplace la table emp2 vers un nouveau segment avec de nouvelles spécifications de stockage

exemple de déplacement d'une table

ALTER TABLE hr.emp2 MOVE
STORAGE (    INITIAL        20K
NEXT        40K
MINEXTENTS    2
MAXEXTENTS    20
PCTINCREASE    0
) ;

Si la table contient des colonnes LOB, cette instruction peut être utilisée pour déplacer la table ainsi que ses colonnes LOB et leurs segments d'indexes associés.
(sans indication explicite, les colonnes LOB ne sont pas déplacées).

1-2-3 - Allocation manuelle de stockage pour une table

Oracle alloue dynamiquement de nouveaux extents pour le segment de données d'une table selon le besoin.
Il est tout de même possible d'allouer un nouvel extent avec l'instruction ALTER TABLE ... ALLOCATE EXTENT.

Il est également possible de désallouer l'espace inutilisé avec la clause DEALLOCATE UNUSED de l'instruction ALTER TABLE.

1-2-4 - Modification de la définition d'une colonne

L'instruction ALTER TABLE ... MODIFY permet de modifier la définition d'une colonne pré-existante, son type, sa taille, sa valeur par défaut ou l'une de ses contraintes.

Il est possible d'augmenter la taille d'une colonne et même de la diminuer à la condition que la nouvelle taille reste suffisante pour conserver intactes les données pré-existantes. Il est également possible de modifier le type d'une colonne sous réserve de la même condition.
Dans le cas d'une augmentation de taille d'une colonne de type CHAR, il convient de rappeler que cette opération risque de consommer du temps et de l'espace non négligeable, notament sur les tables à forte volumétrie, par l'ajout de tous les espaces nécessaire en fin de chaîne.

1-2-5 - Ajout de nouvelles colonnes

Pour ajouter une ou plusieurs colonnes à une table, utiliser l'instruction ALTER TABLE ... ADD :

exemples d'ajout de colonne

ALTER TABLE hr.emp2 ADD
(
bonus        NUMBER(7,2)
) ;

ALTER TABLE hr.emp2 ADD
(
bonus        NUMBER(7,2),
cp        NUMBER(7,2)
) ;

Remarque : Le nom d'une colonne doit être unique au sein d'une même table.
Comme dit précédemment, la modification d'une colonne peut invalider les objets qui en dépendent.
Dans le cas d'un renommage de colonne, Oracle met à jour automatiquement le dictionnaire de données pour conserver la validité des indexes de fonction ainsi que les contraintes de contrôle.

1-2-6 - Suppression de colonnes (à partir d'Oracle 9i)

Pour supprimer une colonne d'une table, utiliser l'instruction ALTER TABLE ... DROP COLUMN :

suppression d'une colonne

ALTER TABLE hr.emp2 DROP COLUMN bonus ;

Pour supprimer plusieurs colonnes d'une table, utiliser l'instruction ALTER TABLE ... DROP (...) :

suppression de plusieurs colonnes

ALTER TABLE hr.emp2 DROP (bonus, cp) ;

A l'issue de ces commandes, les descriptions de colonnes ainsi que les données contenues sont supprimées de la table.

Marquage des colonnes inutilisées

La suppression immédiate de colonne(s) avec la clause DROP peut, sur une table de forte volumétrie, engendrer un surcoût de temps à l'exécution.

Dans ce cas, la suppression peut être effectuée en plusieurs étapes.
Les colonnes à suprimer sont d'abord marquées avec l'instruction ALTER TABLE ... SET UNUSED.
Les colonnes ainsi marquées ne sont plus affichées, n'apparaissent plus dans les vues du dictionnaire et leur nom n'est plus consigné (il est donc possible d'ajouter à la table une nouvelle colonne portant ce nom). Toutefois, les données de ces colonnes sont toujours présentes dans les blocs et l'espace correspondant n'est pas encore libéré.

marquage de colonnes inutilisées

ALTER TABLE hr.emp2 SET UNUSED (bonus, cp) ;

Plus tard, lorsque l'activité de la base de données est réduite, la suppression physique des données est réalisée avec l'instruction ALTER TABLE ... DROP UNUSED COLUMNS.

Les vues du dictionnaire USER_UNUSED_COL_TABS, ALL_UNUSED_COL_TABS et DBA_UNUSED_COL_TABS peuvent être consultées (dans un traitement batch par exemple) pour connaitre la liste des tables contenant des colonnes inutilisées.

SELECT * FROM DBA_UNUSED_COL_TABS ;

OWNER                          TABLE_NAME                             COUNT
------------------------------ -------------------------------------- -----
HR                             EMP2                                       2

Ces vues indiquent le nombre de colonnes marquées pour suppression, mais pas leur nom.

L'instruction ALTER TABLE ... DROP UNUSED COLUMNS supprime physiquement les données du block et libère l'espace occupé. cette opération peut être lourde pour les segments de undo, c'est pourquoi la clause CHECKPOINT peut être ajoutée pour forcer un checkpoint après un nombre de lignes précisé.

suppression physique des colonnes inutilisées

ALTER TABLE hr.emp2 DROP UNUSED COLUMNS CHECKPOINT 300 ;

1-2-7 - Redéfinition d'une table en ligne

Dans un système de haute disponibilité, il peut être nécessaire de redéfinir une table sans la rendre indisponible (en tout cas le moins longtemps possible).

Lorsqu'une table est redéfinie en ligne, elle reste accessible aux instructions du DML pendant presque toute la durée du processus. Elle ne reste verrouillée qu'un court laps de temps (dépendant de la volumétrie de la table et de la complexité de la redéfinition).

La redéfinition d'une table en ligne offre les possibilités suivantes :

- modifier les paramètres de stockage

- déplacer la table vers un autre tablespace mais dans le même schéma

- ajouter le support du mode parallèle

- ajouter ou supprimer le partitionnement

- re-créer la table pour réduire la fragmentation

- changer l'organisation "normale" d'une table en organisation index

- ajouter et/ou supprimer une ou plusieurs colonnes

Le package PL/SQL DBMS_REDEFINITION

Le privilège d'exécution sur ce package est attribué au rôle EXECUTE_CATALOG_ROLE. De plus les privilèges suivants sont nécessaires :

- CREATE ANY TABLE

- ALTER ANY TABLE

- DROP ANY TABLE

- LOCK ANY TABLE

- SELECT ANY TABLE

Il est clair que ces types de privilèges sont habituellement accordés aux utilisateurs ayant un rôle DBA.

Etapes de redéfinition d'une table en ligne

1. Choisir d'abord l'une des deux méthodes disponibles :

- basée sur la clé primaire de la table (méthode par défaut et conseillée). Avec cette méthode, les définitions source et cible de la table doivent conserver la même clé primaire

- basée sur le ROWID. Avec cette méthode, la table ne doit pas être organisée en index. De plus, une colonne cachée (M_ROW$$) est automatiquement ajoutée à la table redéfinie et doit être marquée comme inutilisée (UNUSED) puis supprimée (DROP UNUSED COLUMNS) après la fin de la procédure de redéfinition

2. Vérifier que la table peut effectivement être redéfinie en ligne à l'aide de la procédure DBMS_REDEFINITION.CAN_REDEF_TABLE() en spécifiant la méthode de redéfinition.

3. Créer une table intérimaire vide (dans le même schéma que la table a redéfinir) contenant les nouveaux attributs ( permettant de ne pas inclure certaines colonnes de la table source et d'ajouter de nouvelles colonnes).

Il est possible de paralléliser la redéfinition de la table en spécifiant un degré de parallélisme sur les deux tables (source et interimaire) et en s'assurant que l'exécution parallèle est activé pour la session.

4. Démarrer le processus de redéfinition par l'appel de la procédure DBMS_REDEFINITION.START_REDEF_TABLE() avec les arguments suivants :

- le nom de la table a redéfinir

- le nom de la table d'interim

- la correspondance des colonnes (entre les définitions source et cible)

- la méthode de redéfinition

Si la liste des correspondances de colonnes n'est pas fournie, Le package inclut toutes les colonnes sources. Si la méthode de redéfinition n'est pas indiquée, alors la méthode basée sur la clé primaire est utilisée.

5. Créer tous les objets dépendants sur la table intérimaire (triggers, indexes, privilèges et contraintes). Toutes les contraintes d'intégrité référentielle doivent être créées avec le statut désactivé (DISABLED). Tant que le processus de redéfinition n'est pas achevé, les triggers sur la table intérimaire ne sont pas exécutés.

Lorsque la redéfinition est entièrement achevée, les triggers, les contraintes et les droits créés sur la table d'intérim sont transférés sur la table redéfinie. Les contraintes d'intégrité référentielle créées avec le statut DISABLED sur la table d'intérim sont également transférées et automatiquement basculées avec un statut ENABLED.

6. Exécuter la procédure DBMS_REDEFINITION.FINISH_REDEF_TABLE() pour achever le processus de redéfinition.

Durant l'exécution de cette procédure, la table d'origine est verrouillée en mode exclusif pendant un court instant.

7. Si la méthode par ROWID a été utilisée, pensez à rendre inutilisée la colonne M_ROW$$ :

ALTER TABLE nom_table SET UNUSED (M_ROW$$)

Synchronisation intermédiaire

Pendant le processus de redéfinition (après START_REDEF_TABLE() et avant FINISH_REDEF_TABLE()), il est possible qu'un grand nombre d'ordres DML aient été exécutés sur la table origine. Il est recommandé de lancer périodiquement la synchronisation entre la table source et la table intérimaire par appel de la procédure DBMS_REDEFINITION.SYNC_INTERIM_TABLE(), permettant ainsi de réduire le temps nécessaire à l'exécution de la procédure FINISH_REDEF_TABLE().

Arrêt volontaire ou involontaire du processus et procédure de nettoyage

Dans le cas d'arrêt demandé ou causé par une erreur, utiliser la procédure DBMS_REDEFINITION.ABORT_REDEF_TABLE() pour nettoyer l'environement mis en place par le processus de redéfinition, et vous pouvez alors supprimer la table intérimaire ainsi que ses objets associés.

Restrictions relatives à la redéfinition en ligne :

- Si la méthode de redéfinition est basée sur la clé primaire, les tables source et cible doivent garder les mêmes colonnes composant la clé primaire. Si la méthode est basée sur le ROWID, la table ne peut pas être organisée en index

- Les tables supportant des vues matérialisées ou des logs de vues matérialisées ne peuvent pas être redéfinies en ligne

- Les vues matérialisées et les tables de type Advanced Queuing ne peuvent pas être redéfinies en ligne

- La partie overflow d'une table organisée en index ne peut pas être redéfinie en ligne

- Les tables supportant des types utilisateurs (objets, REF, collections et tables imbriquées) ne peuvent pas être redéfinies en ligne

- Les tables contenant des colonnes BFILE ne peuvent pas être redéfinies en ligne

- Les tables contenant des colonnes LONG ne peuvent pas être redéfinies en ligne

- La table devant être redéfinie ne doit pas appartenir à un cluster

- Les tables des schémas SYS et SYSTEM ne peuvent pas être redéfinies en ligne

- Les tables temporaires ne peuvent pas être redéfinies en ligne

- Seules, les expressions simples peuvent être utilisées pour déterminer la correspondance des colonnes (les sous-requêtes sont interdites)

- Si de nouvelles colonnes sont ajoutées à la table cible, elles ne peuvent pas être déclarées NOT NULL avant la fin du processus de redéfinition

- Il ne doit exister aucune contrainte d'intégrité référentielle entre la table source et la table intérimaire

- La redéfinition en ligne ne peut être éffectuée en mode NOLOGGING

1-3 - Suppression de table

Pour pouvoir supprimer une table, celle-ci doit se trouver dans votre schéma ou vous devez posséder le privilège système DROP ANY TABLE.

Utilisez l'instruction DROP TABLE.

Exemple de suppression de la table emp2 du schéma hr :

DROP TABLE hr.emp2 ;

Si vous voulez supprimer une table supportant des contraintes d'intégrité référentielle, ajoutez la clause CASCADE CONSTRAINTS à l'instruction DROP TABLE :

DROP TABLE hr.emp2 CASCADE CONSTRAINTS ;

Remarque : La supression d'une table retire sa définition du dictionnaire de données
Tous les indexes et triggers associés sont également supprimés
Toutes les vues et procédures PL/SQL dépendant de la table supprimée deviennent invalides
Les synonymes créés sur la table persistent mais leur utilisation retourne une erreur
Tous les extents alloués à la table supprimée sont ajoutés à la liste des espaces libres du tablespace

1-4 - Gestion des tables organisées en index

Une table standard contient des données stockées sans ordre particulier.
Dans une table organisée en index, les données sont stockées dans une structure de type B-tree, triées sur la clé primaire.

Une table organisée en index procure un accès rapide aux données car toutes les informations sont contenues dans la structure de type index (en effet les données n'appartenant pas à l'index ne sont pas stockées dans un espace différent).
Pour la même raison, l'espace de stockage nécessaire est moindre puisque les données contenues dans les colonnes indexées ne sont pas dupliquées (table et index).
Il n'est donc plus nécessaire de stocker dans l'index le ROWID pointant sur le reste des données non indexées comme dans une table standard.

Ce type d'organisation convient parfaitement pour les requêtes dont les termes sont basés exclusivement sur les éléments de la clé primaire.

Note : dans la suite de cette section, le terme table organisée en index sera indiqué sous sa forme compressée anglaise : IOT (Index-Organised Table).

1-4-1 - Création d'une table organisée en index

L'instruction de création d'une IOT est toujours CREATE TABLE. Toutefois il est nécessaire de renseigner certaines informations supplémentaires :

- ajout de la clause ORGANIZATION INDEX

- spécification obligatoire d'une contrainte de clé primaire

- en option une indication de stockage du débordement (OVERFLOW) permettant d'indiquer les colonnes qui seront stockées en dehors de la structure B-tree (dans un autre segment de données) ainsi qu'une clause INCLUDING pour indiquer quelles colonnes n'appartenant pas à la clé primaire seront stockées dans la zone d'overflow

- un argument PCTTHRESHOLD pour définir le pourcentage d'espace réservé dans le bloc d'index. Chaque partie de la ligne qui excèdera ce pourcentage sera stockée dans la zone de débordement. En d'autres termes, le contenu d'une ligne peut être divisé en deux partie. une partie "préfixe" stockée dans la feuille d'index et une partie "suffixe" stockée dans la zone de débordement. L'entrée de l'index (partie préfixe) contient donc les éléments de la clé primaire plus les autres données à concurrence du pourcentage indiqué dans le paramètre PCTTHRESHOLD ainsi qu'un pointeur vers le reste de la ligne

exemple de création d'une table organisée en index

CREATE TABLE perso
(
nom        VARCHAR2(30),
prenom        VARCHAR2(20),
age        NUMBER(3),
infos        VARCHAR2(512),
CONSTRAINT    pk_perso PRIMARY KEY (nom, prenom)
)
ORGANIZATION INDEX
TABLESPACE    tbs1
PCTTHRESHOLD    20
OVERFLOW        TABLESPACE tbs2 ;

Une table est créée avec 4 colonnes. les deux premières sont les éléments de la clé primaire.
La table (partie préfixe) est stockée dans le tablespace tbs1
Le pourcentage d'espace maximum occupé par les colonnes n'appartenant pas à la clé primaire et devant donc être déplacées dans la zone de débordement est de 20% de la taille d'un bloc
La zone de débordement est stockée dans le tablespace tbs2

Une IOT peut stocker des types objet :

CREATE OR REPLACE TYPE typ_nom AS OBJECT
(
nom    VARCHAR2(30),
prenom    VARCHAR2(20)
) ;

CREATE TABLE perso
(
num    NUMBER(7)    PRIMARY KEY,
id    typ_nom
)
ORGANIZATION INDEX ;

Elle peut être elle-même de type objet :

CREATE TABLE perso OF typ_nom
(
nom    PRIMARY KEY
)
ORGANIZATION INDEX ;

Il est possible d'utiliser une sous-requête pour créer une IOT :

CREATE TABLE perso
(
empno    PRIMARY KEY,
ename,
job
)
ORGANIZATION INDEX PARALLEL (DEGREE 2)
AS SELECT empno, ename, job FROM hr.emp ;

Utilisation de la clause OVERFLOW

Cette clause indique dans quel tablespace les colonnes n'appartenant pas à la clé sont stockées (celle qui excèdent le pourcentage indiqué, 20% dans l'exemple précédent).

Si une mise à jour d'une de ces colonnes réduit ou augmente la taille de la ligne, Oracle identifie quelle partie de la ligne ( préfixe ou suffixe) est impactée, et éventuellement, reconstruit une partie suffixe afin de conserver la loi des 20%.

Choix de la valeur de PCTTHRESHOLD

La valeur de ce pourcentage doit être calculée de sorte que la partie préfixe contienne les colonnes n'appartenant pas à la clé primaire dont les valeurs sont fréquemment recherchées.

Vous pouvez utiliser l'instruction ANALYZE TABLE ... LIST CHAINED ROWS pour déterminer quelles colonnes de votre IOT sont déplacées dans la zone de débordement.

Utilisation de la clause INCLUDING

Après avoir spécifié l'argument PCTTHRESHOLD, vous pouvez utiliser la clause INCLUDING pour contrôler quelles colonnes n'appartenant pas à la clé primaire doivent rester dans la partie préfixe. Oracle conservera (dans la limite de PCTTHRESHOLD) ces colonnes dans la feuille d'index. Toutes les colonnes suivant celle indiquée dans la clause INCLUDING seront stockée dans la zone de débordement.

Remarque : Oracle déplace toutes les colonnes appartenant à la clé primaire en tête de la table.

L'exemple suivant montre comment forcer le placement dans la zone de débordement de toutes les colonnes n'appartenant pas à la clé primaire :

CREATE TABLE perso
(
nom        VARCHAR2(30),
prenom        VARCHAR2(20),
age        NUMBER(3),
infos        VARCHAR2(512),
CONSTRAINT    pk_perso PRIMARY KEY (nom, prenom)
)
ORGANIZATION INDEX
TABLESPACE    tbs1
PCTTHRESHOLD    20
INCLUDING        age
OVERFLOW        TABLESPACE tbs2 ;

Où seules les colonnes jusqu'à la colonne age sont conservées dans la partie préfixe.

1-4-2 - Utilisation de la compression de clé

Cette fonctionnalité est intéressante pour éliminer la redondance d'informations stockées dans la clé primaire.
Elle consiste à diviser la clé en deux parties. la première stocke l'information redondante et la deuxième celle qui ne l'est pas.

Prenons un exemple concret.

Soit la table suivante :

CREATE TABLE test
(
a    NUMBER    PRIMARY KEY,
b    NUMBER,
c    NUMBER,
)
ORGANIZATION INDEX COMPRESS ;

Et les valeurs insérées :

(1,2,3), (1,2,4), (1,2,6), (2,3,4), (2,4,5), (3,5,6)

Toutes les valeurs 1 et 2 de la clé primaire seront compressées, permettant de stocker plus de lignes dans la même feuille d'index.

Il est possible de fixer une valeur à l'argument COMPRESS (défaut : 2) afin de déterminer combien de colonnes de la clé primaire participent à la compression.

L'activation de la fonctionnalité de compression peut être effectuée suite à l'une des actions suivantes :

- création de la table organisée en index

- déplacement de la table organisée en index

La désactivation de la fonctionnalité de compression s'effectue avec l'instruction :

ALTER TABLE ... MOVE NOCOMPRESS ;

1-4-3 - Modification d'une IOT

Utilisez l'instruction ALTER TABLE pour modifier la structure et les attributs de stockage pour la partie préfixe et la partie suffixe d'une IOT.

Tous les attributs spécifiés avant la clause OVERFLOW s'appliquent à la partie préfixe et tous les attributs spécifiés après cette clause s'appliquent à la zone de débordement.

Par exemple vous pouvez fixer le paramètre INITRANS de la partie préfixe à 4 et le même paramètre INITRANS de la partie suffixe à 6 :

ALTER TABLE perso INITRANS 4 OVERFLOW INITRANS 6 ;

Vous pouvez modifier la valeur des paramètres PCTTHRESHOLD et INCLUDING :

ALTER TABLE perso PCTTHRESHOLD 10 INCLUDING prenom ;

Pour les IOT créées sans zone de débordement, il est possible d'ajouter explicitement cette zone avec l'instruction :

ALTER TABLE perso ADD OVERFLOW TABLESPACE tbs3 ;

1-4-4 - Recréation d'une IOT

Pour reconstruire une IOT, utilisez l'instruction :

ALTER TABLE ... MOVE ;

Pour reconstruire une IOT en ligne, ajouter la clause ONLINE :

ALTER TABLE perso MOVE ONLINE ;

Seule la partie préfixe est reconstruite. Si vous voulez reconstruire également la zone de débordement, il faut l'indiquer explicitement dans l'instruction :

ALTER TABLE perso MOVE ONLINE TABLESPACE tbs1
OVERFLOW TABLESPACE tbs2 ;

Reconstruction d'une table avec déplacement du segment LOB :

CREATE TABLE test_lob
(
num        NUMBER(7)    PRIMARY KEY,
col_lob    CLOB
)
ORGANIZATON INDEX
LOB (col_lob) STORE AS (TABLESPACE tbs3) ;

ALTER TABLE test_lob MOVE LOB (col_lob) STORE AS (TABLESPACE tbs4) ;

1-4-5 - Analyse d'une IOT

Rappel : Oracle recommande d'utiliser les fonctions du package DBMS_STATS pour la collecte de statistiques à destination de l'optimiseur, réservant l'utilisation de l'instruction ANALYZE à la collecte de statistiques non destinées à l'optimiseur (validation de structure, liste des lignes chaînées).

ANALYZE TABLE perso COMPUTE STATISTICS ;

Cette instruction analyse à la fois la partie préfixe et la zone de débordement.

Les statistiques logiques sont consultables à travers les vues USER_TABLES, ALL_TABLES et DBA_TABLES.

Les statistiques physiques de la partie préfixe sont consultables à travers les vues USER_INDEXES, ALL_INDEXES et DBA_INDEXES (colonnes LAST_ANALYZED, BLEVEL, LEAF_BLOCKS, DISTINCTS_KEYS)

Les statistiques physiques de la zone de débordement sont consultables à travers les vues USER_TABLES, ALL_TABLES et DBA_TABLES (LAST_ANALYZED, NUM_ROWS, BLOCKS, EMPTY_BLOCKS) en précisant la recherche sur la partie overflow (WHERE IOT_TYPE='IOT_OVERFLOW').

1-4-6 - Conversion d'une IOT en table standard (régulière)

Deux méthodes possibles :

- utiliser l'outil d'exportation (EXPORT), créer une table standard de même structure et réaliser l'importation (IMPORT)

- créer la nouvelle table avec une syntaxe CREATE TABLE ... AS SELECT

1-5 - Gestion des tables externes

Depuis la version 9i, il est possible de gérer des tables externes.
Ces tables consistent en fichiers plats stockés hors de la base et sont interrogeables de la même manière que les tables stockées en base.

Il est possible d'interroger, joindre, trier les données d'une table externe, de créer des vues et des synonymes.

Il n'est pas possible d'utiliser les instruction du DML (INSERT, UPDATE et DELETE) ni de créer un index sur une table externe.

Le mécanisme des tables externes est à rapprocher de l'utilisation de l'outil Sql*Loader, avec l'avantage que les fichiers plats ne sont pas chargés dans une table standard mais gérés tel quels depuis le système de fichiers (avec toute la simplification de la gestion des interfaces que cela procure).

La définition de toutes les informations nécessaires à la gestion d'une table externe et réalisée dans l'instruction CREATE TABLE ... ORGANIZATION EXTERNAL.

Les privilèges système et objet nécessaire à la manipulation d'une table externe sont les suivants :

Privilèges système :

- CREATE ANY TABLE

- ALTER ANY TABLE

- DROP ANY TABLE

- SELECT ANY TABLE

Privilèges objet :

- ALTER

- SELECT

1-5-1 - Création d'une table externe

Le ou les fichiers plats entrant dans la définition d'une table externe doivent résider dans un répertoire Oracle (DIRECTORY) préalablement défini et pour lequel vous avez le droit de lecture (READ).

Le mécanisme étant proche de celui utilisé par l'utilitaire Sql*Loader, l'écriture éventuelle d'un fichier log et d'un fichier de rejet requiert également le droit d'écriture (WRITE) sur un répertoire Oracle.

Exemple de création d'une table externe.

Soit le fichier plat test_table_externe.txt suivant :

10,Emile,DUBOUDIN, Charcutier
20,Jocelyne,POTAUFEU,Epicière
30,Harry,COVER,Maraîcher
40,Hans,BURGER,Restaurateur

CREATE TABLE employes_ext
(
numero    NUMBER(7),
nom    VARCHAR2(30),
prenom    VARCHAR2(20),
job    VARCHAR2(20)
)
ORGANIZATION EXTERNAL
(
TYPE ORACLE_LOADER
DEFAULT DIRECTORY    nom_directory_read
ACCESS PARAMETERS
(
records delimited by newline
badfile    nom_directory_write:'test_table_externe.bad'
logfile nom_directory_write:'test_table_externe.log'
fields terminated by ','
missing field values are null
( numero, nom, prenom, job )
)
LOCATION ('test_table_externe.txt')
)
PARALLEL
REJECT LIMIT UNLIMITED ;

SELECT * FROM employes_ext ;

1-5-2 - Modification d'une table externe

L'instruction ALTER TABLE est utilisée avec les seules clauses possibles suivantes :

- REJECT LIMIT

- DEFAULT DIRECTORY

- ACCESS PARAMETERS

- LOCATION

- PARALLEL

- ADD COLUMN

- MODIFY COLUMN

- DROP COLUMN

- RENAME TO

1-5-3 - Suppression d'une table externe

Utilisez l'instruction DROP TABLE.

1-6 - Consultation des informations relatives aux tables

Vue

Description

DBA_TABLES
ALL_TABLES
USER_TABLES

Liste des tables relationnelles de la base

DBA_TAB_COLUMNS
ALL_TAB_COLUMNS
USER_TAB_COLUMNS

Liste des colonnes des tables, vues et clusters de la base

DBA_ALL_TABLES
ALL_ALL_TABLES
USER_ALL_TABLES

Liste des tables relationnelles et objets de la base

DBA_TAB_COMMENTS
ALL_TAB_COMMENTS
USER_TAB_COMMENTS

Liste des commentaires attachés aux tables

DBA_COL_COMMENTS
ALL_COL_COMMENTS
USER_COL_COMMENTS

Liste des commentaires attachés aux colonnes des tables

DBA_EXTERNAL_TABLES
ALL_EXTERNAL_TABLES
USER_EXTERNAL_TABLES

Liste des attributs attachés aux tables externes

DBA_EXTERNAL_LOCATIONS
ALL_EXTERNAL_LOCATIONS
USER_EXTERNAL_LOCATIONS

Liste des fichiers sources des tables externes

DBA_TAB_HISTOGRAMS
ALL_TAB_HISTOGRAMS
USER_TAB_HISTOGRAMS

Liste des histogrammes disponibles sur les tables et les vues

DBA_TAB_COL_STATISTICS
ALL_TAB_COL_STATISTICS
USER_TAB_COL_STATISTICS

Liste des statistiques et histogrammes collectés

DBA_TAB_MODIFICATIONS
ALL_TAB_MODIFICATIONS
USER_TAB_MODIFICATIONS

Liste des tables modifiées depuis la dernière collecte de statistiques et sur lesquelles le monitoring est positionné

DBA_UNUSED_COL_TABS
ALL_UNUSED_COL_TABS
USER_UNUSED_COL_TABS

Liste des tables contenant des colonnes inutilisées suite à l'instruction ALTER TABLE ... SET UNUSED

DBA_PARTIAL_DROP_TABS
ALL_PARTIAL_DROP_TABS
USER_PARTIAL_DROP_TABS

Liste des tables contenant des colonnes partiellement supprimées (suite à une erreur ou une interruption utilisateur)

2 - Les tables objet

Une table Oracle peut contenir 2 types d'objets :

- les objets de colonne

- les objets de ligne

Les objets de colonne

Il s'agit de collections (VARRAY, NESTED TABLE) ou de types personnalisés et sont stockés dans une colonne d'une table.

Les objets de ligne

Il s'agit d'un objet représenté sous la forme d'une ligne d'une table.

Les objets de colonne peuvent être stockés dans une colonne d'une table standard.
Les objets de ligne ne peuvent être stockés que dans une table objet et possèdent un identificateur unique appelé OID.

La définition d'un objet s'effectue à l'aide de la commande CREATE TYPE.

Oracle supporte l'héritage de type, c'est à dire qu'un type peut être la source d'un sous-type.
Oracle ne supporte pas l'héritage multiple, c'est à dire qu'un sous-type ne peut pas hériter de plusieurs types différents.

Il existe des types non instanciables, appelés types abstraits et des types instanciables permettant de créer des instances d'objets.
Un type ne peut pas contenir de colonnes de type BOOLEAN, ROWID, LONG et LONGRAW et ne peut pas être défini dans du code PL/SQL.

La syntaxe de création d'une table objet est identique à celle d'une table standard avec toutefois des options supplémentaires :

- OBJECT IDENTIFIER IS qui indiqe la méthode de génération des Objects Identifier Descriptors (OID)

- OIDINDEX permettant de définir un index sur la colonne OID

Une table (ainsi qu'un type) peut contenir une colonne contenant une référence (REF) vers un objet ligne d'une autre table (type) permettant l'extraction de l'objet ciblé :

Colonne référençant un type

CREATE TABLE nom_table
(  ...
ref_col REF nom_du_type,
...
);

La clause SCOPE IS permet de limiter la portée d'une référence à une table objet existante.

Colonne référençant un type source d'une table objet

CREATE TABLE nom_table
(  ...
ref_col REF nom_du_type SCOPE IS nom_table_objet,
...
);

La clause SUBSTITUTABLE AT ALL LEVELS (défaut) spécifie que des objets correspondant à des sous-types peuvent être insérés dans la table.
La clause NOT SUBSTITUTABLE AT ALL LEVELS indique que la table ne pourra pas stocker d'objets correspondant à des sous-types du type de base.

Table ne pouvant pas contenir de sous-type du type principal

CREATE TABLE nom_table OF nom_type
NOT SUBSTITUTABLE AT ALL LEVELS
(  ...  );

Dans le cas où la substitution de type est autorisée, il est possible de forcer le sous-type autorisé sur une colonne :

Forçage de sous-type sur une colonne

CREATE TABLE nom_table
(  col1 nom_type
...
)COLUMN col1 IS OF (ONLY nom_sous_type);

Contraintes

Les contraintes se définissent de la même manière que pour une table standard, à la différence qu'elles ne s'appliquent pas sur les colonnes mais sur les attributs du type:

Contraintes appliquées à une table objet

CREATE TYPE etat_civil_type AS OBJECT
( NOM VARCHAR2(32), PRENOM VARCHAR2(20) )
/
CREATE TYPE adresse_type AS OBJECT
( ADR1 VARCHAR2(32), ADR2 VARCHAR2(32), ADR3 VARCHAR2(32), CP NUMBER(5), VILLE VARCHAR2(32) )
/
CREATE TYPE personne_type AS OBJECT
( NUM_SS     VARCHAR2(20),
etat_civil etat_civil_typ,
adresse    adresse_type,
STATUT     VARCHAR2(15)
)
/
CREATE TABLE personnel OF personne_type
(
CONSTRAINT pk_num_ss PRIMARY KEY (NUM_SS),
CONSTRAINT df_statut STATUT DEFAULT 'Interne',
CONSTRAINT nn_ville  CHECK (adresse.VILLE NOT NULL),
CONSTRAINT nn_nom    CHECK (etat_civil.NOM IS NOT NULL),
CONSTRAINT un_nom    UNIQUE (etat_civil.NOM),
CONSTRAINT ck_cp     CHECK (adresse.cp BETWEEN 1000 and 99000)
)
/

Références

Les références sont des pointeurs vers un objet ligne.
Matérialisées par le type SQL : REF, elles ne peuvent contenir qu'un OID et peuvent être déclarées dans un type ou dans une table relationnelle :

Contraintes appliquées à une table objet

CREATE TABLE nom_table
(
...
nom_colonne REF adresse_type,
...
)
/

Les références sont pratiques dans le sens ou, pointant directement sur un objet ligne d'une table objet, elles ne nécessitent pas de jointure dans la requête SQL.

Contraintes sur les références

La contrainte NOT NULL peut être définie sur une colonne de type REF via la clause CHECK :

Contraintes appliquées à une table objet

CREATE TABLE nom_table
(
...
nom_colonne REF adresse_type,
CONSTRAINT nn_nom_colonne CHECK (nom_colonne IS NOT NULL)
...
)
/

La contrainte SCOPE IS permet de limiter la portée d'une référence à une table objet précise :

Contrainte de type SCOPE IS

CREATE TABLE nom_table
(
...
nom_colonne REF adresse_type SCOPE IS adresse,
CONSTRAINT nn_nom_colonne CHECK (nom_colonne IS NOT NULL)
...
)
/

Dans cet exemple, la colonne nom_colonne contiendra une référence (OID) non null et provenant exclusivement de la table objet adresse.

La contrainte REFERENCES est équivalente à SCOPE IS :

Contrainte de type REFERENCES

CREATE TABLE nom_table
(
...
nom_colonne REF adresse_type CONSTRAINT fk_nom_colonne REFERENCES adresse,
CONSTRAINT nn_nom_colonne CHECK (nom_colonne IS NOT NULL)
...
)
/

La contrainte FOREIGN KEY peut être associée à la clause REFERENCES :

Contrainte de type FOREIGN KEY

CREATE TABLE nom_table OF personne
(
...
CONSTRAINT fk_ref_table FOREIGN KEY (ref_colonne) REFERENCES table_objet
...
)
/

3 - Les tables XML

Il est possible de stocker des données au format XML dans une table.

Pour cela, Oracle a créé un nouveau type : XMLType.

Ce type peut être utilisé dans les procédures PL/SQL comme paramètre, variable et valeur retour.
Il contient des méthodes qui permettent de manipuler son contenu.
Des fonctions permettent de créer, extraire et indexer ce type de données.

Les requêtes SQL peuvent être appliquées sur tout ou partie d'un document XML.

Ce type de données peut être stocké sous deux formes :

- Dans une colonne de type LOB

- Dans un objet structuré

Stockage dans une colonne de type LOB

Le document est stocké "tel quel" dans la colonne.
Les performances d'accès par les instructions du DML sont médiocres.
L'accès par les commandes SQL est limité.
Consomme plus d'espace.

Ce type de stockage est conseillé si vous souhaiter simplement stocker le document XML, sans mise à jour particulière.

Stockage dans un objet structuré

Certains caractères non significatifs ne sont pas stockés (saut de ligne, espaces de fin,etc.).
Les performances d'accès par les instructions du DML sont excellentes.
L'accès par les commandes SQL est satisfaisant.
Nécessite moins d'espace de stockage.

Ce type de stockage convient parfaitement à la manipulation de parties du document XML.

Exemple de création d'une table contenant une colonne de type XMLType :

CREATE TABLE doc_xml(
identifiant NUMBER(5) PRIMARY KEY,
Nom         VARCHAR2(64) NOY NULL,
Doc_xml     XMLType )
XMLType COLUMN Doc_xml
STORE AS CLOB (
TABLESPACE tbs_lob
STORAGE (INITIAL 4096 NEXT 4096)
CHUNK 4096 NOCACHE NOLOGGING ) ;

Exemple de création d'une table de type XMLType (stockage dans une colonne CLOB) :

CREATE TABLE doc_xml2 OF XMLType ;

Exemple de création d'une table de type XMLType (stockage dans un objet relationnel) :

CREATE TABLE xwarehouses (
warehouse_id    NUMBER,
warehouse_spec  XMLTYPE)
XMLTYPE warehouse_spec STORE AS OBJECT RELATIONAL
XMLSCHEMA "http://www.oracle.com/xwarehouses.xsd"
ELEMENT "Warehouse";

Changer les caractéristiques de stockage d'une colonne XMLType :

ALTER TABLE doc_xml
MODIFY LOB (Doc_xml.XMLDATA)
(STORAGE (NEXT 16K) CACHE) ;

Le schéma XML

Un schéma XML définit une classe de document XML.

Il permet de s'assurer que les données insérées dans les colonnes de type XMLType sont en adéquation avec ce schéma.

Si l'on observe le document XML suivant :

<?xml version="1.0"?>
<purchaseOrder orderDate="1999-10-20">
<shipTo country="US">
<name>Alice Smith</name>
<street>123 Maple Street</street>
<city>Mill Valley</city>
<state>CA</state>
<zip>90952</zip>
</shipTo>
<billTo country="US">
<name>Robert Smith</name>
<street>8 Oak Avenue</street>
<city>Old Town</city>
<state>PA</state>
<zip>95819</zip>
</billTo>
<comment>Hurry, my lawn is going wild!</comment>
<items>
<item partNum="872-AA">
<productName>Lawnmower</productName>
<quantity>1</quantity>
<USPrice>148.95</USPrice>
<comment>Confirm this is electric</comment>
</item>
<item partNum="926-AA">
<productName>Baby Monitor</productName>
<quantity>1</quantity>
<USPrice>39.98</USPrice>
<shipDate>1999-05-21</shipDate>
</item>
</items>
</purchaseOrder>

Voici ce que doit spécifier le schéma afin que le document XML soit accepté dans la colonne de type XMLType :

<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<xsd:annotation>
<xsd:documentation xml:lang="en">
Purchase order schema for Example.com.
Copyright 2000 Example.com. All rights reserved.
</xsd:documentation>
</xsd:annotation>

<xsd:element name="purchaseOrder" type="PurchaseOrderType"/>

<xsd:element name="comment" type="xsd:string"/>

<xsd:complexType name="PurchaseOrderType">
<xsd:sequence>
<xsd:element name="shipTo" type="USAddress"/>
<xsd:element name="billTo" type="USAddress"/>
<xsd:element ref="comment" minOccurs="0"/>
<xsd:element name="items"  type="Items"/>
</xsd:sequence>
<xsd:attribute name="orderDate" type="xsd:date"/>
</xsd:complexType>

<xsd:complexType name="USAddress">
<xsd:sequence>
<xsd:element name="name"   type="xsd:string"/>
<xsd:element name="street" type="xsd:string"/>
<xsd:element name="city"   type="xsd:string"/>
<xsd:element name="state"  type="xsd:string"/>
<xsd:element name="zip"    type="xsd:decimal"/>
</xsd:sequence>
<xsd:attribute name="country" type="xsd:NMTOKEN"fixed="US"/>
</xsd:complexType>

<xsd:complexType name="Items">
<xsd:sequence>
<xsd:element name="item" minOccurs="0" maxOccurs="unbounded">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="productName" type="xsd:string"/>
<xsd:element name="quantity">
<xsd:simpleType>
<xsd:restriction base="xsd:positiveInteger">
<xsd:maxExclusive value="100"/>
</xsd:restriction>
</xsd:simpleType>
</xsd:element>
<xsd:element name="USPrice"  type="xsd:decimal"/>
<xsd:element ref="comment"   minOccurs="0"/>
<xsd:element name="shipDate" type="xsd:date" minOccurs="0"/>
</xsd:sequence>
<xsd:attribute name="partNum" type="SKU" use="required"/>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>

<!-- Stock Keeping Unit, a code for identifying products -->
<xsd:simpleType name="SKU">
<xsd:restriction base="xsd:string">
<xsd:pattern value="\d{3}-[A-Z]{2}"/>
</xsd:restriction>
</xsd:simpleType>
</xsd:schema>

La contrainte de schéma se spécifie au moment de la création de la table :

CREATE TABLE xwarehouses OF XMLTYPE
XMLSCHEMA "http://www.w3.org/2001/XMLSchema"
ELEMENT "Warehouse";

La documentation Oracle concernant l'utilisation des schémas XML est disponible à l'url suivante :
http://download-west.oracle.com/docs/cd/B10501_01/appdev.920/a96620/appbsch.htm#621642

4 - Syntaxe de l'instruction CREATE TABLE

4-1 - Syntaxe 9i, 10g

Est présentée dans ce chapitre la syntaxe de l'instruction CREATE TABLE des versions Oracle 9i et 10g.

Pour obtenir des informations sur les versions antérieures, consultez la documentation officielle,
notament la syntaxe CREATE TABLE de la version Oracle 8i..

CREATE_TABLE

[object_table] [XMLType_table]

relational_table

[relational_properties] [physical_properties] [table_properties]

GLOBAL TEMPORARY signifie la création d'une table temporaire dont les données insérées ne persistent que le temps d'une transaction ou d'une session
schema indique le schéma utilisateur dans lequel la table sera créée. sans indication, la table est créée dans le schéma de l'utilisateur
table représente le nom de la table à créer (30 caractères maximum commençant par une lettre)
ON COMMIT DELETE ROWS indique pour une table temporaire que les données seront supprimées à la fin de la transaction
ON COMMIT PRESERVE ROWS indique pour une table temporaire que les données seront supprimées à la fin de la session

object_table

[object_table_substitution] [object_properties] [OID_clause] [OID_index_clause]

OF object_type permet de créer une table d'objets de type object_type
Chaque ligne de la table contiendra une instance de cet objet et chaque instance se verra assigner un indentifiant d'objet unique (OID).
object_table_substitution spécifie que les lignes correspondant à des sous-types peuvent être insérées
OID_clause permet d'indiquer si l'identifiant d'objet unique (OID) est généré par le système (défaut) ou basé sur la clé primaire
Il n'est pas possible de spécifier OBJECT IDENTIFIER IS PRIMARY KEY si une contrainte de clé primaire n'est pas déclarée
Il n'est pas possible d'utiliser cette clause sur une table imbriquée (nested table)
OID_index_clause valide seulement pour l'option SYSTEM GENERATED et spécifie un index et éventuellement ses caractéristiques de stockage sur la colonne cachée de l'identifiant objet

XMLType_table

[XMLType_storage] [XMLSchema_spec]

XMLTYPE indique que la colonne contiendra des données au format XML
XMLType_storage spécifie si la donnée XML est stockée dans une colonne objet (STORE AS OBJECT RELATIONAL) ou une colonne CLOB (STORE AS CLOB)
si l'option STORE AS OBJECT RELATIONAL est choisie, alors la clause XMLSchema_spec doit également être renseignée
XMLSchema_spec permet d'indiquer l'url d'un schéma XML enregistré (facultatif) et le nom d'un élément XML (obligatoire).
Cela permet de refuser l'insertion de données XML dont le format ne correspond pas au schéma indiqué.
Si l'url est spécifiée, le schéma XML doit avoir été préalablement enregistré à l'aide du package DBMS_XMLSCHEMA.

relational_properties

[supplemental_logging_props]

column spécifie le nom de la colonne (30 caractères maximum)
datatype spécifie le type de la colonne (voir les types oracle en introduction)
DEFAULT expr indique la valeur assignée par défaut à la colonne si celle-ci n'est pas spécifiée dans l'ordre INSERT
Toute fonction SQL peut-être utilisée dans l'expression mais celle-ci ne peut pas contenir de référence à une fonction PL/SQL ou à une autre colonne, ni les pseudo-colonnes LEVEL, PRIOR et ROWNUM.
inline_constraint spécifie une contrainte de niveau colonne
inline_ref_constraint spécifie une contrainte de niveau colonne (pour les colonnes de type REF)
out_of_line_constraint spécifie une contrainte de niveau table (après la définition des colonnes)
out_of_line_ref_constraint spécifie une contrainte de niveau table (pour les colonnes de type REF)

object_table_substitution

SUBSTITUTABLE AT ALL LEVELS (défaut) indique des objets correspondant à des sous-types peuvent être insérés dans la table
NOT SUBSTITUTABLE AT ALL LEVELS indique des objets correspondant à des sous-types ne peuvent pas être insérés dans la table

object_properties

[supplemental_logging_props]

attribute spécifie le nom d'une colonne d' un objet

OID_clause

SYSTEM GENERATED indiqe que le système attribut automatiquement le OID
PRIMARY KEY indique que l'OID est basé sur la clé primaire

OID_index_clause

[physical_attributes_clause]

physical_properties

[data_segment_compression] [segment_attributes_clause] [index_org_table_clause] [external_table_clause]

ORGANIZATION HEAP (défaut) pour une table standard
ORGANIZATION INDEX pour une table organisée en index (IOT)
ORGANIZATION EXTERNAL pour une table externe
CLUSTER cluster indique que la table fait partie d'un cluster de nom cluster
La liste indique les colonnes appartenant au cluster

segment_attributes_clause

[physical_attributes_clause]

tablespace défini le nom du tablespace dans lequel sera créée la table. si celui ci est homis, la table sera créée dans le tablespace par défaut de l'utilisateur

physical_attributes_clause

[storage_clause]

PCTUSED indique (en pourcentage) le niveau en deçà duquel le bloc sera disponible (affichage en début de freelist) pour de nouvelles insertions
PCTFREE indique (en pourcentage) l'espace que l'on souhaite conserver dans le bloc pour les mises à jour
INITRANS defini le nombre initial de transactions allouées à chaque bloc
MAXTRANS indique combien de transactions (maxi : 255) peuvent réaliser simultanément des changement dans un bloc

Si ces valeurs ne sont pas spécifiées, les valeurs par défaut sont les suivantes :

- PCTFREE: 10

- PCTUSED: 40

- INITRANS: 1

- MAXTRANS: dépendant du data block size

storage_clause

Cette clause permet de fixer les paramètres de stockage.

INITIAL exprime en octets la taille du premier segment créé (en Ko si vous suffixez avec K et en Mo si vous suffixez avec M). si ce paramètre n'est pas spécifié, sa valeur par défaut est de 5 blocs (la valeur maxi dépend du système d'exploitation)
INITIAL ne peut pas être spécifié dans une instruction ALTER TABLE
NEXT exprime en octets la taille des segments suivants (en Ko si vous suffixez avec K et en Mo si vous suffixez avec M). si ce paramètre n'est pas spécifié, sa valeur par défaut est de 5 blocs (la valeur maxi dépend du système d'exploitation)
PCTINCREASE spécifie le pourcentage d'augmentation des segments ultérieurement alloués. (la valeur par defaut est de 50). si cette valeur est positionnée à 0, cela indique ques les extents auront toujours la même taille
Chaque nouvel extent créé voit sa taille initialisé à celle de l'extent précédent multiplié par le pourcentage de PCTINCREASE.
PCTINCREASE ne peut pas être spécifié sur un segment d'annulation (rollback segment).
MINEXTENTS indique le nombre minimum d'extents alloués à la création de la table (valeur minimum et par défaut : 1)
Il n'est pas possible de changer cette valeur pour un objet qui réside dans un tablespace géré localement.
MAXEXTENTS spécifie le nombre maximum d'extents pouvant être alloués sur la table (valeur minimum : 1, sauf pour un segment d'annulation dont la valeur minimum est : 2)
Il n'est pas possible de changer cette valeur pour un objet qui réside dans un tablespace géré localement.
UNLIMITED indique que l'on ne souhaite pas fixer de nombre maximum d'extents
FREELIST GROUPS spécifie le nombre de groupes de free lists (défaut et valeur minimum : 1)
Chaque groupe de free list utilise un bloc.
Si vous ne spécifiez pas une valeur suffisante pour le paramètre INITIAL pour couvrir la valeur minimum + 1 bloc pour chaque groupe de free list, Oracle augmente la valeur de INITIAL.
FREELISTS indique le nombre de free lists pour chaque groupe (valeur défaut et minimum : 1). La valeur maximum dépend de la taille du bloc et peut être obtenu en spécifiant une valeur trop grande pour ce paramètre
OPTIMAL (uniquement pour un segment d'annulation) permet de spécifier la taille optimale en octets d'un segment d'annulation. (préfixé par K pour une taille exprimée en Ko et M pour Mo)
Ce paramètre est appelé optimal dans la mesure ou Oracle essaie de maintenir cette taille en désallouant les extents non nécessaires.
NULL permet d'indiquer à Oracle de ne pas effectuer cette opération de désallocation
BUFFER_POOL permet de spécifier un tampon de cache par défaut où les blocs de l'objet seront stockés
Si vous définissez ce paramètre pour une table ou un index partitionné, alors les partitions héritent du cache de la table ou de l'index, tant qu'ils ne sont pas surchargés par une clause de niveau partition.
Pour une IOT, vous pouvez spécifier un buffer pool spécifique pour le segment d'index et le segment d'overflow.

Restrictions sur le paramètre BUFFER_POOL

- Vous ne pouvez pas spécifier ce paramètre pour une table en cluster

- Vous ne pouvez pas spécifier ce paramètre pour un tablespace ou un segment d'annulation

KEEP permet de charger les blocs dans le KEEP buffer pool
RECYCLE permet de charger les blocs dans le RECYCLE buffer pool
DEFAULT permet de charger les blocs dans le DEFAULT buffer pool

data_segment_compression

Clause utilisée avec les tables organisées en index (IOT).
COMPRESS permet de compresser les segments de données
NOCOMPRESS interdit la compression des segments de données

table_properties

[parallel_clause] [enable_disable_clause] [row_movement_clause]

CACHE permet de placer les blocs en début de LRU lus lors d'un FULL TABLE SCAN
NOCACHE permet de placer les blocs en fin de LRU lus lors d'un FULL TABLE SCAN
ROWDEPENDENCIES Cette clause permet de définir si un cluster utilise le suivi de dépendance au niveau ligne (chaque ligne contient un numéro de modification système (SCN) représentant un temps supérieur ou égal au dernier enregistrement (commit) qui a modifié la ligne. ce réglage ne peut pas être modifié une fois que le cluster est créé)
ROWDEPENDENCIES indique que le tracking est activé
NOROWDEPENDENCIES indique que le traking n'est pas activé
MONITORING permet d'activer la collecte de statistiques (non applicable sur une table temporaire)
NOMONITORING (défaut) n'active pas la collecte de statistiques (non applicable sur une table temporaire)

column_properties

[nested_table_col_properties] [varray_col_properties] [LOB_storage_clause] [LOB_partition_storage]

object_type_col_properties

substitutable_column_clause

Cette clause indique qu'une colonne objet ou un attribut de la même hiérarchie sont subsituables.

nested_table_col_properties

NESTED TABLE cette clause (obligatoire lors de la création d'une table contenant une ou plusieurs colonnes contenant des tables imbriquées) permet de définir les informations de stockage propres à ce type de colonne
nested_item indique le nom de la colonne de type table imbriquée (nested table)
COLUMN_VALUE indique le nom de la sous-table imbriquée ou du sous-varray contenu dans une autre table imbriquée (collection multiniveaux)
storage table indique le nom de la table dans laquelle la collection sera stockée
RETURN AS indique le type de résultat retourné lors d'une interrogation
VALUE retourne une copie de la collection
LOCATOR retourne un pointeur vers la collection

varray_col_properties

Cette clause permet de spécifier les informations de stockage du LOB dans lequel le varray sera stocké.

Si la clause STORE AS LOB est spécifiée, 2 cas sont possibles :

- La taille maximum du varray est inférieure à 4000 octets et le stockage en ligne n'est pas désactivé, alors le tableau sera stocké en ligne (dans la table)

- La taille maximum du varray est supérieure à 4000 ou le stockage en ligne est désactivé, alors le tableau sera stocké en dehors de la table

Si elle n'est pas spécifiée, 2 cas sont possibles :

- La taille maximum du varray est inférieure à 4000 octets, le tableau sera stocké en ligne (dans une colonne de type RAW)

- La taille maximum du varray est supérieure à 4000 octets, le tableau sera stocké hors ligne (BLOB hors de la table)

Il n'est pas possible de spécifier le tablespace dans les infos de paramètrage du LOB (Le LOB est créé dans le même tablespace que la table principale).

LOB_storage_clause

Cette clause permet de spécifier les paramètres de stockage d'une ou plusieurs colonnes LOB.
Si elle n'est pas spécifiée, Oracle génère un nom pour chaque colonne de type SYS_LOB_Pn pour la partie data du LOB (ou n est un numéro généré automatiquement) ou SYS_IL_Pn pour l'index du LOB
et SYS_LOB_SUBPn et SYS_IL_SUBPn pour des LOB de sous-partitions.

LOB_item spécifie la colonne ou l'attribut objet LOB sur lequel vous souhaitez définir le tablespace et les informations de stockage
LOB_segname spécifie le nom du segment (interdit si vous spécifiez plus d'une colonne LOB)
LOB_parameters permet de spécifier toutes les informations de stockage

LOB_parameters

[logging_clause]

tablespace est le nom du tablespace dans lequel sera stocké le LOB
ENABLE STORAGE IN ROW permet de stocker le contenu du LOB dans une colonne de type RAW de la table (si sa taille est inférieure à 4000 octets - les infos de contrôle)
Cette clause ne peut pas être utilisée pour une table organisée en index à moins que la partie OVERFLOW ait été spécifiée.
DISABLE STORAGE IN ROW indique que le LOB sera stocké en dehors de la table
La clause STORAGE IN RAW ne peut plus être modifiée, sauf si la table est déplacée (move).
CHUNK integer spécifie le nombre d'octets a allouer pour la manipulation du LOB
La valeur maximale pour integer est 32768 (taille de bloc maximale possible sous Oracle).
Si integer n'est pas un multiple du data block size, il est arrondi au data block size supérieur.
(Si le data block size actuel est de 2048 et vous spécifiez 3000, alors integer est arrondi à 4096)
Une fois définie, cette valeur ne plus être modifiée.
Elle ne peut pas non plus excéder la valeur du paramètre NEXT défini dans la clause de stockage.
PCTVERSION integer indique le pourcentage maximum (défaut 10) d'espace utilisé pour maintenir les anciennes versions du LOB
En clair, une ancienne version du LOB ne sera pas écrasée tant qu'elle ne consommera que integer% de l'espace.
Cette clause peut être spécifiée si la base tourne en manual ou automatic undo mode.
PCTVERSION est par défaut en manual undo mode et RETENTION est par défaut en automatic undo mode
RETENTION indique que l'on souhaite conserver les anciennes versions du LOB. Oracle utilise la valeur du paramètre UNDO_RETENTION pour déterminer la valeur (en temps) des données non enregistrées à conserver dans la base
Vous ne pouvez pas spécifier PCTVERSION et RETENTION en même temps.
FREEPOOLS integer indique le nombre de groupes de freelist pour le segment LOB. Normalement integer doit correspondre au nombre d'instances en environnement Real Application Clusters et à 1 pour une base constituée d'une seule instance
Cette clause ne peut être spécifiée que si la base tourne en automatic undo mode.
Vous ne pouvez pas spécifier à la fois FREEPOOLS et le paramètre FREELIST GROUPS de la clause de stockage.
CACHE indique que vous souhaitez conserver les blocs lus en mémoire
NOCACHE spécifie que vous ne souhaitez pas conserver les blocs lus en mémoire
CACHE READS indique que seuls les blocs lus sont placés en cache (et non les blocs écrits)

logging_clause

Cette clause indique si la création de la table, d'une partition, d'une définition de stockage d'un LOB seront inscrits dans les redo log files (LOGGING) ou non (NOLOGGING).
Elle indique également si les opérations d'insertions (direct-path) depuis SQL*Loader seront inscrites dans les redo log files.

LOB_partition_storage

[LOB_storage_clause] [varray_col_properties]

Cette clause permet de définir les informations de stockage des LOB d'une table partitionnée.

XMLType_column_properties

Permet de définir les informations de stockage d'une colonne de type XML.

XMLType_storage

Une colonne de type XML peut être stockée dans un LOB ou dan un objet.
STORE AS OBJECT RELATIONAL indique de stocker le type XML dans une colonne objet (vous devez alors également spécifier la clause XMLSchema_spec)
STORE AS CLOB indique que la donnée au format XML sera stockée dans un CLOB (dans ce cas vous spécifierez la clause LOB_parameters ou la clause XMLSchema_spec mais pas les deux)

XMLSchema_spec

Permet de spécifier l'url d'un schéma XML enregistré (via le package DBMS_XMLSCHEMA) et (obligatoire) le nom d'un élément XML.

row_movement_clause

Cette clause permet de définir si une ligne d'une table pourra être déplacée (à l'occasion d'une compression de segment ou d'une mise à jour).
Le déplacement de la ligne impliquant une modification du ROWID.
ENABLE ROW MOVEMENT autorise le déplacement d'une ligne
DISABLE ROW MOVEMENT (défaut) interdit le déplacement d'une ligne
Cette clause n'est pas utilisable avec une IOT non partitionnée.

index_org_table_clause

[mapping_table_clause] [key_compression] [index_org_overflow_clause]

Cette clause est utilisée dans la création d'une table organisée en index.
PCTTHRESHOLD integer représente le pourcentage d'espace d'un bloc d'index réservé au stockage d'une ligne
Il doit être suffisant pour stocker la clé primaire.
Toutes les colonnes qui ne rentrent pas dans cet espace sont placées dans une zone de débordement.
integer doit être compris entre 1 et 50 (défaut)
PCTTHRESHOLD ne peut pas être utilisé sur une partition individuelle d'une IOT

mapping_table_clause

Utilisez cette clause pour stocker les ROWID dans une table relationnelle. cette table sera nécessaire pour créer un index bitmap sur l'IOT.

key_compression

Cette clause permet d'activer la compression de clé sur l'IOT.
COMPRESS active la compression de la clé primaire
COMPRESS integer active la compression avec les integer premières colonnes de la clé primaire
NOCOMPRESS (défaut) interdit la compression de la clé primaire
Au niveau partition, la clause COMPRESS n'accepte pas le paramètre integer.

index_org_overflow_clause

[segment_attributes_clause]

Cette clause permet de définir la zone de débordement d'une IOT dans laquelle seront placées les colonnes qui dépassent la valeur de PCTTHRESHOLD.
Lors de la création de l'IOT, Oracle évalue la taille maximum d'une ligne. si celle-ci ne tient pas dans le pourcentage PCTTHRESHOLD et que vous n'avez pas défini de zone d'overflow, Oracle génère une erreur.
Tous les attributs physiques et les clauses de stockage définis après le mot clé OVERFLOW s'appliquent donc aux colonnes placées dans la zone de débordement.
Si la table contient des colonnes LOB, celles-ci seront stockées en dehors de la table, même si la clause OVERFLOW est spécifiée.
INCLUDING column_name indique à partir de quelle colonne les données seront stockées dans la zone de débordement

supplemental_logging_props

[supplemental_log_grp_clause]

Cette clause indique à Oracle d'ajouter des informations dans le flot des logs pour le support des outils basés sur les logs.

external_table_clause

TYPE access_driver_type indique le pilote d'accès à utiliser. Par défaut il s'agit de ORACLE_LOADER
REJECT LIMIT indique le nombre d'erreurs de conversion permises lors de la lecture de la table externe avant d'annuler le processus (par défaut 0)

external_data_properties

directory spécifie le nom du répertoire dans lequel se trouvent les fichiers source
ACCESS PARAMETERS permet de décrire la structure du fichier externe (dans le cas du driver générique Oracle, cette syntaxe se rapproche beaucoup d'un fichier de description Sql*Loader)
LOCATION indique la liste des fichiers externes à lire

table_partitioning_clause

[hash_partitioning] [list_partitioning] [composite_partitioning]

range_partitioning utilisez cette clause pour créer des partitions par tranches de valeurs
hash_partitioning utilisez cette clause pour laisser Oracle définir l'algorithme de découpage
list_partitioning utilisez cette clause pour créer des partitions définies sur une liste de valeurs
composite_partitioning utilisez cette clause pour créer des partitions par tranches, elles-même partitionnées par hash ou par liste

range_partitioning

[range_values_clause] [table_partition_description]

column indique la ou les colonnes sur lesquelles s'appliquent le découpage (a l'exception des colonnes de type ROWID, LONG, LOB, ou TIMESTAMP WITH TIME ZONE)
partition permet d'indiquer le nom de la partition (par défaut SYS_Pn)
range_value_clause indique la valeur maximum (non incluse) de column pour appartenir à la partition. Le mot clé MAXVALUE peut être substitué pour indiquer la valeur maximum (normalement pour la dernière partition)
table_partition_description permet de définir les caractéristiques de stockage, attributs de segment, de compression

hash_partitioning

[individual_hash_partitions] [hash_partitions_by_quantity]

column indique la ou les colonnes sur lesquelles s'appliquent le découpage
individual_hash_partitions permet de nommer chaque partition
hash_partitions_by_quantity permet de définir simplement le nombre de partitions que l'on veut créer

list_partitioning

[list_values_clause] [table_partition_description]

column indique la colonne (et une seule) sur laquelle s'applique le découpage (a l'exception des colonnes de type LOB)
list_values_clause détermine la liste des valeurs (au moins une valeur doit être définie) appartenant à la partition (DEFAUT peut être substitué pour représenter toutes les autres valeurs)
Une partition par liste ne peut pas être sous-partitionnée.
Si la colonne est de type objet, alors vous ne pouvez partitionner que sur un seul attribut de la colonne.
Chaque valeur déterminée dans les listes doit être unique.
Une IOT ne peut pas être partitionnée par liste.

composite_partitioning

[subpartition_by_list] [subpartition_by_hash] [range_values_clause]

Cette clause permet de subdiviser une partition par tranche (range) en sous-partitions par hash ou par liste.
Elle n'est pas permise avec les tables organisées en index.

subpartition_by_hash

[subpartition_template]

Cette clause permet de subdiviser une partition en sous-partitions par hash qui peuvent être définies à partir d'un modèle (template) ou d'une quantité.
SUBPARTITIONS quantity indique le nombre de sous-partitions voulues (par défaut 1) pour chaque partition ainsi que le (les) tablespace(s) dans lesquels elles seront stockées

individual_hash_partitions

[partitioning_storage_clause]

Cette clause permet de nommer chaque partition et d'en indiquer le tablespace de stockage.
En absence de cette clause, Oracle nomme les partitions sous la forme SYS_Pn ou n est un numéro généré automatiquement.

hash_partitions_by_quantity

Cette clause permet de définir le nombre de partitions ainsi que les tablespaces de stockage.
La clause OVERFLOW permet d'indiquer une liste de tablespaces où seront stockées les zones de débordement d'une IOT.

subpartition_by_list

[subpartition_template]

Permet de définir des sous-partitions définies par une liste de valeurs extraite des colonnes indiquées.

subpartition_template

[list_values_clause] [partitioning_storage_clause]

Cette clause permet d'appliquer un schéma de sous-partitionnement d'une partition.
Ce schéma peut contenir soit un partitionnement par hash, soit un partitionnement par liste.
Il sera appliqué par défaut pour chaque partition pour laquelle les sous-partitions ne seront pas définies explicitement.
Lorsqu'une sous-partition est définie par un modèle, vous devez spécifier un nom pour chacune d'entre-elles.
La seule clause de la section partitioning_storage_clause que vous pouvez spécifier est la clause TABLESPACE.
Si la clause TABLESPACE est spécifiée pour une sous-partition de type LOB, vous devez spécifier la clause TABLESPACE pour chaque sous-partitions de cette colonne LOB.

range_values_clause

Permet de spécifier, pour chaque partition, la plus grande valeur non incluse de la colonne.
Les valeurs données pour chaque partition doivent être ordonnées de la plus petite à la plus grande.
La valeur indiquée peut être MAXVALUE et doit, dans ce cas, être la dernière série.

exemple : partitionnement d'une table contenant des départements :
1ère partition : PARTITION_01_29 VALUES LESS THAN (30) (pour stocker les départements de 01 à 29)
2ème partition : PARTITION_30_59 VALUES LESS THAN (60) (pour stocker les départements de 30 à 59)
3ème partition : PARTITION_60_99 VALUES LESS THAN (MAXVALUE) (pour stocker les départements supérieurs à 59)

table_partition_description

[segment_attributes_clause] [key_compression] [LOB_storage_clause] [varray_col_properties] [partition_level_subpartition]

Cette clause permet de définir les caractéristiques de stockage d'une partition.
LOB_storage_clause permet de définir les caractéristiques de stockage des colonnes LOB de chaque partition
varray_col_properties permet de définir les caractéristiques de stockage pour les colonnes varray de la partition
La clause partition_level_subpartition ne peut être utilisée que sur des partitionnements composites.

partition_level_subpartitions

[subpartition_spec]

Cette clause n'est permise que sur les partitions composites.
Elle écrase les réglages par défaut définis dans la clause subpartition_by_hash (pour les partitions composites de type range-hash) ou dans le modèle (template) de sous-partitionnement (pour les partitions composites de type range-hash ou range-list).

Spécificités pour tous types de partitions composites :
Vous pouvez spécifier un nombre de partitions désiré, auquel cas, Oracle assigne un nom automatiquement à chaque partition.
Vous pouvez spécifier une liste de TABLESPACEs dans lesquelles seront stockées les différentes partitions.
Vous pouvez utiliser la clause subpartition_spec pour nommer explicitement chaque partition.
Si la clause partition_level_subpartition est omise et que vous avez créé un modèle de sous-partitionnement, Oracle utilisera ce modèle.
Avec la clause partition_spec, seule la clause TABLESPACE peut être définie à l'intérieur de la clause partitioning_storage_clause.

Spécificité pour le type de partition composite range-hash
La clause list_values_clause de subpartition_spec est invalide.

Spécificité pour le type de partition composite range-list
La clause hash_subpartition_quantity n'est pas autorisée.
Dans la clause subpartition_spec vous devez spécifier la clause list_values_clause pour chaque sous-partition, et les valeurs indiquées doivent être unique dans toutes les sous-partitions d'une même partition.

subpartition_spec

[list_values_clause] [partitioning_storage_clause]

Spécification des partitions, de leur plages de valeurs et de leur caractéristiques de stockage.

partitioning_storage_clause

Permet de définir le tablespace dans lequel sera stockée la partition ainsi que la zone d'overflow pour une IOT.
Indique également les caractéristiques de stockage des colonnes de type LOB ou de type varray des partitions.

parallel_clause

Permet de régler les paramètres de parallélisme lors d'une création de table, ainsi que lors d'opérations du DML (Insert, Update, Delete, Merge).
NOPARALLEL (défaut) spécifie qu'aucun mécanisme de parallélisme n'est demandé
PARALLEL indique que l'on souhaite activer le mode parallèle avec un degré équivalent au nombre de processeurs disponibles
PARALLEL integer indique que l'on active le parallélisme avec un degré égal à integer (nombre de threads mis en oeuvre dans l'opération)
Le traitement en parallèle est désactivé pour une table contenant un trigger ou une contrainte d'intégrité référentielle.
En présence d'un index bitmap sur la table, le parallélisme est désactivé si la table n'est pas partitionnée.
Si la table contient des colonnes de type LOB ou de type objet, les ordre insert, update ou delete qui modifient ce LOB ou cet objet ne seront pas traités en parallèle.
Un indicateur (hint) parallel surcharge la clause PARALLEL définie au niveau de la table.
Si un ordre DML ou CREATE TABLE ... AS SELECT référence un objet présent dans une autre base, le parallélisme n'est pas activé.

enable_disable_clause

[using_index_clause] [exceptions_clause]

Cette clause permet de définir à la création de la table l'état dans lequel sont créées les contraintes.
ENABLE indique que la contrainte est activée
ENABLE VALIDATE indique que les données pré-existantes dans la table doivent respecter les contraintes
Si une donnée dans la table viole la contrainte, celle-ci reste à l'état DISABLE et Oracle retourne une erreur.
ENABLE NOVALIDATE n'effectue aucun contrôle sur les données pré-existantes, mais seulement sur les données insérées utltérieurement
VALIDATE est la valeur par défaut si aucune n'est spécifiée
Il n'est pas possible d'activer une clé étrangère qui référence une clé primaire ou unique désactivée.
DISABLE permet de désactiver la contrainte
DISABLE VALIDATE désactive la contrainte et supprime son index mais la laisse dans un état valide
DISABLE NOVALIDATE (défaut) désactive la contrainte
Si vous désactivez une contrainte de type primary key ou unique, Oracle supprime l'index associé.

using_index_clause

Cette clause ne s'applique qu'aux contraintes de type PRIMARY KEY ou UNIQUE.
index indique que l'on souhaite utiliser l'index existant index pour valider la contrainte
create_index_statement permet de définir en ligne un ordre de création d'index
SORT indique que l'index devra être trié dans l'ordre croissant
NOSORT indique qu'aucun tri ne sera appliqué à l'index

global_partitioned_index

[index_partitioning_clause]

Permet de définir le partitionnement d'un index.

index_partitioning_clause

[segment_attributes_clause]

Cette clause permet de définir les particularités de chaque partition d'index.

4-2 - Exemples de création de tables

CREATE TABLE employees_demo
( employee_id    NUMBER(6)
, first_name     VARCHAR2(20)
, last_name      VARCHAR2(25)
CONSTRAINT emp_last_name_nn NOT NULL
, email          VARCHAR2(25)
CONSTRAINT emp_email_nn     NOT NULL
, phone_number   VARCHAR2(20)
, hire_date      DATE  DEFAULT SYSDATE
CONSTRAINT emp_hire_date_nn  NOT NULL
, job_id         VARCHAR2(10)
CONSTRAINT     emp_job_nn  NOT NULL
, salary         NUMBER(8,2)
CONSTRAINT     emp_salary_nn  NOT NULL
, commission_pct NUMBER(2,2)
, manager_id     NUMBER(6)
, department_id  NUMBER(4)
, dn             VARCHAR2(300)
, CONSTRAINT     emp_salary_min
CHECK (salary > 0)
, CONSTRAINT     emp_email_uk
UNIQUE (email)
) ;

Indications de stockage

CREATE TABLE employees_demo
( employee_id    NUMBER(6)
, first_name     VARCHAR2(20)
, last_name      VARCHAR2(25)
CONSTRAINT emp_last_name_nn NOT NULL
, email          VARCHAR2(25)
CONSTRAINT emp_email_nn     NOT NULL
, phone_number   VARCHAR2(20)
, hire_date      DATE  DEFAULT SYSDATE
CONSTRAINT emp_hire_date_nn  NOT NULL
, job_id         VARCHAR2(10)
CONSTRAINT     emp_job_nn  NOT NULL
, salary         NUMBER(8,2)
CONSTRAINT     emp_salary_nn  NOT NULL
, commission_pct NUMBER(2,2)
, manager_id     NUMBER(6)
, department_id  NUMBER(4)
, dn             VARCHAR2(300)
, CONSTRAINT     emp_salary_min
CHECK (salary > 0)
, CONSTRAINT     emp_email_uk
UNIQUE (email)
)
TABLESPACE example
STORAGE (INITIAL     6144
NEXT        6144
MINEXTENTS     1
MAXEXTENTS     5 );

Table temporaire avec persistence des données sur toute la session

CREATE GLOBAL TEMPORARY TABLE today_sales
ON COMMIT PRESERVE ROWS
AS SELECT * FROM orders WHERE order_date = SYSDATE;

Table d'objets

CREATE TABLE persons OF person_t;

Table contenant une colonne objet

CREATE TABLE books (title VARCHAR2(100), author person_t);

Mise en oeuvre du parallélisme

CREATE TABLE dept_80
PARALLEL
AS SELECT * FROM employees
WHERE department_id = 80;

Contrainte non activée

CREATE TABLE departments_demo
( department_id    NUMBER(4)   PRIMARY KEY DISABLE
, department_name  VARCHAR2(30)
CONSTRAINT  dept_name_nn  NOT NULL
, manager_id       NUMBER(6)
, location_id      NUMBER(4)
, dn               VARCHAR2(300)
) ;

Table contenant une colonne de type table imbriquée (nested table)

CREATE TABLE print_media
( product_id        NUMBER(6)
, ad_id             NUMBER(6)
, ad_composite      BLOB
, ad_sourcetext     CLOB
, ad_finaltext      CLOB
, ad_fltextn        NCLOB
, ad_textdocs_ntab  textdoc_tab
, ad_photo          BLOB
, ad_graphic        BFILE
, ad_header         adheader_typ
, press_release     LONG
) NESTED TABLE ad_textdocs_ntab STORE AS textdocs_nestedtab;

Table contenant des collections multi-niveaux

CREATE TYPE phone AS OBJECT (telephone NUMBER);
/
CREATE TYPE phone_list AS TABLE OF phone;
/
CREATE TYPE my_customer AS OBJECT (
cust_name VARCHAR2(25),
phones phone_list);
/
CREATE TYPE customer_list AS TABLE OF my_customer;
/
CREATE TABLE business_contacts (
company_name VARCHAR2(25),
company_reps customer_list)
NESTED TABLE company_reps STORE AS outer_ntab
(NESTED TABLE phones STORE AS inner_ntab);

Table contenant des colonnes LOB

CREATE TABLE print_media_new
( product_id        NUMBER(6)
, ad_id             NUMBER(6)
, ad_composite      BLOB
, ad_sourcetext     CLOB
, ad_finaltext      CLOB
, ad_fltextn        NCLOB
, ad_textdocs_ntab  textdoc_tab
, ad_photo          BLOB
, ad_graphic        BFILE
, ad_header         adheader_typ
, press_release     LONG
) NESTED TABLE ad_textdocs_ntab STORE AS textdocs_nestedtab_new
LOB (ad_sourcetext, ad_finaltext) STORE AS
(TABLESPACE example
STORAGE (INITIAL 6144 NEXT 6144)
CHUNK 4000
NOCACHE LOGGING);

Table organisée en index

CREATE TABLE countries
( country_id      CHAR(2)
CONSTRAINT country_id_nn NOT NULL
, country_name    VARCHAR2(40)
, currency_name   VARCHAR2(25)
, currency_symbol VARCHAR2(3)
, region          VARCHAR2(15)
, CONSTRAINT    country_c_id_pk
PRIMARY KEY (country_id )
ORGANIZATION INDEX
INCLUDING   country_name
PCTTHRESHOLD 2
STORAGE
( INITIAL  4K
NEXT  2K
PCTINCREASE 0
MINEXTENTS 1
MAXEXTENTS 1 )
OVERFLOW
STORAGE
( INITIAL  4K
NEXT  2K
PCTINCREASE 0
MINEXTENTS 1
MAXEXTENTS 1 );

Table externe

CREATE TABLE dept_external (
deptno     NUMBER(6),
dname      VARCHAR2(20),
loc        VARCHAR2(25)
)
ORGANIZATION EXTERNAL
(TYPE oracle_loader
DEFAULT DIRECTORY admin
ACCESS PARAMETERS
(
RECORDS DELIMITED BY newline
BADFILE 'ulcase1.bad'
DISCARDFILE 'ulcase1.dis'
LOGFILE 'ulcase1.log'
SKIP 20
FIELDS TERMINATED BY ","  OPTIONALLY ENCLOSED BY '"'
(
deptno     INTEGER EXTERNAL(6),
dname      CHAR(20),
loc        CHAR(25)
)
)
LOCATION ('ulcase1.ctl')
)
REJECT LIMIT UNLIMITED;

Table XML dont les données sont stockées dans une colonne LOB

CREATE TABLE xwarehouses OF XMLTYPE;

Table XML utilisant un schéma enregistré

CREATE TABLE xwarehouses OF XMLTYPE
XMLSCHEMA "http://www.oracle.com/xwarehouses.xsd"
ELEMENT "Warehouse";

Table XML dont les données sont stockées dans un objet relationnel

CREATE TABLE xwarehouses (
warehouse_id    NUMBER,
warehouse_spec  XMLTYPE)
XMLTYPE warehouse_spec STORE AS OBJECT RELATIONAL
XMLSCHEMA "http://www.oracle.com/xwarehouses.xsd"
ELEMENT "Warehouse";

Table partitionnée par tranches

CREATE TABLE range_sales
( prod_id        NUMBER(6)
, cust_id        NUMBER
, time_id        DATE
, channel_id     CHAR(1)
, promo_id       NUMBER(6)
, quantity_sold  NUMBER(3)
, amount_sold         NUMBER(10,2)
)
PARTITION BY RANGE (time_id)
(PARTITION SALES_Q1_1998 VALUES LESS THAN (TO_DATE('01-APR-1998','DD-MON-YYYY')),
PARTITION SALES_Q2_1998 VALUES LESS THAN (TO_DATE('01-JUL-1998','DD-MON-YYYY')),
PARTITION SALES_Q3_1998 VALUES LESS THAN (TO_DATE('01-OCT-1998','DD-MON-YYYY')),
PARTITION SALES_Q4_1998 VALUES LESS THAN (TO_DATE('01-JAN-1999','DD-MON-YYYY')),
PARTITION SALES_Q1_1999 VALUES LESS THAN (TO_DATE('01-APR-1999','DD-MON-YYYY')),
PARTITION SALES_Q2_1999 VALUES LESS THAN (TO_DATE('01-JUL-1999','DD-MON-YYYY')),
PARTITION SALES_Q3_1999 VALUES LESS THAN (TO_DATE('01-OCT-1999','DD-MON-YYYY')),
PARTITION SALES_Q4_1999 VALUES LESS THAN (TO_DATE('01-JAN-2000','DD-MON-YYYY')),
PARTITION SALES_Q1_2000 VALUES LESS THAN (TO_DATE('01-APR-2000','DD-MON-YYYY')),
PARTITION SALES_Q2_2000 VALUES LESS THAN (TO_DATE('01-JUL-2000','DD-MON-YYYY')),
PARTITION SALES_Q3_2000 VALUES LESS THAN (TO_DATE('01-OCT-2000','DD-MON-YYYY')),
PARTITION SALES_Q4_2000 VALUES LESS THAN (MAXVALUE))
;

Table partitionnée par listes

CREATE TABLE list_customers
( customer_id             NUMBER(6)
, cust_first_name         VARCHAR2(20)
, cust_last_name          VARCHAR2(20)
, cust_address            CUST_ADDRESS_TYP
, nls_territory           VARCHAR2(30)
, cust_email              VARCHAR2(30))
PARTITION BY LIST (nls_territory) (
PARTITION asia VALUES ('CHINA', 'THAILAND'),
PARTITION europe VALUES ('GERMANY', 'ITALY', 'SWITZERLAND'),
PARTITION west VALUES ('AMERICA'),
PARTITION east VALUES ('INDIA'),
PARTITION rest VALUES (DEFAULT));

Table partitionnée par hash

CREATE TABLE hash_products
( product_id          NUMBER(6)
, product_name        VARCHAR2(50)
, product_description VARCHAR2(2000)
, category_id         NUMBER(2)
, weight_class        NUMBER(1)
, warranty_period     INTERVAL YEAR TO MONTH
, supplier_id         NUMBER(6)
, product_status      VARCHAR2(20)
, list_price          NUMBER(8,2)
, min_price           NUMBER(8,2)
, catalog_url         VARCHAR2(50)
, CONSTRAINT          product_status_lov
CHECK (product_status in ('orderable'
,'planned'
,'under development'
,'obsolete')
) )
PARTITION BY HASH (product_id)
PARTITIONS 6
STORE IN (tbs_1, tbs_2, tbs_3, tbs_4);

Table partitionnée contenant des colonnes LOB

CREATE TABLE print_media_demo
( product_id NUMBER(6)
, ad_id NUMBER(6)
, ad_composite BLOB
, ad_sourcetext CLOB
, ad_finaltext CLOB
, ad_fltextn NCLOB
, ad_textdocs_ntab textdoc_tab
, ad_photo BLOB
, ad_graphic BFILE
, ad_header adheader_typ
) NESTED TABLE ad_textdocs_ntab STORE AS textdocs_nestedtab_demo
LOB (ad_composite, ad_photo, ad_finaltext)
STORE AS(STORAGE (NEXT 20M))
PARTITION BY RANGE (product_id)
(PARTITION p1 VALUES LESS THAN (3000) TABLESPACE tbs_1
LOB (ad_composite, ad_photo)
STORE AS (TABLESPACE tbs_2 STORAGE (INITIAL 10M)),
PARTITION P2 VALUES LESS THAN (MAXVALUE)
LOB (ad_composite, ad_finaltext)
STORE AS (TABLESPACE tbs_3)
)
TABLESPACE tbs_4;

Table à partitions composites

CREATE TABLE composite_sales
( prod_id        NUMBER(6)
, cust_id        NUMBER
, time_id        DATE
, channel_id     CHAR(1)
, promo_id       NUMBER(6)
, quantity_sold  NUMBER(3)
, amount_sold         NUMBER(10,2)
)
PARTITION BY RANGE (time_id)
SUBPARTITION BY HASH (channel_id)
(PARTITION SALES_Q1_1998 VALUES LESS THAN (TO_DATE('01-APR-1998','DD-MON-YYYY')),
PARTITION SALES_Q2_1998 VALUES LESS THAN (TO_DATE('01-JUL-1998','DD-MON-YYYY')),
PARTITION SALES_Q3_1998 VALUES LESS THAN (TO_DATE('01-OCT-1998','DD-MON-YYYY')),
PARTITION SALES_Q4_1998 VALUES LESS THAN (TO_DATE('01-JAN-1999','DD-MON-YYYY')),
PARTITION SALES_Q1_1999 VALUES LESS THAN (TO_DATE('01-APR-1999','DD-MON-YYYY')),
PARTITION SALES_Q2_1999 VALUES LESS THAN (TO_DATE('01-JUL-1999','DD-MON-YYYY')),
PARTITION SALES_Q3_1999 VALUES LESS THAN (TO_DATE('01-OCT-1999','DD-MON-YYYY')),
PARTITION SALES_Q4_1999 VALUES LESS THAN (TO_DATE('01-JAN-2000','DD-MON-YYYY')),
PARTITION SALES_Q1_2000 VALUES LESS THAN (TO_DATE('01-APR-2000','DD-MON-YYYY')),
PARTITION SALES_Q2_2000 VALUES LESS THAN (TO_DATE('01-JUL-2000','DD-MON-YYYY'))
SUBPARTITIONS 8,
PARTITION SALES_Q3_2000 VALUES LESS THAN (TO_DATE('01-OCT-2000','DD-MON-YYYY'))
(SUBPARTITION ch_c,
SUBPARTITION ch_i,
SUBPARTITION ch_p,
SUBPARTITION ch_s,
SUBPARTITION ch_t),
PARTITION SALES_Q4_2000 VALUES LESS THAN (MAXVALUE)
SUBPARTITIONS 4)
;

Partitionnement composite avec utilisation d'un modèle

CREATE TABLE customers_part (
customer_id        NUMBER(6),
cust_first_name    VARCHAR2(20),
cust_last_name     VARCHAR2(20),
nls_territory      VARCHAR2(30),
credit_limit       NUMBER(9,2))
PARTITION BY RANGE (credit_limit)
SUBPARTITION BY LIST (nls_territory)
SUBPARTITION TEMPLATE
(SUBPARTITION east  VALUES
('CHINA', 'JAPAN', 'INDIA', 'THAILAND'),
SUBPARTITION west VALUES
('AMERICA', 'GERMANY', 'ITALY', 'SWITZERLAND'),
SUBPARTITION other VALUES (DEFAULT))
(PARTITION p1 VALUES LESS THAN (1000),
PARTITION p2 VALUES LESS THAN (2500),
PARTITION p3 VALUES LESS THAN (MAXVALUE));

Table contenant une colonne objet référencée

CREATE TABLE employees_obj
( e_name   VARCHAR2(100),
e_number NUMBER,
e_dept   REF department_typ SCOPE IS departments_obj_t );

Table contenant une colonne objet référencée avec contrainte d'intégrité

CREATE TABLE employees_obj
( e_name   VARCHAR2(100),
e_number NUMBER,
e_dept   REF department_typ REFERENCES departments_obj_t);

Table d'objets avec OID basé sur la clé primaire

CREATE TYPE employees_typ AS OBJECT
(e_no NUMBER, e_address CHAR(30));

CREATE TABLE employees_obj_t OF employees_typ (e_no PRIMARY KEY)
OBJECT IDENTIFIER IS PRIMARY KEY;

Table d'objets munis de contraintes

CREATE TYPE address_t AS OBJECT
( hno    NUMBER,
street VARCHAR2(40),
city   VARCHAR2(20),
zip    VARCHAR2(5),
phone  VARCHAR2(10) );

CREATE TYPE person AS OBJECT
( name        VARCHAR2(40),
dateofbirth DATE,
homeaddress address,
manager     REF person );

CREATE TABLE persons OF person
( homeaddress NOT NULL,
UNIQUE (homeaddress.phone),
CHECK (homeaddress.zip IS NOT NULL),
CHECK (homeaddress.city <> 'San Francisco') );

Contrainte en ligne

CREATE TABLE locations_demo
( location_id    NUMBER(4) CONSTRAINT loc_id_pk PRIMARY KEY
, street_address VARCHAR2(40)
, postal_code    VARCHAR2(12)
, city       VARCHAR2(30)
, state_province VARCHAR2(25)
, country_id     CHAR(2)
) ;

Contrainte hors ligne

CREATE TABLE locations_demo
( location_id    NUMBER(4)
, street_address VARCHAR2(40)
, postal_code    VARCHAR2(12)
, city       VARCHAR2(30)
, state_province VARCHAR2(25)
, country_id     CHAR(2)
, CONSTRAINT loc_id_pk PRIMARY KEY (location_id));

Contrainte de clé étrangère en ligne

CREATE TABLE dept_20
(employee_id     NUMBER(4),
last_name       VARCHAR2(10),
job_id          VARCHAR2(9),
manager_id      NUMBER(4),
hire_date       DATE,
salary          NUMBER(7,2),
commission_pct  NUMBER(7,2),
department_id   CONSTRAINT fk_deptno
REFERENCES departments(department_id) );

Contrainte de clé étrangère hors ligne

CREATE TABLE dept_20
(employee_id     NUMBER(4),
last_name       VARCHAR2(10),
job_id          VARCHAR2(9),
manager_id      NUMBER(4),
hire_date       DATE,
salary          NUMBER(7,2),
commission_pct  NUMBER(7,2),
department_id,
CONSTRAINT fk_deptno
FOREIGN  KEY (department_id)
REFERENCES  departments(department_id) );

Clé étrangère avec clause ON DELETE

CREATE TABLE dept_20
(employee_id     NUMBER(4) PRIMARY KEY,
last_name       VARCHAR2(10),
job_id          VARCHAR2(9),
manager_id      NUMBER(4) CONSTRAINT fk_mgr
REFERENCES employees ON DELETE SET NULL,
hire_date       DATE,
salary          NUMBER(7,2),
commission_pct  NUMBER(7,2),
department_id   NUMBER(2)   CONSTRAINT fk_deptno
REFERENCES departments(department_id)
ON DELETE CASCADE );

Contraintes de contrôle

CREATE TABLE divisions
(div_no    NUMBER  CONSTRAINT check_divno
CHECK (div_no BETWEEN 10 AND 99)
DISABLE,
div_name  VARCHAR2(9)  CONSTRAINT check_divname
CHECK (div_name = UPPER(div_name))
DISABLE,
office    VARCHAR2(10)  CONSTRAINT check_office
CHECK (office IN ('DALLAS','BOSTON',
'PARIS','TOKYO'))
DISABLE);

Contrainte de contrôle multi-colonnes

CREATE TYPE person_name AS OBJECT
(first_name VARCHAR2(30), last_name VARCHAR2(30));
/

CREATE TABLE students (name person_name, age INTEGER,
CHECK (name.first_name IS NOT NULL AND
name.last_name IS NOT NULL));

Contraintes indexées

CREATE TABLE promotions_var3
( promo_id         NUMBER(6)
, promo_name       VARCHAR2(20)
, promo_category   VARCHAR2(15)
, promo_cost       NUMBER(10,2)
, promo_begin_date DATE
, promo_end_date   DATE
, CONSTRAINT promo_id_u UNIQUE (promo_id, promo_cost)
USING INDEX (CREATE UNIQUE INDEX promo_ix1
ON promotions_var3 (promo_id, promo_cost))
, CONSTRAINT promo_id_u2 UNIQUE (promo_cost, promo_id)
USING INDEX promo_ix1);

Création d'une table dans un cluster

CREATE TABLE dept_10
CLUSTER personnel (department_id)
AS SELECT * FROM employees WHERE department_id = 10;

5 - Syntaxe de l'instruction ALTER TABLE

5-1 - Syntaxe 9i, 10g

Est présentée dans ce chapitre la syntaxe de l'instruction ALTER TABLE des versions Oracle 9i et 10g.

Pour obtenir des informations sur les versions antérieures, consultez la documentation officielle,
notamment la syntaxe ALTER TABLE de la version Oracle 8i.

ALTER TABLE

[column_clauses] [constraint_clauses] [alter_table_partitioning] [move_table_clause] [alter_enable_disable_clause]

alter_table_properties

[physical_attributes_clause] [logging_clause] [data_segment_compression] [allocate_extent_clause] [deallocate_unused_clause] [upgrade_table_clause] [records_per_block_clause] [parallel_clause] [row_movement_clause]

physical_attributes_clause

[storage_clause]

PCTUSED indique (en pourcentage) le niveau en deçà duquel le bloc sera disponible (affichage en début de freelist) pour de nouvelles insertions
PCTFREE indique (en pourcentage) l'espace que l'on souhaite conserver dans le bloc pour les mises à jour
INITRANS defini le nombre initial de transactions allouées à chaque bloc
MAXTRANS indique combien de transactions (maxi : 255) peuvent réaliser simultanément des changement dans un bloc

supplemental_log_grp_clause

Cette clause permet d'ajouter ou de supprimer des groupes supplémentaires de redo log.
ADD SUPPLEMENTAL LOG GROUP permet d'ajouter des groupes de redo log
DROP SUPPLEMENTAL LOG GROUP permet de supprimer des groupes de redo log

allocate_extent_clause

Cette clause permet d'allouer un nouvel extent sur une table, une partition ou sous-partition, la zone de débordement d'une IOT ou un index LOB.
Il n'est pas possible d'allouer un nouvel extent pour une table temporaire ou pour une partition par tranche (range) ou une partition composite.
L'utilisation de cette clause ne change pas les valeurs actuelles de NEXT et PCTINCREASE et n'affecte donc pas la taille de l'extent suivant alloué par Oracle.
SIZE spécifie la taille du nouvel extent en octets (suffixer la valeur avec K pour l'exprimer en Ko ou M pour l'exprimer en Mo)
DATAFILE 'filename' indique le datafile ou l'on souhaite créer l'extent
INSTANCE integer (valide uniquement avec Real Application Clusters) rend l'extent disponible dans le groupe de freelist associé à l'instance spécifiée

deallocate_unused_clause

Cette clause permet de désallouer l'espace inutilisé en fin de table, de partition ou sous-partition, de zone de débordement d'une IOT ou d'un index LOB.
KEEP spécifie le nombre d'octets au dela du high water mark que le segment doit avoir après la désallocation
Après cette opération, Oracle positionne la valeur du paramètre NEXT à la taille du dernier extent qui a été désalloué.

upgrade_table_clause

[column_properties]

Cette clause applicable sur les tables objet ou sur les colonnes objet des tables standard, permet de convertir les données conformément à la nouvelle version du type de référence.
INCLUDING DATA demande la conversion des données en conformité avec la nouvelle version du type
Il est possible de spécifier les informations de stockage via les clauses column_properties et LOB_partition_storage.
Pour obtenir les informations sur les tables dont les données sont basées sur une ancienne version du type, interrogez la colonne DATA_UPGRADED de la vue USER_TAB_COLUMNS.
NOT INCLUDING DATA demande que le contenu de la colonne reste inchangé

records_per_block_clause

Cette clause permet de définir comment Oracle restreint le nombre d'enregistrements que peut contenir un bloc.
Cela permet de s'assurer que tout nouvel index bitmap créé sur la table sera le plus compressé possible.
MINIMIZE demande à Oracle de calculer le plus grand nombre d'enregistrements dans tous les blocs et limiter les futures insertions de telle manière qu'aucun bloc ne puisse contenir plus d'enregistrements
Oracle recommande de disposer d'un échantillon représentatif de données dans la table avant de spécifier MINIMIZE.
MINIMIZE ne peut pas être employé sur une table vide.
NOMINIMIZE (défaut) désactive la fonctionnalité

row_movement_clause

Cette clause permet d'indiquer si les lignes d'une table peuvent être déplacées lors d'une compression de segment ou d'une mise à jour de données partitionnées.
ENABLE indique que l'on autorise le déplacement des lignes
DISABLE indique que l'on interdit le déplacement des lignes
Si vous stockez les ROWID d'une table dans votre application, interdisez le déplacement des lignes avec la clause DISABLE.
Vous ne pouvez pas spécifier cette clause pour une IOT non partitionnée.

alter_iot_clause

[index_org_table_clause] [alter_overflow_clause]

Cette clause permet de modifier la définition d'une table organisée en index.
COALESCE demande à Oracle de combiner les blocs d'index de la clé primaire lorsque c'est possible pour libérer des blocs (peut être spécifié avec la clause PARALLEL)

index_org_table_clause

[mapping_table_clause] [key_compression] [index_org_overflow_clause]

Cette clause permet de modifier la définition d'une table organisée en index.
PCTTHRESHOLD integer représente le pourcentage d'espace d'un bloc d'index réservé au stockage d'une ligne
Il doit être suffisant pour stocker la clé primaire.
Toutes les colonnes qui ne rentrent pas dans cet espace sont placées dans une zone de débordement.
integer doit être compris entre 1 et 50 (défaut)
PCTTHRESHOLD ne peut pas être utilisé sur une partition individuelle d'une IOT.

mapping_table_clause

Utilisez cette clause pour stocker les ROWID dans une table relationnelle. cette table sera nécessaire pour créer un index bitmap sur l'IOT.

key_compression

Cette clause permet d'activer la compression de clé sur l'IOT.
COMPRESS active la compression de la clé primaire
COMPRESS integer active la compression avec les integer premières colonnes de la clé primaire
NOCOMPRESS (défaut) interdit la compression de la clé primaire
Au niveau partition, la clause COMPRESS n'accepte pas le paramètre integer.

index_org_overflow_clause

[segment_attributes_clause]

Cette clause permet de définir la zone de débordement d'une IOT dans laquelle seront placées les colonnes qui dépassent la valeur de PCTTHRESHOLD.
Lors de la création de l'IOT, Oracle évalue la taille maximum d'une ligne. si celle-ci ne tient pas dans le pourcentage PCTTHRESHOLD et que vous n'avez pas défini de zone d'overflow, Oracle génère une erreur.
Tous les attributs physiques et les clauses de stockage définis après le mot clé OVERFLOW s'appliquent donc aux colonnes placées dans la zone de débordement.
Si la table contient des colonnes LOB, celles-ci seront stockées en dehors de la table, même si la clause OVERFLOW est spécifiée.
INCLUDING column_name indique à partir de quelle colonne les données seront stockées dans la zone de débordement

segment_attributes_clause

[physical_attributes_clause] [logging_clause]

tablespace défini le nom du tablespace dans lequel sera créée la table. si celui ci est homis, la table sera créée dans le tablespace par défaut de l'utilisateur

alter_overflow_clause

[allocate_extent_clause] [deallocate_unused_clause]

Cette clause permet de modifier les caractéristiques d' une zone de débordement sur une IOT.
INCLUDING column_name indique à partir de quelle colonne les données seront stockées dans la zone de débordement
INCLUDING ne peut pas être spécifié sur des partitions individuelles.

add_overflow_clause

[segment_attributes_clause]

Cette clause permet d'ajouter une zone de débordement sur une IOT.
Elle peut être également utilisée pour créer explicitement un nouvel extent ou désallouer l'espace inutilisé d'un segment d'overflow.
Si PARTITION n'est pas spécifié, Oracle alloue automatiquement une zone de débordement pour chaque partition.
Si vous voulez spécifier les attributs physiques pour une ou plusieurs partitions, vous devez spécifier chaque attributs de chaque partition. il n'est pas nécessaire d'indiquer le nom des partitions, mais elles doivent être spécifiées dans l'ordre où elles ont été créées.
(Vous pouvez retrouver l'ordre des partitions en interrogeant les colonnes PARTITION_NAME et PARTITION_POSITION de la vue USER_IND_PARTITIONS)
Tous les attributs physiques et les clauses de stockage définis après le mot clé OVERFLOW s'appliquent donc aux colonnes placées dans la zone de débordement.
Si la table contient des colonnes LOB, celles-ci seront stockées en dehors de la table, même si la clause OVERFLOW est spécifiée.

alter_mapping_table_clause

[allocate_extent_clause] [deallocate_unused_clause]

Cette clause n'est valide que pour une IOT disposant d'une table de mapping.
UPDATE BLOCK REFERENCES permet de mettre à jour les références dans la table de mapping
allocate_extent_clause permet d'allouer un nouvel extent en fin de table de mapping
deallocate_unused_clause permet de désallouer l'espace inutilisé en fin de table de mapping

column_clauses

[add_column_clause] [drop_column_clause] [rename_column_clause] [modify_collection_retrieval] [modify_LOB_storage_clause] [alter_varray_col_properties]

add_column_clause permet d'ajouter des colonnes à la table
modify_column_clauses permet de modifier les caractéristiques d'une colonne
drop_column_clauses permet de supprimer des colonnes
rename_column_clauses permet de renommer une colonne
modify_collection_retrieval permet de modifier le type de retour d'une collection
modify_LOB_storage_clause permet de modifier les attributs physique d'une colonne de type LOB
alter_varray_col_properties permet de modifier les attributs physique d'une colonne de type LOB dans laquelle est stocké un VARRAY

add_column_clause

[column_properties]

Lors de l'ajout d'une colonne, la valeur initiale pour chaque ligne est NULL à moins que vous n'ayez spécifié la clause DEFAULT, auquel cas chaque ligne de la table est mise à jour avec cette valeur. Cette opération déclenche tous triggers de type AFTER UPDATE définis sur la table.
Si une valeur par défaut est définie sur la colonne, vous pouvez utiliser la clause DEFAULT pour remettre une valeur NULL par défaut.
Vous pouvez ajouter un segment d'overflow sur chaque partition d'une IOT.
Vous pouvez ajouter des colonnes de type LOB et spécifier les caractéristiques de stockage.
Si une vue créée avec une requête de type "SELECT*" existe avant l'ajout de la colonne, Oracle ne rajoute pas les nouvelles colonnes à la description de cette vue. Il faudra donc la recréer.

Restrictions liées à l'ajout de colonnes

- Vous ne pouvez pas ajouter une colonne de type LOB sur une table en cluster

- Si vous ajoutez une colonne de type LOB sur une table partitionnée par hash, le seul attribut qu'il est possible de modifier pour la nouvelle partition est TABLESPACE

- Il n'est pas possible d'ajouter une colonne munie d'une contrainte NOT NULL si la table posséde déjà des lignes, sauf si la clause DEFAULT est définie

inline_constraint permet de définir une contrainte de niveau colonne
inline_ref_constraint permet de définir une contrainte de type REF

modify_column_clause

[modify_col_properties] [modify_col_substitutable]

Permet de modifier la définition d'une colonne existante ou l'aspect substituable d'une colonne objet.

Le type de la colonne peut être modifié si toutes les lignes ont une valeur NULL, où si le nouveau type défini ne remet pas en cause les données existantes dans la colonne.
Vous pouvez modifier une colonne de type DATE en TIMESTAMP ou TIMESTAMP WITH LOCAL TIME ZONE.
Vous pouvez modifier une colonne de type TIMESTAMP WITH LOCAL TIME ZONE en DATE (mais les fractions de seconde et le décalage horaire sont perdus).
Vous pouvez modifier une colonne de type LONG en CLOB ou NCLOB et une colonne de type LONG RAW en BLOB.
Le seul type de contrainte applicable avec la clause MODIFY est NOT NULL, si aucune ligne de la table ne contient de NULL.
Pour définir de nouvelle contraintes (UNIQUE, PRIMARY KEY, REFERENCES, CHECK)sur une colonne existante, utilisez la clause add_column_clause.
Pour modifier une contrainte existante sur la colonne, utilisez la clause constraint_clauses.

Restrictions liées à la modification de colonnes

- Vous ne pouvez pas modifier une colonne si un index de domaine y est défini. Vous devez préalablement supprimer cet index

- Vous ne pouvez pas définir une colonne de type ROWID sur une IOT (par contre vous pouvez la définir de type UROWID)

- Vous ne pouvez pas modifier le type d'une colonne REF

modify_col_properties

Modification des propriétés d'une colonne.

modify_col_substitutable

Modification de l'aspect substituable d'une colonne objet.

FORCE supprime toutes les colonnes cachées contenant l'information de type de donnée d'un sous-type. Cette clause doit être spécifiée si l'un des attibuts du type est NOT FINAL

Restrictions liées à la modification de l'aspect substituable

- Vous ne pouvez cette clause qu'une fois dans l'ordre ALTER TABLE

- Vous ne pouvez pas spécifier cette clause sur une colonne d'une table objet si la subsitualité à été positionnée au niveau de la table

- Vous ne pouvez pas spécifier cette clause sur une colonne créée ou ajoutée avec la clause IS OF TYPE

- Vous ne pouvez pas spécifier NOT SUBSTITUTABLE sur une colonne de type VARRAY si l'un des attributs du type est NOT FINAL

drop_column_clause

Permet de supprimer une ou plusieurs colonnes ou de la (les) définir comme inutilisée(s).

SET UNUSED permet de "marquer" les colonnes comme n'étant plus utilisées. les données ne sont pas encore supprimées, pourtant cette colonne n'est plus affichée et n'est plus interrogeable
DROP permet de supprimer physiquement une ou plusieurs colonne (ainsi que tous indexes et contraintes liés à cette colonne)
DROP UNUSED COLUMNS supprime physiquement les colonne marquées avec la clause SET UNUSED
CASCADE CONSTRAINTS supprime les contraintes d'intégrité référentielles relatives à la colonne supprimée
INVALIDATE spécifie qu'un objet invalidé par la suppression de la colonne sera automatiquement re-validé à sa prochaine invocation
CHECKPOINT integer permet de forcer un checkpoint après chaque integer suppression de données. Si integer n'est pas spécifié, sa valeur défaut est 512
DROP COLUMNS CONTINUE permet de reprendre le processus de suppression après une erreur

Restrictions liées à la suppression de colonne

- Vous ne pouvez utiliser cette clause qu'une fois dans l'ordre ALTER TABLE

- Vous ne pouvez pas supprimer une colonne d'une IOT participant à la clé primaire

- Vous ne pouvez pas supprimer une colonne sur laquelle est défini un index de domaine

- Vous ne pouvez pas spécifier cette clause pour supprimer une pseudo-colonne, colonne de cluster ou une colonne partitionnée, une colonne d'une table imbriquée, d'une table objet ou d'une table du schéma SYS

rename_column_clause

Permet de renommer une colonne d'une table (le nouveau nom ne doit pas exister dans la table).

Les indexes de fonction et les contraintes de type CHECK restent valides.
Les vues, triggers, indexes de domaine, fonctions, procédures et paquetages qui référençaient la colonne deviennent invalides.

Restrictions liées au renommage de colonne

- Cette clause ne peut pas être associée à une autre clause column_clause dans la même instruction

- Vous ne pouvez pas renommer une colonne participant à un index, sans avoir détruit préalablement cet index

modify_collection_retrieval

Permet de spécifier le type de donnée retourné lors de l'interrogation d'une collection.

RETURN AS LOCATOR retourne un pointeur sur la collection
RETURN AS VALUE retourne une copie de la collection

constraint_clauses

Permet d'ajouter, modifier ou supprimer une contrainte.

ADD permet d'ajouter une contrainte de type hors-ligne
MODIFY permet de modifier une contrainte
RENAME CONSTRAINT permet de renommer une contrainte
DROP permet de supprimer une contrainte

drop_constraint_clause

PRIMARY KEY supprime cette contrainte de la table
UNIQUE supprime la contrainte d'unicité des colonnes spécifiées
CONSTRAINT supprime toute autre contrainte définie sur la table
CASCADE demande la suppression en cascade de toutes les contraintes d'intégrité dépendant de la contrainte supprimée
KEEP INDEX conserve l'index lié à la contrainte
DROP INDEX supprime l'index lié à la contrainte

Restrictions liées à la suppression de contraintes

- Vous ne pouvez pas supprimer une contrainte de type PRIMARY KEY ou UNIQUE faisant partie d'une contrainte d'intégrité référentielle (foreign key) sans supprimer également la clé étrangère

- Vous ne pouvez pas supprimer une contrainte de type PRIMARY KEY (même avec la clause CASCADE) sur une table objet dont l'OID est basé sur la clé primaire

- Si vous supprimez une contrainte d'intégrité référentielle portant sur une colonne de type REF, la colonne continue de pointer vers sa table de référence

- Vous ne pouvez pas supprimer la clause SCOPE d'une colonne

column_properties

[object_type_col_properties] [nested_table_col_properties] [varray_col_properties] [LOB_storage_clause] [XMLType_column_properties] [LOB_partition_storage]

Cette clause permet de modifier les caractéristiques de stockage d'un objet, une table imbriquée, un varray ou un LOB.

object_type_col_properties

[substitutable_column_clause]

Pour modifier les informations de stockage d'une nouvelle colonne objet ou un élément d'une collection.
Cette clause est valide uniquement pour l'ajout d'une colonne ou d'un attribut d'objet. Pour modifier les propriétés d'une colonne objet, utilisez la clause modify_column_clauses.

substitutable_column_clause

Cette clause permet d'indiquer dans quelle emsure une colonne objet d'un type peut accueillir des objets d'un sous-type.

ELEMENT indique qu'une collection ou un attribut seront contraints au sous-type indiqué
IS OF indique que la colonne objet sera contrainte au sous-type indiqué
NOT SUBSTITUTABLE AT ALL LEVELS indique qu'aucun sous-type ne pourra être substitué au type

nested_table_col_properties

[substitutable_column_clause] [physical_properties] [column_properties] [object_properties]

Cette clause permet de modifier les caractéristiques de stockage d'une table imbriquée.

nested_item désigne le nom de la colonne contenant la table imbriquée
COLUMN_VALUE permet de spécifier la sous-table imbriquée dans le cas de collections multi-niveaux (ou seule la collection du premier niveau est nommée)
STORE AS désigne le nom de la table dans laquelle seront stockées les lignes de la collection

varray_col_properties

[substitutable_column_clause] [LOB_parameters]

Cette clause permet de modifier les caractéristiques de stockage du LOB dans lequel sera stocké le varray.

La clause TABLESPACE ne peut pas être définie dans la description du LOB. Ce dernier sera stocké dans le même tablespace que celui de la table mère.

LOB_storage_clause

Cette clause permet de spécifier les caractéristiques de stockage d'une nouvelle colonne LOB.

Pour modifier les caractéritiques d'une colonne LOB existante, utilisez la clause modify_LOB_storage_clause.

LOB_parameters

[storage_clause]

tablespace est le nom du tablespace dans lequel sera stocké le LOB
ENABLED STORAGE IN ROW permet de stocker le contenu du LOB dans une colonne de type RAW de la table (si sa taille est inférieure à 4000 octets - les infos de contrôle)
Cette clause ne peut pas être utilisée pour une table organisée en index à moins que la partie OVERFLOW ait été spécifiée.
DISABLE STORAGE IN ROW indique que le LOB sera stocké en dehors de la table
La clause STORAGE IN RAW ne peut plus être modifiée, sauf si la table est déplacée (move).
CHUNK integer spécifie le nombre d'octets a allouer pour la manipulation du LOB
La valeur maximale pour integer est 32768 (taille de bloc maximale possible sous Oracle).
Si integer n'est pas un multiple du data block size, il est arrondi au data block size supérieur.
(Si le data block size actuel est de 2048 et vous spécifiez 3000, alors integer est arrondi à 4096.)
Une fois définie, cette valeur ne plus être modifiée.
Elle ne peut pas non plus excéder la valeur du paramètre NEXT défini dans la clause de stockage.
PCTVERSION integer indique le pourcentage maximum (défaut 10) d'espace utilisé pour maintenir les anciennes versions du LOB
En clair, une ancienne version du LOB ne sera pas écrasée tant qu'elle ne consommera que integer% de l'espace.
Cette clause peut être spécifiée si la base tourne en manual ou automatic undo mode.
PCTVERSION est par défaut en manual undo mode et RETENTION est par défaut en automatic undo mode
RETENTION indique que l'on souhaite conserver les anciennes versions du LOB. Oracle utilise la valeur du paramètre UNDO_RETENTION pour déterminer la valeur (en temps) des données non enregistrées à conserver dans la base
Vous ne pouvez pas spécifier PCTVERSION et RETENTION en même temps.
FREEPOOLS integer indique le nombre de groupes de freelist pour le segment LOB. Normalement integer doit correspondre au nombre d'instances en environnement Real Application Clusters et à 1 pour une base constituée d'une seule instance
Cette clause ne peut être spécifiée que si la base tourne en automatic undo mode.
Vous ne pouvez pas spécifier à la fois FREEPOOLS et le paramètre FREELIST GROUPS de la clause de stockage.
CACHE indique que vous souhaitez conserver les blocs lus en mémoire
NOCACHE spécifie que vous ne souhaitez pas conserver les blocs lus en mémoire
CACHE READS indique que seuls les blocs lus sont placés en cache (et non les blocs écrits)

modify_LOB_storage_clause

Cette clause permet de mofifier les attributs physiques d'une colonne de type LOB.

Vous ne pouvez modifier qu'une seule colonne LOB à la fois avec cette clause.

modify_LOB_parameters

[logging_clause] [allocate_extent_clause] [deallocate_unused_clause]

REBUILD FREEPOOLS
permet de supprimer les anciennes données contenues dans le LOB (dans le cas où vous avez modifié la clause PCTVERSION du LOB)
Vous devez appliquer cette clause si vous downgradez vers une version antérieure à 9.2.0

Restrictions sur la modification des attributs d'une colonne LOB

- Vous ne pouvez pas modifier la valeur du paramètre INITIAL avec la clause storage_close

- Vous ne pouvez pas spécifier à la fois les clauses allocate_extent_clause et deallocate_unused_clause dans une même instruction

alter_varray_col_properties

[modify_LOB_parameters]

Permet de modifier les attributs de stockage d'une colonne LOB dans laquelle est stocké un varray.

Vous ne pouvez pas spécifier le paramètre TABLESPACE dans la clausse LOB_parameters. Ce dernier étant le même que celui de la table principale.

LOB_partition_storage

[LOB_storage_clause] [varray_col_properties] Cette clause permet de définir les informations de stockage des LOB pour chaque partition.

Les partitions doivent être spécifiées dans l'ordre de leur position.
Si vous ne spécifiez pas les clauses LOB_storage_clause ou varray_col_properties pour une partition particulière, les caractéristiques de stockage du LOB seront celles spécifiées au niveau de la table.

Vous ne pouvez spécifier qu'une seule liste de clause LOB_partition_storage dans une même instruction ALTER TABLE et toutes les clauses LOB_storage_clauses et varray_col_properties doivent précéder la liste des clauses LOB_partition_storage.

XMLType_column_properties

Permet de définir les informations de stockage d'une colonne de type XML.

XMLType_storage

Une colonne de type XML peut être stockée dans un LOB ou dan un objet. STORE AS OBJECT RELATIONAL indique de stocker le type XML dans une colonne objet (vous devez alors également spécifier la clause XMLSchema_spec)
STORE AS CLOB indique que la donnée au format XML sera stockée dans un CLOB (dans ce cas vous spécifierez la clause LOB_parameters ou la clause XMLSchema_spec mais pas les deux)

XMLSchema_spec

Permet de spécifier l'url d'un schéma XML enregistré (via le package DBMS_XMLSCHEMA) et (obligatoire) le nom d'un élément XML.

alter_external_table_clause

[add_column_clause] [drop_column_clause] [parallel_clause] [external_data_properties]

Cette clause permet de modifier les caractéristiques d'une table externe.

Restrictions sur la modification des tables externes

- Il n'est pas possible de modifier la définition d'une table externe en dehors de cette clause

- Il n'est pas possible d'ajouter une colonne de type LONG, LOB ou objet et également de modifier le type d'une colonne existante avec l'un de ces trois types

- Il n'est pas possible de définir une contrainte sur une table externe

- Il n'est pas possible de modifier les paramètre de stockage d'une table externe

REJECT LIMIT indique le nombre d'erreurs de conversion permises lors de la lecture de la table externe avant d'annuler le processus (par défaut 0)

external_data_properties

directory spécifie le nom du répertoire dans lequel se trouvent les fichiers source
ACCESS PARAMETERS permet de décrire la structure du fichier externe (dans le cas du driver générique Oracle, cette syntaxe se rapproche beaucoup d'un fichier de description Sql*Loader)
LOCATION indique la liste des fichiers externes à lire

alter_table_partitioning

[set_subpartition_template] [modify_table_partition] [move_table_partition] [add_table_partition] [coalesce_table_partition]

[drop_table_partition] [drop_table_subpartition] [rename_partition_subpart] [truncate_partition_subpart] [split_table_partition] [split_table_subpartition]

[merge_table_partitions] [merge_table_subpartitions] [exchange_partition_subpart]

Cette clause n'est applicable que sur les tables partitionnées.

modify_table_default_attrs

[segment_attributes_clause] [data_segment_compression] [key_compression] [alter_overflow_clause] [LOB_parameters]

Cette clause n'est applicable que sur les tables partitionnées.
Elle permet de spécifier de nouvelles valeurs par défaut pour les attributs de la table, qui seront utilisées dans les prochaines créations de partitions.
Seuls, les attributs spécifiquement nommés dans cette instruction seront affectés.
FOR PARTITION ne s'applique qu'aux tables à partitions composites. (les sous partitions déjà existantes ne sont pas affectées par l'instruction)
PCTTHRESHOLD, key_compression, ainsi que la clause alter_overflow_clause ne sont valide que pour les IOT partitionnées.
Dans la clause key_compression, integer ne peut pas être spécifié avec le mot clé COMPRESS. (cela ne peut être appliqué qu'à la création de la table (CREATE TABLE)).
Le paramètre PCTUSED ne peut pas être spécifié dans la clause segment_attributes d'un segment d'index d'une IOT.
Vous ne pouvez spécifier la clause key_compression_clause que si la compression de clé est déjà appliquée au niveau table.

set_subpartition_template

[list_values_clause] [partitioning_storage_clause]

Cette clause permet de créer ou modifier des valeurs défaut pour les sous-partitions de type list ou hash. Cette clause n'est valide que pour des partitions composites. Elle remplace éventuellement tout modèle existant. Les sous-partitions ainsi que les indexes locaux ou globaux existants ne sont pas affectés.

Il est possible de supprimer un modèle de sous-partition via l'instruction ALTER TABLE ... SET SUBPARTITION TEMPLATE ().

Restrictions liées au modèles de sous-partitions

- Pour une sous-partition de type hash, vous ne pouvez pas spécifier la clause list_values

- Pour une sous-partition de type list, vous ne pouvez pas spécidier la clause hash_subpartition_quantity

- Pour les 2 types de sous-partitions la seule clause de partitioning_storage_clause que vous pouvez spécifier est TABLESPACE

modify_table_partition

[modify_range_partition] [modify_hash_partition] [modify_list_partition]

Cette clause permet de modifier les caractéristiques physiques d'une partition et également de modifier les attributs de stockage des colonnes LOB de la partition.

Il est également possible de spécifier le comportement d'Oracle vis à vis des indexes locaux devenus inutilisables après modification d'une partition.
Enfin, cette clause permet de modifier la table de correspondance (mapping table) d'une IOT.

modify_range_partition

[partition_attributes] [add_hash_subpartition] [add_list_subpartition] [update_global_index_clause] [parallel_clause] [alter_mapping_table_clause]

Notes sur la modification appliquée à une partition composite

Si vous spécifiez la clause allocate_extent_clause, Oracle alloue un nouvel extent pour chaque sous-partition.
Si vous spécifiez la clause deallocate_unused_clause, Oracle libère l'espace libre de chaque sous-partition.
Tous les autres attributs modifiés dans cette clause remplacent les anciennes valeurs définies.
Pour modifier les attributs d'une sous-partition, utilisez la clause FOR PARTITION de la clause modify_table_default_attrs.
Si vous spécifez UNUSABLE LOCAL INDEXES, aucune autre clause ne peut être utilisée conjointement.

modify_hash_partition

[partition_attributes] [alter_mapping_table_clause]

Lorsque vous modifiez une hash partition, les seules clauses modifiables dans la clause partition_attributes sont allocate_extent_clause et deallocate_unused_clause. Tous les autres attributs sont hérités des spécifications de niveau table.
COALESCE SUBPARTITION, applicable uniquement aux sous-partitions de type hash, permet de ventiler le contenu de la dernière sous-partition dans une ou plusieurs autres avant de la supprimer
Cette opération invalide tous les indexes globaux présents sur la table. Vous pouvez mettre à jour ces indexes pendant l'opération via la clause update_global_index_clause.
L'opération supprime les indexes de partition locaux.

Si vous spécifiez UNUSABLE LOCAL INDEXES, aucune autre clause ne peut être définie.

modify_list_partition

[partition_attributes]

Permet de modifier les attributs d'une partition et/ou d'ajouter ou supprimer des valeurs de la liste.

ADD VALUES permet d'ajouter des valeurs à la liste
DROP VALUES permet de supprimer des valeurs de la liste (cette opération échoue s'il existe des valeurs spécifiées en table)
Ces 2 options n'affectent pas les indexes locaux et globaux.

Restrictions sur l'ajout ou la suppression de valeurs

- Il n'est pas possible d'jouter des valeurs à la liste par défaut.

- Il n'est pas possible de supprimer une valeur appartenant à la liste par défaut

modify_table_subpartition

[modify_hash_subpartition] [modify_list_subpartition]

Cette clause n'est applicable que sur les partitions composites.

modify_hash_subpartition permet d'allouer ou désallouer l'espace de stockage d'une sous-partition
modify_list_subpartition permet de modifier les caractéristiques d'une partition par liste et d'ajouter ou supprimer des valeurs à la liste (uniquement sur les partitions composites)

move_table_partition

[table_partition_description] [update_global_index_clause] [parallel_clause]

Cette clause permet de déplacer une partition vers un autre segment ou un autre tablespace.
Elle permet également de réduire la fragmentation et de modifier les attributs physiques établis lors de la création.
Si la table contient des colonnes LOB, vous pouvez utiliser la clause LOB_storage_clause pour déplacer les données et indexes associés. Seules les colonnes LOB nommées sont affectées.
Les indexes globaux des IOT sont invalidés, mais il est possible de les mettre à jour pendant l'opération via la clause update_global_index_clause.
Les indexes locaux sont déplacés par l'opération et doivent être reconstruits si la partition déplacée n'est pas vide.
Lors du déplacement d'une colonne LOB, Oracle supprime l'ancient segment de données ainsi que l'index correspondant et recréé un nouveau segment.
Cette opération peut être éffectuée en parallèle.

Restrictions sur le déplacement des partitions

- S'il s'agit d'une partition par tranche (range) le seul attribut modifiable de la clause est TABLESPACE

- Il n'est pas possible d'utiliser cette clause pour une partition sous-partitionnée.(pour cela, utilisez la clause move_table_subpartition_clause)

move_table_subpartition

[subpartition_spec] [update_global_index_clause] [parallel_clause]

Cette clause est identique à la clause move_table_partition à la différence qu'elle s'applique à une sous-partition.

add_table_partition

[add_range_partition_clause] [add_hash_partition_clause] [add_list_partition_clause]

Cette clause permet d'ajouter une partition sur une table.

add_range_partition_clause

[range_values_clause] [table_partition_description]

Cette clause permet d'ajouter une partition par tranche après la dernière tranche existante.
Il est possible de spécifier également les attributs de la nouvelle partition, ainsi que les caractéristiques des éventuelles colonnes de type LOB.
S'il existe un index de domaine sur la table, celui-ci ne doit pas avoir le statut IN_PROGRESS ou FAILED.

Restrictions sur l'ajout d'une partition par tranche

- Il n'est pas possible d'ajouter une tranche si la dernière déclarée utilise le mot clé MAXVALUE (utiliser la clause split_table_partition pour insérer une tranche intermédiaire)

- Les clauses key_compression et OVERFLOW ne sont valides que pour une IOT, et OVERFLOW ne peut être spécifié que si la table partitionnée dispose déjà d'un segment d'overflow. la compresion de clé ne peut être spécifiée que si elle est déjà activée au niveau table

- Vous ne pouvez pas spécifier PCTUSED pour un segment d'index d'une IOT

range_values_clause spécifie la tranche supérieure de la nouvelle partition. La liste des valeurs (ordonnées et séparées par une virgule) doit être supérieure aux listes déjà présentes

add_hash_partition_clause

[partitioning_storage_clause] [update_global_index_clause] [parallel_clause]

Cette clause permet d'ajouter une partition de type hash.
Cette partition peut être explicitement nommée et stockée dans le tablespace indiqué (par défaut, celui de la table).
Il est possible de mettre à jour les indexes globaux pendant cette opération en utilisant la clause update_global_index_clause.
Spécifiez la clause parallel_clause pour paralléliser la création de la nouvelle partition.
Il n'est pas possible de spécifier partition_level_subpartition dans la clause table_partition_description.

add_list_partition_clause

[list_values_clause] [table_partition_description]

Cette clause permet d'ajouter une partition de type list.

Lors de l'ajout de la partition, un index local basé sur la liste définie est créé. Les indexes globaux ne sont pas impactés.

Restrictions sur l'ajout d'une partition par liste

- La clause partition_level_subpartition ne peut pas être spécifiée dans la clause table_partition_description

- Cette clause n'est pas permise si une partition DEFAULT existe déjà sur la table (utilisez dans ce cas la clause split_table_partition sur la partition DEFAULT

coalesce_table_partition

[update_global_index_clause] [parallel_clause]

Cette clause n'est applicable que sur les partitions de type hash.

Cela permet de supprimer la dernière partition en ventilant le contenu sur les autres.
Cette opération invalide les indexes globaux sur les tables standard, mais ils peuvent être mis à jour en utilisant la clause update_global_index_clause(les index globaux des IOT sont basés sur la clé primaire et restent donc valides).
Les indexes locaux de partitions sont supprimés et vous devez les recréer.

drop_table_partition

[update_global_index_clause] [parallel_clause]

Supprime une partition ainsi que les données qu'elle contient.

Si la partition contient des colonnes LOB, celles-ci ainsi que leur index sont également supprimés.
Si la table est une IOT sur laquelle est définie une table de correspondance (mapping table), celle-ci est également supprimée.
Il est possible de mettre à jour les indexes globaux d'une table standard pendant l'opération en utilisant la clause update_global_index_clause.

Restrictions sur la suppression de partition

- Vous ne pouvez pas supprimer une partition de type hash

- Si la table ne contient qu'une partition, vous ne pouvez pas la supprimer. Vous devez supprimer la table elle-même

drop_table_subpartition

Supprime une sous-partition de type list (et les données contenues) d'une partition composite.

La sous-partition est également retirée de l'index local. Les autres indexes de sous-partition ne sont pas affectés mais les indexes globaux sont marqués UNUSABLE sauf si vous spécifiez la clause update_global_index_clause.

Restrictions sur la suppression de sous-partitions

- Vous ne pouvez pas supprimer une sous-partition de type hash. Utilisez pour cela la clause MODIFY PARTITION ... COALESCE SUBPARTITION

- Vous ne pouvez pas supprimer l'ultime sous-partition d'une partition. Utilisez pour cela la clause drop_table_partition

rename_partition_subpart

Cette clause permet de renommer une partition ou une sous-partition. (le nouveau nom ne doit pas déjà exister dans une autre partition/sous-partition).

truncate_partition_subpart

[update_global_index_clause] [parallel_clause]

Cette clause permet de supprimer les lignes d'une partition ou d'une sous-partition.

TRUNCATE PARTITION supprime les lignes de la partition spécifiée.
TRUNCATE SUBPARTITION supprime les lignes de la sous-partition spécifiée.
Si la partition/sous-partition contient des données, vous devez d'abord désactiver les contraintes d'intégrité référentielles (ou sinon, supprimer (DELETE) les lignes puis tronquer la partition).
Si la table contient des colonnes de type LOB, alors les segments de données et d'indexes du LOB seront également tronqués.

Si un index de domaine est défini sur la table, alors cet index ne doit pas avoir un statut IN_PROGRESS ou FAILED, ainsi que l'index de partition qui ne doit pas être en statut IN_PROGRESS.
Les indexes locaux sont également tronqués (ce qui replace leur statut à VALID).
Il est possible de mettre à jour les indexes globaux pendant cette opération en spécifiant la clause update_global_index_clause.
DROP STORAGE permet de libérer l'espace obtenu
REUSE STORAGE permet de conserver l'espace libéré

split_table_partition

[partition_spec] [update_global_index_clause] [parallel_clause]

Cette clause permet de créer 2 nouvelles partitions à partir de celle que vous spécifiez, chacune ayant un nouveau segment, de nouveaux attributs physiques et un nouveau segment initial (le segment de la partition éclatée est supprimé).

Les nouvelles partitions héritent des attributs physiques (non spécifiés) de la partition éclatée.
Si vous éclatez la list partition DEFAUT, alors la première des deux nouvelles partitions héritera des valeurs éclatées et la deuxième des valeurs DEFAULT.
Si la table est une IOT, Oracle éclate également l'éventuelle table de correspondance définie (dans le même tablespace que la table). Les zones de débordement (overflow) sont également éclatées et il est possible de spécifier les attributs de segment via la clause OVERFLOW.
Les indexes locaux correspondant sont éclatés même s'ils sont marqués UNUSABLE.
Si la table contient des colonnes de type LOB, vous pouvez utiliser la clause LOB_storage_clause pour spécifier les attributs de stockage. (les anciens segments de données et d'index du LOB sont supprimés et de nouveaux sont créés).
AT (valide uniquement sur les partitions de type range), permet de spécifier la nouvelle borne maxi (non incluse) pour la première nouvelle partition, les autres valeurs étant dirigées vers la deuxième nouvelle partition
VALUES (valide uniquement sur les partitions de type list), permet d'indiquer la liste des valeurs que vous voulez inclure dans la première nouvelle partition (et placer de facto les autres valeurs dans la deuxième nouvelle partition)
INTO permet de décrire les deux nouvelles partitions créées par l'éclatement. Dans la clause partition_spec le mot clé PARTITION est obligatoire même si vous ne spécifiez pas de nom ni d'attribut physique pour les nouvelles partitions
Pour une partition composite de type range-hash, si vous spécifiez des sous-partitions pour les nouvelles partitions, vous ne pouvez spécifier que la clause TABLESPACE pour les partitions. Toutes les autres valeurs sont héritées de la partition éclatée.
Pour une partition composite de type range-list, vous ne pouvez pas spécifier de sous-partitions pour les nouvelles partitions créées.
La partition est également éclatée dans les indexes locaux définis sur la table, même s'ils sont marqués UNUSABLE.
Les indexes globaux sont invalidés, mais peuvent être mis à jour durant l'opération avec la clause update_global_index_clause.

Restrictions sur l'éclatement des partitions

- Cette clause n'est pas applicable sur une partition de type hash

- Dans la clause partition_spec vous ne pouvez spécifier les clauses key_compression et OVERFLOW que pour une IOT. De plus vous ne pouvez spécifier le paramètre PCTUSED pour un segment d'index d'une IOT

split_table_subpartition

[subpartition_spec] [update_global_index_clause] [parallel_clause]

Identique à la clause split_table_partition, mais pour éclater une sous-partition.

merge_table_partitions

[partition_spec] [update_global_index_clause] [parallel_clause]

Cette clause permet de fusionner deux partitions en une nouvelle puis de supprimer les deux partitions d'origine.

Les deux partitions doivent se suivrent s'il s'agit de partitions par tranche (range). La nouvelle partition hérite de la limite la plus haute des deux partitions.
Les partitions par liste peuvent ne pas se suivre. le contenu de la nouvelle liste consiste en une fusion des deux listes. Si l'une des deux partitions est la partition DEFAULT, la nouvelle partition devient la partition DEFAULT.
Si vous fusionnez deux partitions composites de type range-list, vous ne pouvez pas spécifier la clause partition_level_subpartition (les informations de sous-partitionnement sont héritées du modèle (template). Si aucun modèle n'existe, Oracle ne crée qu'une sous-partition DEFAULT.
Les attributs non spécifiés dans la clause segment_attributes_clause héritent des valeurs définies au niveau de la table.
Les indexes globaux des tables standard sont marqués UNUSABLE, et peuvent être mis à jour pendant l'opération via la clause update_global_index_clause.
Les indexes locaux des partitions fusionnées sont supprimés et vous devez recréer un nouvel index local sur la nouvelle partition.

Vous ne pouvez pas spécifier cette clause sur une partition de type hash. Utilisez la clause coalesce_table_partition pour cela.

merge_table_subpartitions

[subpartition_spec] [update_global_index_clause] [parallel_clause]

Cette clause permet de fusionner deux sous-partitions de type list en une nouvelle puis de supprimer les deux sous-partitions d'origine.

Les deux sous-partitions doivent provenir de la même partition.
Les indexes locaux sont également fusionnés puis marqués UNUSABLE ainsi que tous les indexes globaux.

Restrictions sur la fusion de sous-partitions

- Cette clause n'est pas applicable sur des sous-partitions de type hash

exchange_partition_subpart

[exceptions_clause] [update_global_index_clause] [parallel_clause]

Utilisez cette clause lorsque vous voulez échanger les segments de données ou d'index des éléments suivants :

- Une table non partitionnée avec une partition de type hash, list ou range

- une partition de type hash avec une sous-partition de type range d'une partition composite de type range-hash

- une partition de type list avec une sous-partition de type list d'une partition composite de type range-list

Dans tous les cas, la structure de la table et des partitions ou sous-partitions échangées doit être identique. Dans le cas de partitions par listes, la liste des valeurs doit également correspondre.

WITH TABLE table indique la table dans laquelle les partitions ou sous-partitions doivent être échangées
INCLUDING INDEXES spécifie que les indexes locaux seront également échangés
EXCLUDING INDEXES spécifie que les indexes seront marqués UNUSABLE
WITH VALIDATION retourne une erreur si une ligne de la table ne peut être échangée entre partition ou sous-partition
WITHOUT VALIDATION indique qu'aucune vérification de correspondance ne sera faite pendant l'opération

exceptions_clause

Cette clause spécifie le nom d'une table dans laquelle seront stockés les ROWID des lignes en erreur (si cette clause est omise, les erreurs sont consignées dans la table EXCEPTIONS).
La table EXCEPTIONS peut être créée avec les scripts suivants :

- UTLEXCPT.SQL utilise les ROWID physiques (ne peut donc stocker les ROWID des tables organisées en index)

- UTLEXPT1.SQL utilise les ROWID universels (UROWID)

Si vous créez votre propre table d'erreur, celle-ci doit respecter la structure utilisé dans ces scripts.

Restriction sur l'utilisation de la clause exception

- Cette clause n'est pas valide avec des sous-partitions

- La table partitionnée doit avoir été créée avec une contrainte UNIQUE dont le statut est DISABLE VALIDATE

Si ces conditions ne sont pas respectées, la clause est ignorée.

list_values_clause

Indique la liste des valeurs séparées par une virgule incluses dans la partition ou sous-partition.

Le mot clé DEFAULT doit être utilisé seul sur la dernière partition et indique que la partition contiendra toutes les valeurs non spécifiées dans les autres partitions.

range_values_clause

Spécifie la valeur maximum non incluse des données stockées dans la partition.

Le mot clé MAXVALUE indique la plus haute valeur possible et concerne donc la dernière partition.

partitioning_storage_clause

Cette clause permet de spécifier les informations de stockage des partitions et de leurs éventuelles colonnes de type LOB.

partition_attributes

[physical_attributes_clause] [logging_clause] [allocate_extent_clause] [deallocate_unused_clause] [data_segment_compression] [modify_LOB_parameters]

Cette clause permet de spécifier les attributs de chaque partition.

add_hash_subpartition

[subpartition_spec] [update_global_index_clause] [parallel_clause]

Cette clause permet d'ajouter une sous-partition de type hash.

Elle n'est valide que pour une partition composite de type range-hash.
La clause list_values_clause n'est pas valide pour cette opération.
Les indexes globaux sont invalidés mais peuvent être mis à jour via la clause update_global_index_clause.
Les indexes locaux sont créés.

add_list_subpartition

[subpartition_spec]

Cette clause permet d'ajouter une sous-partition de type list à une partition.

Elle n'est valide que sur une partition composite de type range-list qui ne contient pas encore de sous-partition DEFAULT.
La clause list_values_clause doit être renseignée et les nouvelles valeurs de la liste ne doivent pas exister dans d'autres sous-partitions.
La sous-partition est ajoutée aux indexes locaux.

Restrictions sur l'ajout d'une sous-partition de type list

- Vous ne pouvez pas spécifier cette clause si une sous-partition DEFAULT existe déjà (vous devez pour cela éclater la sous-partition à l'aide de la clause split_list_subpartition)

modify_hash_subpartition

[allocate_extent_clause] [deallocate_unused_clause] [modify_LOB_parameters]

Cette clause permet d'allouer ou désallouer de l'espace pour une sous-partition.

Elle n'est valide que pour une partition composite de type range-hash.
Vous pouvez également spécifier ce que deviennent les indexes locaux devenus UNUSABLE via la clause unusable_local_indexes.

Restrictions sur la modification d'une sous-partition de type hash

- Les seuls paramètres spécifiables de la clause modify_LOB_parameters sont allocate_extent_clause et deallocate_unused_clause

modify_list_subpartition

[allocate_extent_clause] [deallocate_unused_clause] [modify_LOB_parameters]

Cette clause permet le même type de modification que pour une sous-partition de type hash.

Elle permet également d'ajouter ou supprimer des valeurs de la liste.

Cette clause n'est valide pour pour une partition composite de type range-list.

ADD VALUES permet d'ajouter une valeur à la liste. cette nouvelle valeur ne doit pas déjà exister dans une autre sous-partition de la même partition
Si une sous-partition DEFAULT a été spécifiée, une vérification est faite pour contrôler qu'aucune valeur ajoutée n'existe dans les lignes gérées par la sous-partition DEFAULT sous peine de tomber en erreur.
Les indexes locaux sont mis à jour.

DROP VALUES permet de supprimer des valeurs de la liste
Ces valeurs doivent être des sous-ensembles de valeurs préalablement définies.
Il n'est pas possible d'utiliser cette clause pour supprimer toutes les valeurs d'une sous-partition. (pour cela vous devez utiliser l'instruction ALTER TABLE ... DROP SUBPARTITION).
Si une partition contient des lignes dont la valeur correspond à celles que vous voulez supprimer, l'opération échoue. Vous devez donc supprimer ces lignes avant de supprimer la valeur de la liste.
Les indexes locaux sont mis à jour.

Restrictions sur la modification d'une sous-partition de type list

- Les seuls paramètres spécifiables de la clause modify_LOB_parameters sont allocate_extent_clause et deallocate_unused_clause

table_partition_description

[segment_attributes_clause] [data_segment_compression] [key_compression] [LOB_storage_clause] [varray_col_properties] [partition_level_subpartition]

Cette clause permet de définir les attributs d'une partition (segment, zone d'overflow, colonnes LOB).

partition_level_subpartition

[subpartition_spec]

Cette clause permet de spécifier des sous-partitions de type hash ou list pour une nouvelle partition composite de type range-hash ou range-list.

Cette clause n'est valide que sur une partition composite.
Cette clause surcharge toute description de sous-partitionnement définie par un modèle (template) de niveau table.

Concernant tous les types de partitions composites

Vous pouvez spécifier le nombre de sous-partitions ainsi que les tablespaces d'accueil.
Vous pouvez utiliser la clause subpartition_spec pour spécifier les sous-partitions nommées.
Si la clause partition_level_subpartition est omise et qu'il existe un modèle (template) alors celui-ci sera utilisé pour créer les sous-partitions.
Si la clause partition_level_subpartition est omise et qu'il n'existe pas de modèle, alors Oracle créé une sous-partition de type hash ou une sous-partition DEFAULT de type list.
La seule clause partitioning_storage_clause que vous pouvez spécifier dans la clause partition_spec est TABLESPACE.
Pour une partition composite de type range-hash, la clause list_values_clause de la clause subpartition_spec est invalide.

Concernant les partitions composites de type range-list

La clause hash_subpartition_quantity n'est pas prise en compte.
Dans la clause subpartition_spec, vous devez spécifier la clause list_values_clause pour chaque sous-partition et les valeurs spécifiées dans les listes doivent être uniques dans toutes les sous-partitions de la partition.

Un index de partition est ajouté.

partition_spec

[table_partition_description]

Cette clause permet de définir les caractéristiques d'une partition.

subpartition_spec

[list_values_clause] [partitioning_storage_clause]

Cette clause permet de définir les caractéristiques d'une sous-partition.

update_global_index_clause

Cette clause permet de mettre à jour les indexes globaux d'une partition lors d'un ordre DDL.

UPDATE GLOBAL INDEXES permet de mettre à jour les indexes globaux
INVALIDATE GLOBAL INDEXES (défaut) indique d'invalider les indexes globaux

Cette clause n'est pas applicable sur un index de domaine, ni sur une table organisée en index.
De plus, cette clause ne met à jour que les indexes dont le statut est USABLE et VALID. les indexes dont le statut est UNUSABLE demeurent avec ce statut et ceux dont le statut est INVALID sont ignorés.

parallel_clause

Permet de régler les paramètres de parallélisme.
NOPARALLEL (défaut) spécifie qu'aucun mécanisme de parallélisme n'est demandé
PARALLEL indique que l'on souhaite activer le mode parallele avec un degré équivalent au nombre de processeurs disponibles
PARALLEL integer indique que l'on active le parallélisme avec un degré égal à integer (nombre de threads mis en oeuvre dans l'opération)
Le traitement en parallèle est désactivé pour une table contenant un trigger ou une contrainte d'intégrité référentielle.
En présence d'un index bitmap sur la table, le parallélisme est désactivé si la table n'est pas partitionnée.
Si la table contient des colonnes de type LOB ou de type objet, les ordre insert, update ou delete qui modifient ce LOB ou cet objet ne seront pas traités en parallèle.
Un indicateur (hint) parallel surcharge la clause PARALLEL définie au niveau de la table.
Si un ordre DML ou CREATE TABLE ... AS SELECT référence un objet présent dans une autre base, le parallélisme n'est pas activé.

move_table_clause

[segment_attributes_clause] [data_segment_compression] [index_org_table_clause] [LOB_storage_clause] [varray_col_properties] [parallel_clause]

Cette clause permet de déplacer une table non partitionnée sur un autre segment ou un autre tablespace.

Elle permet également de modifier les attributs de stockage.
Les colonnes de type LOB peuvent aussi être déplacées via les clauses LOB_storage_clause et varray_col_properties.
Les colonnes de type LOB non spécifiées dans cette clause ne sont pas déplacés.

Pour une table organisée en index, la clause index_org_table_clause permet de spécifier un autre segment pour la zone de débordement (overflow).
Lors du déplacement d'une IOT, la zone de débordement n'est pas recréée si la clause OVERFLOW n'est pas spécifiée, à deux exceptions près :

- Si vous modifiez la valeur de PCTTHRESHOLD ou de INCLUDING, alors le segment d'overflow sera reconstruit

- Si vous déplacez explicitement des colonnes de la zone de débordement (LOBs, varrays, nested table) de l'IOT, le segment d'overflow sera également reconstruit

Les segments de données et d'index des colonnes de type LOB ne seront pas reconstruit si elles ne sont pas explicitement spécifées dans l'instruction ALTER TABLE.

ONLINE indique que les opération du DML seront autorisées sur l'IOT pendant sa reconstruction

Restrictions sur la clause ONLINE

- Cette clause ne peut pas être combinée avec une autre clause dans la même instruction

- Cette clause ne peut être spécifiée que pour une table organisée en index non partitionnée

- Le parallélisme des opération du DML n'est pas supporté avec l'option ONLINE

enable_disable_clause

[using_index_clause] [exceptions_clause]

Cette clause permet de modifier l'état d'une contrainte.
ENABLE indique que la contrainte est activée
ENABLE VALIDATE indique que les données pré-existantes dans la table doivent respecter les contraintes
Si une donnée dans la table viole la contrainte, celle-ci reste à l'état DISABLE et Oracle retourne une erreur.
ENABLE NOVALIDATE n'effectue aucun contrôle sur les données pré-existantes, mais seulement sur les données insérées utltérieurement
Il n'est pas possible d'activer une clé étrangère qui référence une clé primaire ou unique désactivée.
DISABLE permet de désactiver la contrainte
DISABLE VALIDATE désactive la contrainte et supprime son index mais la laisse dans un état valide
DISABLE NOVALIDATE (défaut) désactive la contrainte
Si vous désactivez une contrainte de type primary key ou unique, Oracle supprime l'index associé.

using_index_clause

[storage_clause] [logging_clause] [global_partitioned_index]

Cette clause ne s'applique qu'aux contraintes de type PRIMARY KEY ou UNIQUE.
index indique que l'on souhaite utiliser l'index existant index pour valider la contrainte
create_index_statement permet de définir en ligne un ordre de création d'index
SORT indique que l'index devra être trié dans l'ordre croissant
NOSORT indique qu'aucun tri ne sera appliqué à l'index

global_partitioned_index

[index_partitioning_clause]

Cette clause permet de créer un index global de partition.

index_partitioning_clause

[segment_attributes_clause]

Cette clause permet de créer un index de partition.

5-2 - Exemples de modification de tables

Collection Retrieval

ALTER TABLE print_media MODIFY NESTED TABLE ad_textdocs_ntab
RETURN AS VALUE;

Activation du parallélisme

ALTER TABLE customers
PARALLEL;

Validation d'une contrainte

ALTER TABLE employees
ENABLE VALIDATE CONSTRAINT emp_manager_fk
EXCEPTIONS INTO exceptions;

Récupération des lignes en erreur

SELECT employees.*
FROM employees e, exceptions ex
WHERE e.row_id = ex.row_id
AND ex.table_name = 'EMPLOYEES'

Activation de contraintes

ALTER TABLE employees
ENABLE NOVALIDATE PRIMARY KEY
ENABLE NOVALIDATE CONSTRAINT emp_last_name_nn;

Désactivation d'une contrainte

ALTER TABLE customers
DISABLE UNIQUE (areaco, phoneno) CASCADE;

Création d'une table de récupération d'erreur sur une IOT

EXECUTE DBMS_IOT.BUILD_EXCEPTIONS_TABLE ('hr', 'countries', 'except_table');

ALTER TABLE countries
ENABLE PRIMARY KEY
EXCEPTIONS INTO except_table;

Désactivation de tous les triggers d'une table

ALTER TABLE employees
DISABLE ALL TRIGGERS;

Activation de tous les triggers d'une table

ALTER TABLE employees
ENABLE ALL TRIGGERS;

Récupération de l'espace inutilisé

ALTER TABLE employees
DEALLOCATE UNUSED;

Renommer une colonne

ALTER TABLE customers
RENAME COLUMN credit_limit TO credit_amount;

Suppression d'une colonne munie d'une contrainte

ALTER TABLE t1 DROP (colonne_pk) CASCADE CONSTRAINTS;

Modifications sur une IOT

-- modification de la valeur INITRANS de la partie préfixe
ALTER TABLE countries INITRANS 4;
-- ajout d'une zone d'overflow
ALTER TABLE countries ADD OVERFLOW;
-- modification de la valeur INITRANS de la partie suffixe (overflow)
ALTER TABLE countries OVERFLOW INITRANS 4;

Eclatement d'une partition

ALTER TABLE sales SPLIT PARTITION SALES_Q4_2000
AT (TO_DATE('15-NOV-2000','DD-MON-YYYY'))
INTO (PARTITION SALES_Q4_2000, PARTITION SALES_Q4_2000b);

Eclatement d'une partition et gestion des LOB

ALTER TABLE print_media_part
SPLIT PARTITION p2 AT (150) INTO
(PARTITION p2a TABLESPACE omf_ts1
LOB ad_photo, ad_composite) STORE AS (TABLESPACE omf_ts2),
PARTITION p2b
LOB (ad_photo, ad_composite) STORE AS (TABLESPACE omf_ts2));

Ajout d'une partition de type range

ALTER TABLE print_media_part ADD PARTITION p3 VALUES LESS THAN
(MAXVALUE)
LOB (ad_photo, ad_composite) STORE AS (TABLESPACE omf_ts2)
LOB (ad_sourcetext, ad_finaltext) STORE AS (TABLESPACE omf_ts1);

Ajout d'une partition de type list

ALTER TABLE q1_sales_by_region
ADD PARTITION q1_nonmainland VALUES ('HI', 'PR')
STORAGE (INITIAL 20K NEXT 20K) TABLESPACE tbs_3
NOLOGGING;

Ajout d'une partition à une table partitionnée en range-hash

ALTER TABLE sales ADD PARTITION q1_2000
VALUES LESS THAN (2000, 04, 01)
SUBPARTITIONS 8 STORE IN tbs5;

Ajout d'une sous-partition à une table partitionnée en range-hash

ALTER TABLE diving MODIFY PARTITION locations_us
ADD SUBPARTITION us_locs5 TABLESPACE us1;

Ajout d'une partition à une table partitionnée en range-list

ALTER TABLE quarterly_regional_sales
ADD PARTITION q1_2000 VALUES LESS THAN (TO_DATE('1-APR-2000','DD-MON-YYYY'))
STORAGE (INITIAL 20K NEXT 20K) TABLESPACE ts3 NOLOGGING
(
SUBPARTITION q1_2000_northwest VALUES ('OR', 'WA'),
SUBPARTITION q1_2000_southwest VALUES ('AZ', 'UT', 'NM'),
SUBPARTITION q1_2000_northeast VALUES ('NY', 'VM', 'NJ'),
SUBPARTITION q1_2000_southeast VALUES ('FL', 'GA'),
SUBPARTITION q1_2000_northcentral VALUES ('SD', 'WI'),
SUBPARTITION q1_2000_southcentral VALUES ('OK', 'TX')
);

Ajout d'une sous-partition à une table partitionnée en range-list

ALTER TABLE quarterly_regional_sales
MODIFY PARTITION q1_1999
ADD SUBPARTITION q1_1999_south
VALUES ('AR','MS','AL') tablespace ts2;

Suppression d'un index de partition

ALTER INDEX npr DROP PARTITION P1;

Reconstruction d'un index de partition

ALTER INDEX npr REBUILD PARTITION P2;

Modification d'attributs par défaut d'une partition

ALTER TABLE emp
MODIFY DEFAULT ATTRIBUTES PCTFREE 25;

ALTER TABLE emp
MODIFY DEFAULT ATTRIBUTES FOR PARTITION p1 TABLESPACE ts1;

Suppression de valeurs d'une list partition

ALTER TABLE sales_by_region
MODIFY PARTITION region_south
DROP VALUES ('OK', 'KS');

Modification d'un modèle de sous-partitionnement

ALTER TABLE emp_sub_template
SET SUBPARTITION TEMPLATE
(SUBPARTITION e, TABLESPACE ts1,
SUBPARTITION f, TABLESPACE ts2,
SUBPARTITION g, TABLESPACE ts3,
SUBPARTITION h, TABLESPACE ts4
);

Suppression d'un modèle de sous-partitionnement

ALTER TABLE emp_sub_template
SET SUBPARTITION TEMPLATE ( );

Eclatement d'une partition de type list DEFAULT

-- on suppose que rest est la partition DEFAULT de la table
ALTER TABLE list_customers SPLIT PARTITION rest
VALUES ('MEXICO', 'COLOMBIA')
INTO (PARTITION south, PARTITION rest);
-- rest devient la nouvelle partition DEFAULT

Fusion de deux partitions

ALTER TABLE list_customers
MERGE PARTITIONS asia, rest INTO PARTITION rest;

Suppression d'une partition

ALTER TABLE print_media_part DROP PARTITION p3;

Echange de partitions

ALTER TABLE sales
EXCHANGE PARTITION feb97 WITH TABLE sales_feb97
WITHOUT VALIDATION;

Changement de statut des indexes locaux d'une partition

ALTER TABLE sales MODIFY PARTITION nov96
UNUSABLE LOCAL INDEXES;

Reconstruction des indexes locaux d'une partition

ALTER TABLE sales MODIFY PARTITION jan97
REBUILD UNUSABLE LOCAL INDEXES;

Modification des attributs d'une partition

ALTER TABLE branch MODIFY PARTITION branch_ny
STORAGE (MAXEXTENTS 75) LOGGING;

Déplacement d'une partition

ALTER TABLE print_media_part
MOVE PARTITION p2b TABLESPACE omf_ts1;

Renommer une table

ALTER TABLE employees RENAME TO employee;

Renommer une partition

ALTER TABLE employee RENAME PARTITION emp3 TO employee3;

Suppression du contenu d'une partition

ALTER TABLE deliveries
TRUNCATE PARTITION sys_p017 DROP STORAGE; -- restitution de l'espace libéré

Tables objet

-- création du type de base
CREATE TYPE emp_t AS OBJECT (empno NUMBER, address CHAR(30));

-- création de la table avec OID sur la clé primaire
CREATE TABLE emp OF emp_t (
empno PRIMARY KEY)
OBJECT IDENTIFIER IS PRIMARY KEY;

-- création d'une table munie d'une référence sur la table emp_t
CREATE TABLE dept (dno NUMBER, mgr_ref REF emp_t SCOPE is emp);

-- ajout d'une contrainte
ALTER TABLE dept ADD CONSTRAINT mgr_cons FOREIGN KEY (mgr_ref)
REFERENCES emp;

-- ajout d'une référence
ALTER TABLE dept ADD sr_mgr REF emp_t REFERENCES emp;

Ajout d'une colonne

ALTER TABLE countries
ADD (duty_pct     NUMBER(2,2)  CHECK (duty_pct < 10.5),
visa_needed  VARCHAR2(3));

Modification de la taille d'une colonne

ALTER TABLE countries
MODIFY (duty_pct NUMBER(3,2));

Allocation explicite d'un nouvel extent

ALTER TABLE employees
ALLOCATE EXTENT (SIZE 5K INSTANCE 4);

Modification de la clause DEFAULT

ALTER TABLE product_information
MODIFY (min_price DEFAULT 10);

Ajout d'une contrainte de clé primaire

ALTER TABLE xwarehouses
ADD (PRIMARY KEY(XMLDATA."WarehouseID"));

Renommer une contrainte

ALTER TABLE customers RENAME CONSTRAINT cust_fname_nn
TO cust_firstname_nn;

Suppression de la clé primaire

ALTER TABLE departments
DROP PRIMARY KEY CASCADE;

Suppression d'une contrainte UNIQUE

ALTER TABLE employees
DROP UNIQUE (email);

Ajout d'une colonne de type LOB

ALTER TABLE employees ADD (resume CLOB)
LOB (resume) STORE AS resume_seg (TABLESPACE example);

Ajout d'une colonne de type table imbriquée

ALTER TABLE employees ADD (skills skill_table_type)
NESTED TABLE skills STORE AS nested_skill_table;