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

Dropper une table sans cascade (ORA-02449)

J'ai un collègue développeur qui a décidé de coder tous les champs texte en clob das une seule table. Forcément, les performances ont commencé à en pâtir. Quand les données ont dues être intégrées à une base décisionnelle, ça a été un désastre.

Ebauche de solution

J'ai donc réfléchi avec lui pour transformer mes clobs en varchar2...
Malheureusement, Oracle ne permet pas de faire un ALTER TABLE pour modifier un champd de type clob en champ de type varchar2, même en mettant la fonction de conversion.
J'ai donc créé une nouvelle table (oui, l'ordre des colonnes était important car il y avait du SQL Loader impacté) et voulu dropper la table d'origine pour renommer la nouvelle...

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

Oracle est sympa sur ce coup-là : il nous donne un sacré coup de main dans les précisions :
Cause: An attempt was made to drop a table with unique or primary keys referenced by foreign keys in another table.
Action:  Before performing the above operations the table, drop the foreign key constraints in other tables. You can see what constraints are referencing a table by issuing the following command: SELECT * FROM USER_CONSTRAINTS WHERE TABLE_NAME = "tabnam";
Bref, vous avez oublié de supprimer les contraintes liées à votre table.

La requête qui tue

J'ai donc concocté cette requête magique (oui, pas de user_constraints, mais dba_constraints car nous n'utilisons jamais les users applicatifs quand des humains se connectent) :
SELECT 'ALTER TABLE '||c.owner||'.'||c.table_name||' DROP CONSTRAINT '||c.constraint_name||';'
FROM dba_constraints p
JOIN dba_constraints c ON p.constraint_name=c.r_constraint_name
  AND p.owner=c.owner
WHERE (p.constraint_type = 'P' OR p.constraint_type = 'U')
AND c.constraint_type = 'R'
AND p.table_name = '&ma_table'
AND p.owner='&mon_schema';
J'ai donc pu tranquillement dropper mes contraintes une par une pour dropper ma table.

Pour aller plus loin

La doc Oracle sur l'ORA-02449 : http://docs.oracle.com/cd/E11882_01/server.112/e17766/e2100.htm#sthref1269