Désactiver toutes les contraintes (Oracle)

by Nicolas Calvi 7. avril 2009 18:22

Je voudrais partager une procédure PL/SQL que j'ai écrite récemment et qui permet d'activer ou de désactiver toutes les contraintes d'un schéma utilisateur Oracle. Cela permet notamment de pouvoir faire des TRUNCATE qui sont parfois bloquées par les contraintes sur les tables. Voici la procédure :

/******************************************************************************
* Nom : DatabaseContrainte
* Description : Active ou désactive les contraintes de la base
*
* - Variables -
* (IN) pi_owner : Nom du owner du schéma
* (IN) pi_mode : 1 les actives, sinon désactive
******************************************************************************/

PROCEDURE DatabaseContrainte(pi_owner IN VARCHAR2, pi_mode IN NUMBER) IS

-- Curseurs

CURSOR c_contrainte(pi_owner VARCHAR2) IS 
        SELECT fk.OWNER, fk.CONSTRAINT_NAME , fk.TABLE_NAME, decode(fk.CONSTRAINT_TYPE,'P',1,'R',2, 3) as COLONNE
            FROM all_constraints fk
            WHERE fk.OWNER = pi_owner
            AND fk.CONSTRAINT_TYPE IN ('R', 'P' ,'C')
            ORDER BY COLONNE;

-- Variables

v_table all_constraints.TABLE_NAME%type;
v_owner all_constraints.OWNER%type;
v_contrainte all_constraints.CONSTRAINT_NAME%type;
v_colonne NUMBER(1);

BEGIN
       
    -- On liste les contraintes
        
    OPEN c_contrainte(pi_owner);
    LOOP
        FETCH c_contrainte INTO v_owner, v_contrainte, v_table, v_colonne;
        EXIT WHEN c_contrainte%NOTFOUND;
    
        IF ( pi_mode = 1 ) THEN
    
            EXECUTE IMMEDIATE 'ALTER TABLE ' || v_owner || '.' || v_table || ' ENABLE CONSTRAINT ' || v_contrainte;
            
        ELSE
                
            EXECUTE IMMEDIATE 'ALTER TABLE ' || v_owner || '.' || v_table || ' DISABLE CONSTRAINT ' || v_contrainte || ' CASCADE';
            
        END IF;
        
    END LOOP;
    CLOSE c_contrainte;

END DatabaseContrainte;

Je voudrais juste préciser un point, le fait que l'on active / désactive les contraintes dans un ordre précis.  En effet j'ai ajouté un ORDER BY du nom de COLONNE qui traite d'abord les clés primaires, ensuite les clés étrangères pour enfin traiter les contraintes. Cela permet d'éviter certain plantage lors de la réactivation de certaines clés étrangères.