7 Replies Latest reply on Apr 28, 2009 10:00 AM by 155651

    Disable/Enable Constraint on all Tables in a database

    697717
      Hi,

      Is there a way I can disable all constraint on all tables in a database, and then enable them again? I am using Oracle 9i.

      Thanks.
        • 1. Re: Disable/Enable Constraint on all Tables in a database
          SanjayRs
          Write script to disable constraints
          1. FK
          2.UK
          3.PK
          4 others
          example FK disabling script generation
          -- DISABLE
          SELECT 'ALTER TABLE "' || a.table_name || '" DISABLE CONSTRAINT "' || a.constraint_name || '";' enable_constraints
          FROM   user_constraints a
          WHERE  
                 a.constraint_type = 'R' 
          AND    a.r_constraint_name IN (SELECT a1.constraint_name
                                         FROM   user_constraints a1
                                         WHERE  a1.table_name = DECODE(Upper('&1'),'ALL',a.table_name,Upper('&1'))
                                         );
          
          -- ENABLE
          SELECT 'ALTER TABLE "' || a.table_name || '" ENABLE CONSTRAINT "' || a.constraint_name || '";'
          FROM   user_constraints a
          WHERE  a.constraint_type = 'R'
          AND    a.table_name      = DECODE(Upper('&1'),'ALL',a.table_name,Upper('&1'))
          AND    a.status          = 'DISABLED';
          Ss
          • 2. Re: Disable/Enable Constraint on all Tables in a database
            591186
            Is there a way I can disable all constraint on all tables in a database, and then enable them again? I am using Oracle 9i.
            choose the owner.
            select ' alter table ' ||table_name || ' disable constraint '|| constraint_name|| ' ;' from dba_constraints where owner='OWNER';
            
            select ' alter table ' ||table_name || ' enable constraint '|| constraint_name|| ' ;' from dba_constraints where owner='OWNER';
            • 3. Re: Disable/Enable Constraint on all Tables in a database
              475755
              DISABLE CONSTRAINT;
              select 'alter table '||table_name||' disable constraint '||CONSTRAINT_NAME||';' from dba_constraints where owner='OWNER';

              ENABLE CONTRAINT
              select 'alter table '||table_name||' enable constraint '||CONSTRAINT_NAME||';' from dba_constraints where owner='OWNER';
              • 4. Re: Disable/Enable Constraint on all Tables in a database
                OrionNet
                Hello,

                You can following procedure and pass "ENABLE" or "DISABLE" to procedure and it will disable and enable all Refrential integrity constraints. You can further extend this disable more cosntraints;

                Usage will be
                exec manage_constraints('DISABLE');
                
                exec manage_constraints('ENABLE');
                CREATE OR REPLACE PROCEDURE manage_constraints (i_status IN varchar2)
                IS
                   CURSOR ref_cons
                   IS
                      SELECT constraint_name, table_name, status
                      FROM user_constraints
                      WHERE constraint_type in ( 'R')         -- YOu can disable more constraints type 
                
                   v_status   VARCHAR2 (10);
                   v_sql      VARCHAR2 (300);
                BEGIN
                   FOR e_cons IN ref_cons
                   LOOP
                      v_sql   :=
                            'ALTER TABLE '
                         || e_cons.table_name
                         || ' '
                         || i_status
                         || '  CONSTRAINT '
                         || e_cons.constraint_name;
                
                      --DBMS_OUTPUT.put_line (v_sql);
                      EXECUTE IMMEDIATE v_sql;
                   END LOOP;
                EXCEPTION
                   WHEN OTHERS
                   THEN
                      RAISE;
                END;
                Regards
                • 5. Re: Disable/Enable Constraint on all Tables in a database
                  Tubby
                  Something to be aware of as well is that you may have instances where there is an associated index for the constraint you are about to disable (unless you specify you want to keep the index around... it will be dropped).
                  ME_XE?drop table disable_test;
                  
                  Table dropped.
                  
                  Elapsed: 00:00:00.03
                  ME_XE?
                  ME_XE?create table disable_test (column1 number);
                  
                  Table created.
                  
                  Elapsed: 00:00:00.03
                  ME_XE?alter table disable_test add constraint disable_test_pk primary key (column1);
                  
                  Table altered.
                  
                  Elapsed: 00:00:00.03
                  ME_XE?
                  ME_XE?select COUNT(*) from user_indexes where table_name = 'DISABLE_TEST';
                  
                            COUNT(*)
                  ------------------
                                   1
                  
                  1 row selected.
                  
                  Elapsed: 00:00:00.01
                  ME_XE?select COUNT(*)  from user_constraints where table_name = 'DISABLE_TEST';
                  
                            COUNT(*)
                  ------------------
                                   1
                  
                  1 row selected.
                  
                  Elapsed: 00:00:00.01
                  ME_XE?
                  ME_XE?alter table disable_test disable constraint DISABLE_TEST_PK;
                  
                  Table altered.
                  
                  --THE ASSOCIATED INDEX IS GONE
                  Elapsed: 00:00:00.01
                  ME_XE?
                  ME_XE?select COUNT(*) from user_indexes where table_name = 'DISABLE_TEST';
                  
                            COUNT(*)
                  ------------------
                                   0
                  
                  1 row selected.
                  
                  Elapsed: 00:00:00.01
                  ME_XE?select COUNT(*)  from user_constraints where table_name = 'DISABLE_TEST';
                  
                            COUNT(*)
                  ------------------
                                   1
                  
                  1 row selected.
                  
                  Elapsed: 00:00:00.01
                  ME_XE?
                  ME_XE?
                  ME_XE?drop table disable_test;
                  
                  Table dropped.
                  
                  Elapsed: 00:00:00.01
                  ME_XE?
                  ME_XE?create table disable_test (column1 number);
                  
                  Table created.
                  
                  Elapsed: 00:00:00.00
                  ME_XE?alter table disable_test add constraint disable_test_pk primary key (column1);
                  
                  Table altered.
                  
                  Elapsed: 00:00:00.01
                  ME_XE?
                  ME_XE?select COUNT(*) from user_indexes where table_name = 'DISABLE_TEST';
                  
                            COUNT(*)
                  ------------------
                                   1
                  
                  1 row selected.
                  
                  Elapsed: 00:00:00.00
                  ME_XE?select COUNT(*)  from user_constraints where table_name = 'DISABLE_TEST';
                  
                            COUNT(*)
                  ------------------
                                   1
                  
                  1 row selected.
                  
                  Elapsed: 00:00:00.01
                  ME_XE?
                  
                  --SPECIFY YOU WANT TO KEEP THE INDEX
                  ME_XE?alter table disable_test disable constraint DISABLE_TEST_PK KEEP INDEX;
                  
                  Table altered.
                  
                  Elapsed: 00:00:00.01
                  ME_XE?
                  
                  --THE INDEX IS STILL HERE
                  ME_XE?select COUNT(*) from user_indexes where table_name = 'DISABLE_TEST';
                  
                            COUNT(*)
                  ------------------
                                   1
                  
                  1 row selected.
                  
                  Elapsed: 00:00:00.01
                  ME_XE?select COUNT(*)  from user_constraints where table_name = 'DISABLE_TEST';
                  
                            COUNT(*)
                  ------------------
                                   1
                  
                  1 row selected.
                  
                  Elapsed: 00:00:00.01
                  ME_XE?
                  Edited by: Tubby on Apr 22, 2009 11:51 AM
                  • 6. Re: Disable/Enable Constraint on all Tables in a database
                    697717
                    Thanks alot OrionNet, your response was very helpful.
                    Adil :)
                    • 7. Re: Disable/Enable Constraint on all Tables in a database
                      155651
                      http://www.myoracleguide.com/s/cons.htm