TRUNCATE en Oracle SQL : Guide complet pour vider une table efficacement

La commande TRUNCATE en Oracle SQL est une instruction DDL (Data Definition Language) permettant de supprimer rapidement et efficacement l’intégralité des lignes d’une table. Contrairement au DELETE classique, TRUNCATE ne génère pas de rollback segment et s’exécute sans journalisation ligne par ligne, ce qui en fait l’outil privilégié des DBA et développeurs Oracle pour purger de grands volumes de données en un temps minimal.

Définition et utilisation de TRUNCATE en Oracle

La commande TRUNCATE TABLE est une instruction DDL qui supprime toutes les lignes d’une table en libérant immédiatement l’espace alloué (ou en le conservant, selon l’option choisie). Elle remet également à zéro les séquences implicites liées aux colonnes identité.

Cas d’usage en entreprise

Dans un contexte professionnel, TRUNCATE est fréquemment utilisé dans les situations suivantes :

• Rechargement de tables de staging ETL : avant chaque intégration de données, on vide la table temporaire pour repartir d’un état propre.
• Purge de tables de logs ou d’audit : en fin de période (mensuelle, annuelle), les historiques volumineux sont purgés rapidement.
• Environnements de test : réinitialisation des données de démonstration sans recréer la structure de la table.
• Maintenance planifiée : dans des jobs Oracle Scheduler, TRUNCATE est utilisé pour préparer des tables de travail avant des traitements batch.

⚠️ TRUNCATE est une commande irréversible par défaut : elle effectue un COMMIT implicite et ne peut pas être annulée avec un ROLLBACK.

Syntaxe complète de TRUNCATE en Oracle SQL

Voici la syntaxe officielle prise en charge par Oracle Database :

TRUNCATE TABLE [schéma.]nom_table
  [ { PRESERVE | PURGE } MATERIALIZED VIEW LOG ]
  [ { DROP [ ALL ] | REUSE } STORAGE ]
  [ CASCADE ];

Explication des paramètres essentiels

• [schéma.]nom_table : nom de la table à vider, précédé optionnellement du schéma propriétaire. Exemple : HR.EMPLOYEES.
• PRESERVE MATERIALIZED VIEW LOG (défaut) : conserve le log de vue matérialisée associé à la table.
• PURGE MATERIALIZED VIEW LOG : supprime également les logs de vues matérialisées.
• DROP STORAGE (défaut) : libère les extents alloués au-delà de l’extent initial. L’espace est rendu au tablespace.
• REUSE STORAGE : conserve les extents alloués pour la table. Utile si la table doit être rechargée immédiatement (évite une réallocation d’espace).
• CASCADE : propage le truncate aux tables enfants liées par une contrainte de clé étrangère activée avec ON DELETE CASCADE. Disponible depuis Oracle 12c.

Exemples pratiques de TRUNCATE en Oracle SQL

Exemple 1 – Purge d’une table de staging avant chargement ETL

Contexte métier : Une équipe Data Engineering charge chaque nuit les ventes journalières dans une table de staging STG_VENTES avant transformation. Avant chaque chargement, la table doit être entièrement vidée.

-- Étape 1 : Vider la table de staging avant le nouveau chargement
-- REUSE STORAGE est utilisé car la table sera immédiatement rechargée
-- ce qui évite de libérer puis réallouer les mêmes extents
TRUNCATE TABLE DWH.STG_VENTES REUSE STORAGE;

-- Étape 2 : Chargement des nouvelles données via INSERT ... SELECT
INSERT INTO DWH.STG_VENTES (ID_VENTE, DATE_VENTE, MONTANT, ID_CLIENT)
SELECT ID_VENTE, DATE_VENTE, MONTANT, ID_CLIENT
FROM DWH.SRC_VENTES_EXTERNES
WHERE DATE_VENTE = TRUNC(SYSDATE - 1);

COMMIT;

💡 Remarque : L’option REUSE STORAGE est particulièrement pertinente ici car les données sont rechargées dans la foulée. Oracle n’a pas besoin de libérer puis de réallouer l’espace disque, ce qui améliore les performances du batch nocturne.

Exemple 2 – Truncate en cascade sur des tables liées

Contexte métier : Dans une application de gestion de commandes, la table COMMANDES possède une table enfant LIGNES_COMMANDE avec une contrainte de clé étrangère. L’option CASCADE permet de vider les deux tables en une seule instruction.

-- Pré-requis Oracle 12c et supérieur
-- La contrainte FK doit être définie avec ON DELETE CASCADE
-- ou désactivée avant le TRUNCATE

-- Vider la table parent et propager aux tables enfants automatiquement
TRUNCATE TABLE VENTES.COMMANDES CASCADE;

-- Vérification : les deux tables sont maintenant vides
SELECT COUNT(*) AS nb_commandes   FROM VENTES.COMMANDES;       -- Résultat : 0
SELECT COUNT(*) AS nb_lignes      FROM VENTES.LIGNES_COMMANDE; -- Résultat : 0

💡 Remarque : Sans l’option CASCADE, Oracle retournerait une erreur ORA-02266 si des enregistrements enfants existent et que la contrainte de clé étrangère est active. L’option CASCADE simplifie considérablement les opérations de purge dans des modèles de données relationnels complexes.

Erreurs courantes avec TRUNCATE en Oracle

Erreur : ORA-02266 – Table référencée par une contrainte d’intégrité active

Message d’erreur Oracle :

ORA-02266: unique/primary keys in table referenced by enabled foreign keys

Cause : Vous tentez d’exécuter un TRUNCATE sur une table parent alors qu’une ou plusieurs tables enfants possèdent des contraintes de clé étrangère actives (ENABLED) qui la référencent.

Solutions disponibles :

-- Solution 1 : Désactiver temporairement la contrainte FK, truncate, puis réactiver
ALTER TABLE VENTES.LIGNES_COMMANDE DISABLE CONSTRAINT FK_LIGNES_COMMANDES;
TRUNCATE TABLE VENTES.COMMANDES;
ALTER TABLE VENTES.LIGNES_COMMANDE ENABLE CONSTRAINT FK_LIGNES_COMMANDES;

-- Solution 2 (Oracle 12c+) : Utiliser l'option CASCADE directement
TRUNCATE TABLE VENTES.COMMANDES CASCADE;

-- Solution 3 : Truncate les tables enfants en premier, puis la table parent
TRUNCATE TABLE VENTES.LIGNES_COMMANDE;
TRUNCATE TABLE VENTES.COMMANDES;

⚠️ Bonne pratique : Préférez la solution CASCADE ou le truncate ordonné (enfants avant parent) plutôt que la désactivation des contraintes, qui expose temporairement votre modèle à des incohérences de données.

Résumé : points clés de TRUNCATE en Oracle SQL

2 bonnes pratiques Oracle pour TRUNCATE

1. Utilisez REUSE STORAGE pour les tables rechargées immédiatement.
   Lors de cycles ETL où une table est vidée puis rechargée dans la même session ou le même job, REUSE STORAGE évite la désallocation et réallocation des extents, réduisant la fragmentation du tablespace et améliorant les temps d’exécution.
2. Ne substituez jamais TRUNCATE à DELETE sans analyse préalable.
   TRUNCATE ne déclenche pas les triggers DML (BEFORE DELETE, AFTER DELETE) et ne peut pas être filtré avec une clause WHERE. Si votre application repose sur ces mécanismes pour maintenir la cohérence des données (audit, archivage partiel), préférez DELETE ou un archivage préalable avant TRUNCATE.

Aller plus loin avec Oracle SQL

Pour approfondir vos connaissances sur la gestion des données en Oracle SQL, nous vous recommandons les articles suivants :

• La commande DELETE en Oracle SQL : Comprenez les différences fondamentales entre DELETE et TRUNCATE, et maîtrisez la suppression sélective de lignes avec des conditions WHERE complexes.
• DROP TABLE en Oracle SQL : Découvrez comment supprimer définitivement une table et sa structure, gérer la corbeille Oracle (RECYCLEBIN) et utiliser FLASHBACK TABLE pour récupérer des objets supprimés.
• Transactions, COMMIT et ROLLBACK en Oracle : Maîtrisez la gestion transactionnelle en Oracle, comprenez pourquoi TRUNCATE échappe au contrôle transactionnel et apprenez à sécuriser vos opérations de modification de données.

Sur le même thème

• DROP SQL Oracle : supprimer des objets de base de données
• PARTITION Oracle SQL : Guide complet avec exemples
• MATERIALIZED VIEW Oracle : Guide Complet et Pratique
• Flashback Drop Oracle : Récupérer une Table Supprimée
• CREATE TABLE Oracle : Guide Complet avec Exemples SQL
• ALTER TABLE Oracle : modifier une table SQL facilement

Veuillez laisser ce champ vide

Un nouveau tutoriel SQL par semaine

Nous ne spammons pas ! Consultez notre politique de confidentialité pour plus d’informations.

Vérifiez votre boite de réception ou votre répertoire d’indésirables pour confirmer votre abonnement.