Blog

Blog

Les surprises de CREATE TABLE AS

Pour certaines opérations, il n'est pas possible de faire un ALTER TABLE. Dans ce cas, on utilise généralement le mécanisme suivant :
  • Créer la nouvelle table à partir de la première
  • Supprimer l'ancienne table
  • Renommer la nouvelle
Malheureusement, le CREATE TABLE AS d'Oracle n'inclue pas les objets liés à la table: index, contraintes et triggers.

Le script pour générer le script

J'ai écrit les requêtes suivantes qui devraient vous permettre de récupérer le script à exécuter. Il faudra remplacer les paramètres par les vrais noms des tables dans la partie centrale (création/drop/renommage de table) 
/*Drop des triggers*/
SELECT 'DROP TRIGGER "'||owner||'"."'||trigger_name||'";' from all_triggers WHERE owner='&mon_schema' AND table_name='&ma_table';
/*Drop des index*/
SELECT 'DROP INDEX "'||owner||'"."'||index_name||'";' from all_indexes WHERE owner='&mon_schema' AND table_name='&ma_table' AND NOT (index_name LIKE 'PK_%' OR uniqueness='UNIQUE');
/*Drop des contraintes*/
SELECT 'DROP CONSTRAINT "'||owner||'"."'||constraint_name||'";' from all_constraints WHERE owner='&mon_schema' AND table_name='&ma_table' AND constraint_type='C';
SELECT 'DROP CONSTRAINT "'||owner||'"."'||constraint_name||'";' from all_constraints WHERE owner='&mon_schema' AND table_name='&ma_table' AND constraint_type='R';
SELECT 'DROP CONSTRAINT "'||owner||'"."'||constraint_name||'";' from all_constraints WHERE owner='&mon_schema' AND table_name='&ma_table' AND
 constraint_type='P';
 
/*Création de la nouvelle table*/
CREATE TABLE &mon_schema.&ma_table_new AS SELECT * FROM &mon_schema.&ma_table;
/*Drop de l'ancienne table*/
DROP TABLE &mon_schema.&ma_table;
/*Renommage de la nouvelle table*/
ALTER TABLE &mon_schema.&ma_table_new RENAME TO &mon_schema.&ma_table;


/*Recréation des contraintes*/
SELECT DBMS_METADATA.GET_DDL('CONSTRAINT', constraint_name, owner) from all_constraints WHERE owner='&mon_schema' AND table_name='&ma_table' AND
 constraint_type='P';
 SELECT DBMS_METADATA.GET_DDL('CONSTRAINT', constraint_name, owner) from all_constraints WHERE owner='&mon_schema' AND table_name='&ma_table' AND constraint_type='R';
SELECT DBMS_METADATA.GET_DDL('CONSTRAINT', constraint_name, owner) from all_constraints WHERE owner='&mon_schema' AND table_name='&ma_table' AND constraint_type='C';
/*Recréation des index*/
SELECT DBMS_METADATA.GET_DDL('INDEX', index_name, owner) from all_indexes WHERE owner='&mon_schema' AND table_name='&ma_table' AND NOT (index_name LIKE 'PK_%' OR uniqueness='UNIQUE');
/*Recréation des triggers*/
SELECT DBMS_METADATA.GET_DDL('TRIGGER', trigger_name, owner) from all_triggers WHERE owner='&mon_schema' AND table_name='&ma_table';

La démarche

La démarche permettant d'obtenir des objets vraiment corrects avec les bons noms est donc la suivante :
  • Générer le script
  • Dropper les triggers sur la table d'origine
  • Dropper les index sur la table d'origine
  • Dropper les contraintes sur la table d'origine
  • Créer la nouvelle table à partir de la table d'origine
  • Dropper l'ancienne table
  • Renommer la nouvelle table
  • Recréer les contraintes sur la nouvelle table
  • Recréer les index sur la nouvelle table
  • Recréer les triggers sur la nouvelle table

Pour aller plus loin

La doc Oracle sur le package dbms_metadata : http://docs.oracle.com/cd/E11882_01/appdev.112/e25788/d_metada.htm

Mon article sur comment dropper une table et ses contraintes associés sans faire un cascade : http://laetitia-avrot.blogspot.fr/2012/12/dropper-une-table-sans-cascade-ora-02449.html
Libellés : ,