Disable all table constraints in Oracle

Question

How can I disable all table constrains in Oracle with a single command  This can be either for a single table  a list of tables  or for all tables

User · Answer

This is another way for disabling constraints (it came from https://asktom.oracle.com/pls/asktom/f?p=100:11:2402577774283132::::P11_QUESTION_ID:399218963817)

WITH qry0 AS
       (SELECT    'ALTER TABLE '
               || child_tname
               || ' DISABLE CONSTRAINT '
               || child_cons_name
                 disable_fk
              ,   'ALTER TABLE '
               || parent_tname
               || ' DISABLE CONSTRAINT '
               || parent.parent_cons_name
                 disable_pk
          FROM (SELECT a.table_name child_tname
                      ,a.constraint_name child_cons_name
                      ,b.r_constraint_name parent_cons_name
                      ,LISTAGG ( column_name, ',') WITHIN GROUP (ORDER BY position) child_columns
                  FROM user_cons_columns a
                      ,user_constraints b
                 WHERE a.constraint_name = b.constraint_name AND b.constraint_type = 'R'
                GROUP BY a.table_name, a.constraint_name
                        ,b.r_constraint_name) child
              ,(SELECT a.constraint_name parent_cons_name
                      ,a.table_name parent_tname
                      ,LISTAGG ( column_name, ',') WITHIN GROUP (ORDER BY position) parent_columns
                  FROM user_cons_columns a
                      ,user_constraints b
                 WHERE a.constraint_name = b.constraint_name AND b.constraint_type IN ('P', 'U')
                GROUP BY a.table_name, a.constraint_name) parent
         WHERE child.parent_cons_name = parent.parent_cons_name
           AND (parent.parent_tname LIKE 'V2_%' OR child.child_tname LIKE 'V2_%'))
SELECT DISTINCT disable_pk
  FROM qry0
UNION
SELECT DISTINCT disable_fk
  FROM qry0;

works like a charm

User · Answer

It doesn t look like you can do this with a single command  but here s the closest thing to it that I could find

User · Answer

It is better to avoid writing out temporary spool files   Use a PL SQL block   You can run this from SQL Plus or put this thing into a package or procedure   The join to USER TABLES is there to avoid view constraints   It s unlikely that you really want to disable all constraints  including NOT NULL  primary keys  etc    You should think about putting constraint type in the WHERE clause   BEGIN   FOR c IN    SELECT c owner  c table name  c constraint name    FROM user constraints c  user tables t    WHERE c table name   t table name    AND c status    ENABLED     AND NOT  t iot type IS NOT NULL AND c constraint type    P      ORDER BY c constraint type DESC    LOOP     dbms utility exec ddl statement  alter table       c owner             c table name       disable constraint      c constraint name     END LOOP  END      Enabling the constraints again is a bit tricker - you  need to enable primary key constraints before you can reference them in a foreign key constraint   This can be done using an ORDER BY on constraint type    P    primary key   R    foreign key   BEGIN   FOR c IN    SELECT c owner  c table name  c constraint name    FROM user constraints c  user tables t    WHERE c table name   t table name    AND c status    DISABLED     ORDER BY c constraint type    LOOP     dbms utility exec ddl statement  alter table       c owner             c table name       enable constraint      c constraint name     END LOOP  END

User · Answer

SELECT  ALTER TABLE    substr c table name 1 35       DISABLE CONSTRAINT    constraint name        FROM user constraints c  user tables u  WHERE c table name   u table name     This statement returns the commands which turn off all the constraints including primary key  foreign keys  and another constraints

User · Answer

Use following cursor to disable all constraint   And alter query for enable constraints     DECLARE  cursor r1 is select   from user constraints  cursor r2 is select   from user tables   BEGIN   FOR c1 IN r1   loop     for c2 in r2     loop        if c1 table name   c2 table name and c1 status    ENABLED  THEN         dbms utility exec ddl statement  alter table      c1 owner           c1 table name      disable constraint      c1 constraint name          end if      end loop    END LOOP  END

User · Answer

with cursor for loop  user    TRANEE   table    D    declare     constr all constraints constraint name TYPE  begin     for constr in          select constraint name from all constraints         where table name    D          and owner    TRANEE       loop         execute immediate  alter table D disable constraint    constr constraint name      end loop  end       If you change disable to enable  you can make all constraints enable

User · Answer

To take in count the dependencies between the constraints    SET Serveroutput ON BEGIN     FOR c IN      SELECT c owner c table name c constraint name     FROM user constraints c user tables t     WHERE c table name t table name     AND c status  ENABLED      ORDER BY c constraint type DESC c last change DESC           LOOP         FOR D IN          SELECT P Table Name Parent Table C1 Table Name Child Table C1 Owner P Constraint Name Parent Constraint              c1 constraint name Child Constraint         FROM user constraints p         JOIN user constraints c1 ON p constraint name c1 r constraint name          WHERE p constraint type  P          OR p constraint type  U           AND c1 constraint type  R          AND p table name UPPER c table name                    LOOP             dbms output put line    Disable the constraint      d Child Constraint      on table    d owner                       d Child Table                       dbms utility exec ddl statement  alter table      d owner          d Child Table      disable constraint                  d Child Constraint            END LOOP      END LOOP  END

User · Answer

It s not a single command  but here s how I do it  The following script has been designed to run in SQL Plus  Note  I ve purposely written this to only work within the current schema   set heading off  spool drop constraints out  select      alter table           owner                table name           disable constraint      -- or  drop  if you want to permanently remove     constraint name        from     user constraints   spool off  set heading on   drop constraints out   To restrict what you drop  filter add a where clause to the select statement -   filter on constraint type to drop only particular types of constraints filter on table name to do it only for one or a few tables    To run on more than the current schema  modify the select statement to select from all constraints rather than user constraints   Note - for some reason I can t get the underscore to NOT act like an italicization in the previous paragraph  If someone knows how to fix it  please feel free to edit this answer

User · Answer

You can execute all the commands returned by the following query    select  ALTER TABLE    substr c table name 1 35       DISABLE CONSTRAINT    constraint name        from user constraints c --where c table name    TABLE NAME

User · Answer

In the  disable  script  the order by clause should be that   ORDER BY c constraint type DESC  c last change DESC   The goal of this clause is  disable the constraints in the right order

User · Answer

This can be scripted in PL SQL pretty simply based on the DBA ALL USER CONSTRAINTS system view  but various details make not as trivial as it sounds  You have to be careful about the order in which it is done and you also have to take account of the presence of unique indexes   The order is important because you cannot drop a unique or primary key that is referenced by a foreign key  and there could be foreign keys on tables in other schemas that reference primary keys in your own  so unless you have ALTER ANY TABLE privilege then you cannot drop those PKs and UKs  Also you cannot switch a unique index to being a non-unique index so you have to drop it in order to drop the constraint  for this reason it s almost always better to implement unique constraints as a  real  constraint that is supported by a non-unique index

[sql] Disable all table constraints in Oracle

Examples related to sql

Examples related to oracle