Oracle SQL Developer - Disabling all constraints from schema

Question

I have two queries for disabling all constraints, but they don't seem to be working.

first one disables foreign keys:

select 'alter table '||table_name||' disable constraint '||constraint_name||';'
from user_constraints
where constraint_type ='R'
and status = 'ENABLED';

and the second disables everything else:

select 'alter table '||table_name||' disable constraint '||constraint_name||';'
from user_constraints
where status = 'ENABLED';

Now when I check the constraints with SELECT * FROM USER_CONSTRAINTS I can see that they all are still 'ENABLED'. Why is this? I tried commit after running the queries but to no avail.

My goal is to disable constraints from all tables with those queries.

Answer 1

As per my comment above, it isn't sufficient to run the 2 queries, you then need to run all the alter table statements that these have generated. However you could do it all at once using PL/SQL. I have combined the 2 queries into one, using order by to process the foreign keys (constraint_type = 'R') first:

begin
  for r in
    ( select 'alter table '||table_name||' disable constraint '||constraint_name as statement
      from user_constraints
      where status = 'ENABLED'
      order by case constraint_type when 'R' then 1 else 2 end
    )
  loop
    execute immediate r.statement;
  end loop;
end;