Disable all table constraints in Oracle

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

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.

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).

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.

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.

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

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)

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