Recently I aided in the migration of some data in Oracle tables from a development environment to a test environment. The tables already existed in the test environment, and had constraints on several of the tables that was impeding the migration effort.  I decided to disable the constraints, but there were a lot of tables and several had multiple constraints on them.  So I came up with a query, with the aid of my co-worker, to generate the ALTER statement needed to disable the constraints on a particular table.

SELECT ‘alter table’ || TABLE_NAME || ‘DISABLE constraint’ || CONSTRAINT_NAME || ‘;’
FROM all_constraints
WHERE r_constraint_name IN
  (SELECT constraint_name
  FROM all_constraints
  WHERE table_name = ‘TABLE1’ AND status = ‘ENABLED’);

This will result in statements being created like the following if you have a constraint on TABLE1 called TB_BTRN_FK.

alter table TABLE1 DISABLE constraint TB_BTRN_FK

You can then run this statement to disable the constraint.  You can also keep track of the constraints you disabled if you save all the statements, which could help when you need to go re-enable them.

Like this post? Share it!