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_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)
) ;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 );CREATE GLOBAL TEMPORARY TABLE today_sales
ON COMMIT PRESERVE ROWS
AS SELECT * FROM orders WHERE order_date = SYSDATE;CREATE TABLE persons OF person_t;CREATE TABLE books (title VARCHAR2(100), author person_t);CREATE TABLE dept_80
PARALLEL
AS SELECT * FROM employees
WHERE department_id = 80;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)
) ;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;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);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);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 );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;CREATE TABLE xwarehouses OF XMLTYPE;CREATE TABLE xwarehouses OF XMLTYPE
XMLSCHEMA "http://www.oracle.com/xwarehouses.xsd"
ELEMENT "Warehouse";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";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))
;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));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);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;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)
;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));CREATE TABLE employees_obj
( e_name VARCHAR2(100),
e_number NUMBER,
e_dept REF department_typ SCOPE IS departments_obj_t );CREATE TABLE employees_obj
( e_name VARCHAR2(100),
e_number NUMBER,
e_dept REF department_typ REFERENCES departments_obj_t);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;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') );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)
) ;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));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) );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) );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 );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);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));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);CREATE TABLE dept_10
CLUSTER personnel (department_id)
AS SELECT * FROM employees WHERE department_id = 10;