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.

Answer 1

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; /