Le stockage de données

I-A-1. Le tablespace temporaire▲

CREATE DATABASE <SID>...
   DEFAULT TEMPORARY TABLESPACE temp;

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2;

CREATE TEMPORARY TABLESPACE temp 
   TEMPFILE 'g:\oracle\oradata\orafrance\temp01.dbf' 
   SIZE 20M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 10M;

ALTER USER orafrance
   TEMPORARY TABLESPACE temp;

I-A-2. Le tablespace UNDO▲

CREATE DATABASE <SID>...
   UNDO TABLESPACE undotbs 
   DATAFILE 'g:\oracle\oradata\orafrance\undotbs.dbf' size 100M;

CREATE UNDO TABLESPACE undotbs
   DATAFILE 'g:\oracle\oradata\orafrance\undotbs.dbf' size 100M;

ALTER SYSTEM
   SET UNDO_TABLESPACE=undotbs2;

I-A-3. Le tablespace transportable▲

SQL> create tablespace DATA_TBS
   2 datafile 'd:\dvp1\oradata\data_tbs.ora' size 1M reuse
   3 AUTOEXTEND OFF
   4 ONLINE
   5 default storage
   6 (initial 32 k next 32 k
   7 minextents 2 maxextents unlimited
   8 pctincrease 1);

Tablespace created.

SQL> create tablespace INDEX_TBS
   2 datafile 'd:\dvp1\oradata\index_tbs.ora' size 1M reuse
   3 AUTOEXTEND OFF
   4 ONLINE
   5 default storage
   6 (initial 32 k next 32 k
   7 minextents 2 maxextents unlimited
   8 pctincrease 1) ;

Tablespace created.

SQL> create table orafrance.matable (col1 NUMBER, col2 NUMBER )
   2 tablespace DATA_TBS;

Table created.

SQL> alter table orafrance.matable add
  2 (constraint matable_pk primary key (col1)
  3 USING INDEX TABLESPACE INDEX_TBS
  4 );

Table altered.

SQL> execute DBMS_TTS.TRANSPORT_SET_CHECK ('DATA_TBS',TRUE);

PL/SQL procedure successfully completed.

SQL> SELECT * FROM TRANSPORT_SET_VIOLATIONS;

no rows selected

SQL> execute DBMS_TTS.TRANSPORT_SET_CHECK ('INDEX_TBS',TRUE);

PL/SQL procedure successfully completed.

SQL> SELECT * FROM TRANSPORT_SET_VIOLATIONS;

VIOLATIONS 
------------------------------------------------------------------------------
Index ORAFRANCE.MATABLE_PK in tablespace INDX_TBS enforces primary constraints 
of table ORAFRANCE.MATABLE in tablespace DATA_TBS

SQL> create table orafrance.matable_fille
  2 (col1 NUMBER)
  3 tablespace INDX_TBS;

Table created.

SQL> alter table orafrance.matable_fille
  2 add constraint matable_matablefille_fk foreign key (col1)
  3 references orafrance.matable(col1);

Table altered.

SQL> 
SQL> execute DBMS_TTS.TRANSPORT_SET_CHECK ('DATA_TBS',TRUE);

PL/SQL procedure successfully completed.

SQL> SELECT * FROM TRANSPORT_SET_VIOLATIONS;

no rows selected

SQL> execute DBMS_TTS.TRANSPORT_SET_CHECK ('INDX_TBS',TRUE);

PL/SQL procedure successfully completed.

SQL> SELECT * FROM TRANSPORT_SET_VIOLATIONS;

VIOLATIONS 
------------------------------------------------------------------------------
Constraint MATABLE_MATABLEFILLE_FK between table ORAFRANCE.MATABLE in 
tablespace DATA_TBS and table ORAFRANCE.MATABLE_FILLE in tablespace INDX_TBS 

Index ORAFRANCE.MATABLE_PK in tablespace INDX_TBS enforces primary constraints 
of table ORAFRANCE.MATABLE in tablespace DATA_TBS

SQL> execute DBMS_TTS.TRANSPORT_SET_CHECK ('DATA_TBS,INDX_TBS',TRUE);

PL/SQL procedure successfully completed.

SQL> SELECT * FROM TRANSPORT_SET_VIOLATIONS;

no rows selected

exp userid = 'system/manager@dvp1' 
            TRANSPORT_TABLESPACE=y 
            TABLESPACES=DATA_TBS,INDX_TBS
            TRIGGERS=y 
            CONSTRAINTS=y 
            GRANTS=y
            ROWS=y

imp userid = 'system/manager@dvp2' 
    TRANSPORT_TABLESPACE=y 
    TABLESPACES=DATA_TBS,INDX_TBS
    DATAFILES=d:\dvp2\oradata\data_tbs.ora, d:\dvp2\oradata\indx_tbs.ora

Lors de la création d'un segment (par exemple une table), Oracle crée un extent (dont la taille est définie dans les clauses de stockage) dans le tablespace cible de l'objet. Lorsqu'on rempli ce segment Oracle rempli les blocs de données qui constituent l'extent jusqu'à remplir l'extent entièrement et crée un nouvel extent si le précédent est plein.
Ainsi l'insertion de gros volumes peut générer un grand nombre de créations d'extents ce qui peut s'avérer coûteux à la longue.

Il existe deux modes de gestion d'espace pour les tablespaces : la gestion par dictionnaire ou local (qui est apparue avec la version 8i).
Un tablespace géré par dictionnaire ordonne à Oracle de stocker les informations relatives à l'allocation d'espace dans le dictionnaire de données ce qui induit une charge supplémentaire pour toutes les opérations sur les objets d'un tablespace et oblige le DBA à tuner finement la taille des extents pour éviter une fragmentation excessive des fichiers et des accès intempestifs au dictionnaire de données.
Heureusement depuis la version 8i, Oracle s'affranchit de la notion d'extent grâce au tablespace géré localement (dit locally managed) qui stocke toutes les informations de stockage en entête du tablespace.
Un tablespace est géré localement par défaut depuis la 9i et il est particulièrement conseillé de l'utiliser même avec la 8i. Effectivement, il permet d'éviter les contentions sur le dictionnaire de données, de simplifier la gestion de l'espace dans un tablespace qui devient complètement automatique et enfin, permet d'oublier la notion d'extent qui avait tendance à compliquer les choses inutilement. À noter que lors de la création de la base, le type du tablespace SYSTEM a un impact irréversible sur le type des autres tablespaces de la base : si le tablespace SYSTEM est LOCALLY MANAGED alors les autres tablespaces de la base devront aussi être géré localement.

Depuis la version 9i, il est possible d'allouer une taille de bloc différente de celui de la base pour chacun des tablespaces lors de sa création dès lors que le paramètre DB_nK_CACHE_SIZE ad hoc est renseigné :

CREATE TABLESPACE ora_data
    DATAFILE 'g:\oracle\oradata\orafrance\ORA_DATA01.dbf' size 100M
    BLOCKSIZE 4k;

À noter que les tablespaces d'une table ou d'un index partitionnés doivent tous avoir une même taille de bloc.

Il existe deux façons de gérer les extents en mode locally managed : UNIFORM SIZE et AUTOALLOCATE.
Le mode UNIFORM SIZE impose à Oracle de créer des extents de taille identique alors que le mode AUTOALLOCATE lui demande de créer des extents de plus en plus grands avec le nombre d'extents créés (les 16 premiers extents font 64k, les 64 suivant 1024k, etc.). À noter que ce dernier ne pourra pas être utilisé pour créer un tablespace temporaire.
Si le premier permet de suivre facilement l'allocation des extents, le deuxième permet au DBA de mieux gérer les systèmes qu'il ne maîtrise pas ou mal parce qu'il n'est pas maître des objets créés. Oracle précise d'ailleurs qu'il vaut mieux allouer de gros extents plutôt que de nombreux petits. De plus, il est intéressant de définir une taille d'extents proportionnelle à la valeur du paramètre DB_FILE_MULTIBLOCK_READ_COUNT.

Quoi qu'il en soit, il faudra éviter autant que possible de monopoliser des ressources système dans la gestion des extents.

Voici une notion souvent ignorée, mais je vais tâcher d'y remédier ;-)
La High-Water Mark (ou niveau de flottaison) est un repère positionné par Oracle pour indiquer jusqu'à quel bloc d'un objet les données ont été renseignées pour savoir quand il doit s'arrêter lors d'un balayage de la table (FULL SCAN).
Par exemple, si dans une base avec une taille de bloc (DB_BLOCK_SIZE) de 16k on insère 40k de données dans une table, une HWM sera positionnée sur le 3° blocs de la table.
Sauf que la HWM ne redescend pas toute seule ! Voilà qui pose un gros problème à Oracle…
Si l'on supprime beaucoup de lignes d'une table alors Oracle scannera quand même celle-ci jusqu'à la HWM au risque de balayer inutilement beaucoup de blocs vides.
Voilà une des raisons qui oblige le DBA à réorganiser l'espace, puisque seul un TRUNCATE (et évidemment une destruction :-)) de la table permet de remettre la HWM à 0. Pour reprendre l'exemple d'un excellent site qui traite du sujet, la HWM fonctionne comme le niveau du mercure dans un thermomètre : il ne redescend qu'en secouant le thermomètre ;-)

À l'insertion d'une ligne dans un segment, Oracle remplit le 1° bloc libre qu'il trouve en laissant PCTFREE % d'espace libre.
PCTFREE est la zone libre réservée aux mises à jour de données pour éviter la migration de celles-ci.
Effectivement, si un update fait en sorte qu'une ligne ne pourra pas être contenue dans un seul bloc, une partie de la ligne sera alors créée dans un autre bloc qui a peu de chances d'être contigu au premier.
Attention, il faut bien distinguer deux types de chaînage de données : la migration et le chaînage simple des données. Si le premier cas est décrit précédemment et peut être évité en changeant le PCTFREE, le deuxième cas lui est « normal » et relatif au cas où on insère des données dont la taille est supérieure à la taille du bloc.

SQL> CREATE TABLE MATABLE (ID NUMBER NOT NULL, 
  2  COMMENTS VARCHAR2(4000))  
  3  TABLESPACE TOOLS PCTFREE 0 PCTUSED 0 
  4  STORAGE ( INITIAL 4096 NEXT 4096 PCTINCREASE 0) ; 

Table créée.

SQL> INSERT INTO matable (id) values (1); 

1 ligne créée.

SQL> INSERT INTO matable (id) values (2); 

1 ligne créée.

SQL> INSERT INTO matable (id) values (3); 

1 ligne créée.

SQL> INSERT INTO matable (id) values (4); 

1 ligne créée.

SQL> INSERT INTO matable (id) values (5); 

1 ligne créée.

SQL> INSERT INTO matable (id) values (6);    

1 ligne créée.

SQL> COMMIT; 

Validation effectuée.

SQL> create table CHAINED_ROWS ( 
  2  owner_name         varchar2(30), 
  3  table_name         varchar2(30), 
  4  cluster_name       varchar2(30), 
  5  partition_name     varchar2(30), 
  6  subpartition_name  varchar2(30), 
  7  head_rowid         rowid, 
  8  analyze_timestamp  date ); 

Table créée.

SQL> analyze table matable list chained rows into chained_rows; 

Table analysée.

SQL> select count(*) from chained_rows; 

  COUNT(*)
----------
         0

SQL> declare 
  1  toto varchar2(4000); 
  2  begin 
  3  for i in 1..4000 loop 
  4  toto := toto || 'a'; 
  5  end loop; 
  6  update matable set comments = toto; 
  7  COMMIT; 
  8  end; 
  9  / 

Procédure PL/SQL terminée avec succès.

SQL> analyze table matable list chained rows into chained_rows; 

Table analysée.

SQL> select count(*) from chained_rows; 

  COUNT(*)
----------
         2

SQL> declare
  2  toto varchar2(4000);
  3  begin
  4  for i in 1..4000 loop
  5  toto := toto || 'a';
  6  end loop;
  7  insert into matable values (7,toto);
  8  COMMIT;
  9* end;
  10 /

Procédure PL/SQL terminée avec succès.

SQL> truncate table chained_rows;

Table tronquée.

SQL> analyze table matable list chained rows into chained_rows; 

Table analysée.

SQL> 
SQL> select * from chained_rows; 

aucune ligne sélectionnée

J'ai bien généré des lignes migrées dans le 1° cas (UPDATE) contrairement au 2° cas (INSERT).

La FREELIST contient la liste des blocs dans lesquels Oracle peut écrire des données. Un bloc sort de la freelist dès qu'il est rempli à plus de PCTUSED %. Il y retourne dès que le niveau redescend en dessous de PCTUSED : en cas de DELETE de lignes ou d'UPDATE avec des valeurs plus petites.

Donc, si le PCTFREE est trop grand, on réserve trop d'espace aux UPDATE et on perd de l'espace. S'il est trop petit on augmente le risque de lignes migrées. Et si le PCTUSED est trop grand, Oracle perd beaucoup de temps dans la gestion de la freelist, mais s’il est trop petit l'espace disque est une nouvelle fois perdu puisque le bloc n'est pas considéré comme libre pour l'écriture de données.

Voici la structure complète d'un ordre CREATE TABLESPACE :

LOGGING : permet d'indiquer si la création d'objets dans le tablespace doit être inscrite dans les redo logs.
FORCE LOGGING : permet de forcer le LOGGING même sur les objets ayant l'option NOLOGGING.
Cette option est invalide pour les tablespaces de type TEMPORARY ou UNDO.
ONLINE ou OFFLINE : permet de rendre le tablespace disponible ou non, juste après la création.
TEMPORARY : permet de créer un tablespace temporaire géré par le dictionnaire.
Pour créer un tablespace temporaire géré localement, il faut utiliser la commande CREATE TEMPORARY TABLESPACE.
data_segment_compression: peut prendre les valeurs COMPRESS ou UNCOMPRESS selon que l'on veut compresser ou non les données.

extent_management_clause :

LOCAL ou DICTIONARY : permet d'indiquer que le tablespace est géré localement (cf. 1.3) ou par dictionnaire.
UNIFORM ou AUTOALLOCATE : permet d'indiquer que le tablespace s'agrandit de manière uniforme (exprimé en taille d'extents) ou automatiquement.

segment_management_clause :

AUTO : permet de laisser la base gérer l'espace libre. Oracle ignore alors les paramètres PCTUSED, FREELIST et FREELIST GROUPS des objets du tablespace.

Voici la structure complète d'un ordre DROP TABLESPACE :

INCLUDING CONTENTS : permet de supprimer le contenu du tablespace. Si cette option n'est pas précisée, il sera impossible de supprimer un tablespace contenant des objets.

AND DATAFILES : permet de supprimer également les fichiers, une trace est alors inscrite dans le fichier des alertes pour chacun des fichiers supprimés.
CASCADE CONSTRAINTS : permet de supprimer les contraintes d'intégrité référentielles vers des objets d'autres tablespaces et les contraintes unique du tablespace supprimé.

Voici la structure complète d'un ordre ALTER TABLESPACE :

COALESCE : permet de fusionner les extents libres contigus.
BEGIN ou END BACKUP : permet d'indiquer le début ou la fin d'une sauvegarde base ouverte de la base. Cette option n'est pas applicable aux tablespaces temporaires ou en READ ONLY

Voici la structure complète d'un ordre ALTER DATABASE :

Voici la structure de la partie database_file_clauses spécifique à notre sujet :

Voilà bien une croyance défendue par de nombreux DBA sans qu'aucune explication ne puisse être fournie. Elle consiste donc à dire que lorsque le nombre d'extents d'un objet est trop important, il faut le reconstruire pour remplacer n petits extents par un initial extent énorme.
Tout d'abord, la question est immédiatement réglée pour les tablespaces gérés localement qui, comme on l'a vu dans le paragraphe 1.1, permettent de s'affranchir complètement de la notion d'extent.
Mais quid des aficionados des tablespaces gérés dans le dictionnaire ?
Je suppose que ce mythe repose sur une croyance : les données d'un extent sont organisées sur le disque de telles sortes qu'elles occupent des blocs contigus.
Mais pensez-vous vraiment que c'est possible ? Quel chance a-t-on de retrouver sur le disque suffisamment de blocs contigus pour y placer la totalité du gros extent ? Et si d'aventure il y avait effectivement suffisamment de blocs contigus, il resterait quand même une problématique propre au fonctionnement d'un disque.
Heureusement pour nous, un disque n'est pas dévoué à une tâche seulement, de telle sorte qu'il lit et écrit des données quasiment simultanément de telle sorte que les têtes de lecture des disques se déplacent fréquemment. Si une lecture intervient pendant l'écriture de votre gros extent initial alors le disque, après sa lecture, continuera l'écriture dans le bloc libre le plus proche du dernier bloc lu (pour éviter que la tête de lecture ne parcoure un trajet trop important), et bien entendu, on n'a quasiment aucune chance que ce bloc libre soit contigu au bloc écrit précédemment.
Ajouter à cela le fonctionnement en cluster ou dans une baie de disque et cela finira je l'espère de vous convaincre.

Il est temps pour moi de pondérer ces propos. En effet, la parole d'Oracle étant parfois idyllique, force est de constater qu'un excès vraiment important d'extents pose problème. Ainsi, on essayera de réduire le nombre d'extent sans verser dans la paranoïa.

C'est absolument faux et cette méprise est due à une méconnaissance de la commande COALESCE.
To coalesce dans la langue de shaekspeare signifie fusionner, mais cette fusion ne concerne pas du tout les données, mais seulement les extents libres. Effectivement, Oracle ne fait que remplacer n extents libre contigus par un seul extent plus grand.
Si 0 représente un extent libre et X un extent non libre alors pour un objet de la forme : X|X|X|0|0|X|0|0|0|X, le coalesce aura pour effet de réorganiser les extents de la manière suivante : X|X|X|00|X|000|X. Et comme on l'a vue précédemment, le fait de remplacer n extents par un extent plus gros n'a aucune incidence sur les performances.

Là, Oracle propose des chiffres très clairs, un objet a une gestion d'espace optimale lorsqu'il contient jusqu'à 1024 extents. Ainsi, on fera des tablespaces pour les petits, moyens et gros objets.

Taille d'extent selon la taille des objets

• Juqu'à 160 Mo, INITIAL=NEXT=160 ko
• De 160 Mo à 5 Go, INITIAL=NEXT=5 Mo
• Au delà de 5 Go, INITIAL=NEXT=160 Mo

Ainsi, une table de 160M pourra bien contenir jusqu'à 1024 extents de 160 ko.