web analytics

Oracle: disable all constraints referencing the table

BEGIN
FOR cur IN (SELECT fk.owner, fk.constraint_name , fk.table_name
    FROM all_constraints fk, all_constraints pk
    WHERE fk.CONSTRAINT_TYPE = 'R' AND
    pk.owner = '&which_owner' AND
    fk.R_CONSTRAINT_NAME = pk.CONSTRAINT_NAME
           AND pk.TABLE_NAME = '&which_table'
        ) LOOP
        EXECUTE IMMEDIATE 'ALTER TABLE '||cur.owner||'.'||cur.table_name||
                ' MODIFY CONSTRAINT '||cur.constraint_name||' DISABLE';
   END LOOP;
END;

2 comments to Oracle: disable all constraints referencing the table

  • Noote

    how to enable them back..??

  • admin

    The most simple way is to use “ENABLE” command:

           EXECUTE IMMEDIATE
           'ALTER TABLE '||cur.owner||'.'||cur.table_name||
           ' MODIFY CONSTRAINT '||cur.constraint_name||' ENABLE';

    However, it’s even better to build the list of the constraints BEFORE:

     SELECT 'ALTER TABLE '||cur.owner||'.'||cur.table_name|| ' MODIFY CONSTRAINT '||cur.constraint_name||' ENABLE;'
     FROM
    (
    SELECT fk.owner, fk.constraint_name , fk.table_name
         FROM all_constraints fk, all_constraints pk
         WHERE fk.CONSTRAINT_TYPE = 'R' AND
         pk.owner = '&which_owner' AND
         fk.R_CONSTRAINT_NAME = pk.CONSTRAINT_NAME
                AND pk.TABLE_NAME = '&which_table'
             ) cur;

Leave a Reply

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

  

  

  

Categories

A sample text widget

Etiam pulvinar consectetur dolor sed malesuada. Ut convallis euismod dolor nec pretium. Nunc ut tristique massa.

Nam sodales mi vitae dolor ullamcorper et vulputate enim accumsan. Morbi orci magna, tincidunt vitae molestie nec, molestie at mi. Nulla nulla lorem, suscipit in posuere in, interdum non magna.