Tables

Fiche express
Type	Stockage physique et réorganisation des tables Oracle
Voir aussi	Tablespace · Schema

Gestion du stockage physique des tables Oracle : paramètres de remplissage des blocs, identifiant physique ROWID, dimensionnement et techniques de réorganisation.

Gestion des blocs

PCTFREE = pourcentage d'espace libre réservé dans chaque bloc (pour les futures mises à jour)
PCTUSED = seuil d'occupation en dessous duquel un bloc redevient éligible aux insertions

Règle : PCTFREE + PCTUSED doit rester inférieur à 100.

ASSM (Automatic Segment Space Management) : gestion automatique de l'espace des segments par bitmaps, alternative moderne à la gestion manuelle par listes libres (freelists).

La ROWID est une pseudo-colonne présente sur chaque ligne de chaque table, qui fait le lien entre une donnée fonctionnelle et son adresse physique de stockage.

SELECT ROWID, numero, nom FROM schema_name;

Stockage d'une table

CREATE TABLE schema_name
(numero    NUMBER(6),
 nom       VARCHAR2(40),
 prenom    VARCHAR2(30))
TABLESPACE data
PCTFREE 30
STORAGE (INITIAL 10M);

Points d'attention :

• bien calibrer PCTFREE selon le taux de croissance attendu des lignes (colonnes

 variables mises à jour ensuite)

• adapter la taille d'extent initiale (INITIAL) au volume réel attendu de la table

Estimer la taille d'une table

Démarche : estimer le nombre de lignes final, créer la table dans des conditions réelles, charger un échantillon représentatif, puis calculer le nombre de blocs occupés.

SELECT blocks FROM dba_tables WHERE table_name = 'schema_name';

SELECT result / nb_lignes_inserees * nb_lignes_final AS estimation FROM dual;

Estimer PCTFREE

PCTFREE = 100 * (1 - Ti / Tf)

où Ti est la taille moyenne initiale d'une ligne (octets) et Tf la taille moyenne finale après mises à jour. Ces valeurs peuvent être estimées via AVG_ROW_LEN.

Surveiller l'activité d'une table :

STATISTICS_LEVEL
DBA_TABS_MODIFICATIONS

Cette dernière vue n'est pas nécessairement à jour en temps réel : la forcer avec la procédure FLUSH_DATABASE_MONITORING_INFO du package DBMS_STATS.

Superviser l'espace occupé

DBA_SEGMENTS
DBA_EXTENTS

HWM = High Water Mark (limite haute jamais redescendue automatiquement des blocs utilisés)

DBMS_SPACE
  FREE_BLOCKS  -- segments gérés manuellement (freelists)
  SPACE_USED   -- segments gérés en ASM/auto
  UNUSED_SPACE

DBMS_STATS.GATHER_TABLE_STATS('schema_name', 'nom_table')

SELECT num_rows, blocks, avg_row_len, sample_size,
       TO_CHAR(last_analyzed, 'DD/MM HH24:MI') last_analyzed
FROM dba_tables WHERE table_name = 'schema_name' AND owner = 'schema_name';

SELECT t.blocks "occupes", s.blocks "alloues"
FROM dba_tables t, dba_segments s
WHERE s.segment_name = t.table_name AND s.owner = t.owner
  AND t.table_name = 'schema_name' AND t.owner = 'schema_name';

Détecter les problèmes de chaînage (row chaining/migration)

@?/rdbms/admin/utlchain.sql
ANALYZE TABLE nom_table LIST CHAINED ROWS;

SELECT COUNT(head_rowid) FROM chained_rows
WHERE table_name = 'schema_name' AND owner_name = 'schema_name';

Réorganiser le stockage d'une table

Techniques disponibles : DEALLOCATE UNUSED, recréation (export/import ou table temporaire), SHRINK SPACE, MOVE.

Objectif	Deallocate	Recréer	Export/import	Shrink	Move
Libérer l'espace au-dessus de la HWM	Oui	X	X	X	X
Améliorer le taux de remplissage		X	X	Oui	Oui
Corriger un problème de chaînage		Oui	X		Oui
Réorganisation générale		X	X		Oui

L'export/import reste la meilleure méthode en cas de changement de taille de bloc. Les ordres SHRINK SPACE (10g+) et MOVE sont à privilégier pour une reconstruction de table en place.

DEALLOCATE UNUSED — libère l'espace alloué mais jamais utilisé, au-dessus de la HWM :

ALTER TABLE schema_name DEALLOCATE UNUSED;
ALTER TABLE schema_name DEALLOCATE UNUSED KEEP 0;
ALTER TABLE schema_name DEALLOCATE UNUSED KEEP 1M;

L'option KEEP indique l'espace à conserver au-dessus de la HWM.

DBA_FREE_SPACE

Recréer via une table temporaire (ne recrée pas les objets dépendants — index, triggers) :

CREATE TABLE temp AS SELECT * FROM schema_name;
TRUNCATE TABLE schema_name;
INSERT INTO schema_name SELECT * FROM temp;

SHRINK SPACE :

ALTER TABLE nom_table SHRINK SPACE [ COMPACT ] [ CASCADE ];

• COMPACT : ne remonte pas la HWM (deux passes nécessaires pour libérer réellement l'espace).
• CASCADE : traite également les index dépendants.

Prérequis : activer le déplacement des lignes (nécessaire car SHRINK modifie les ROWID) :

ALTER TABLE nom_table ENABLE ROW MOVEMENT;
ALTER TABLE nom_table SHRINK SPACE;

MOVE

ALTER TABLE schema_name MOVE
PCTFREE 20
STORAGE (INITIAL 10M);

Attention : MOVE rend les index de la table inutilisables (UNUSABLE) — il faut les reconstruire ensuite :

ALTER INDEX nom_index REBUILD;

Vérifications utiles avant/après :

SELECT tablespace_name, blocks, extents FROM dba_segments
WHERE segment_name = 'schema_name' AND owner = 'schema_name';

SELECT num_rows, blocks, avg_row_len, sample_size,
       TO_CHAR(last_analyzed, 'DD/MM HH24:MI') last_analyzed
FROM dba_tables WHERE table_name = 'schema_name' AND owner = 'schema_name';

MOVE vers un autre tablespace :

ALTER TABLE schema_name.nom_table MOVE TABLESPACE data PCTFREE 20;

Trouver des informations

DBA_TABLES
DBA_TAB_COLUMNS
DBA_SEGMENTS
DBA_EXTENTS
DBA_TAB_MODIFICATIONS

Bugs corrigés : la règle "PCTUSED + PCTUSED < 100" de la page source a été corrigée en "PCTFREE + PCTUSED < 100" (c'est bien la somme des deux paramètres distincts qui doit rester sous 100) ; l'orthographe erronée "DESALLOCATE" a été corrigée en "DEALLOCATE" (mot-clé SQL réel) ; "ANALYSE TABLE" corrigé en "ANALYZE TABLE".

Voir aussi

• Tablespace — espace de stockage dans lequel résident les tables
• Schema — objets et privilèges au niveau utilisateur
• SGA — cache mémoire des blocs de données (database buffer cache)