Oracle: disable all constraints referencing the table

2 comments to Oracle: disable all constraints referencing the table

• Noote

  08/02/2013 at 14:29

  how to enable them back..??

• admin

  08/02/2013 at 15:03

  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;

This site uses Akismet to reduce spam. Learn how your comment data is processed.