Disable and Enable All table constraints in Oracle

8/31/2012 04:22:00 PM

Sometimes you have to do something that requires disable all constraints, like upload Data, and enable them again after do it.

I've found two useful pl/sql code that will do it, but it will only work if all your constraints are enabled, if there is one that is not enabled, at the end of the process it will be enabled. if you have not that problem, check the useful pl/sql here

Disable all table constraints in Oracle

If you don't know if there are disabled constraints, you can find the answer running the next query, if it doesn't return data, all your constraints are enabled and you can use the pl/sql in the adobe link

SELECT constraint_name
   FROM user_constraints
   WHERE status = 'DISABLED'

If you have disabled constraints that you don't need, you can delete them and use the adove link, but sometimes we have disabled constraints because the aplication is not 100% complete or something, anyway, those are constraints that you'll need in the future and you can't delete them, if that is the case, use the next pl/sql block that is just an alteration of the one in the pl/sql in the adobe link.
This new pl/sql search the enabled constraints and generate the code to enable them again and after uses the same procedure to disable all the constraints.

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)
  LOOP
 htp.p('alter table "' || c.owner || '"."' || c.table_name || '" enable constraint ' || c.constraint_name ||';');
  END LOOP;

  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)
  LOOP
    dbms_utility.exec_ddl_statement('alter table "' || c.owner || '"."' || c.table_name || '" disable constraint ' || c.constraint_name);
  END LOOP;
    dbms_utility.exec_ddl_statement('/');
END;
/

This is for use with the Apex sql console, and it will print the script, to run it create a new script file and paste there the generated code, then run it when you need enable the constraints again :D

If you will use it with sqlplus you have to replace the htp.p by dbms_output.put_line and run these commands before run the pl/sql block.

Spool on
Spool C:\constraints.sql
set serveroutput on

Replace the directory in the second line to the place where you want the script be generated. after run the pl/sql, you can find the script in the directory you set. and when you need it, run it.

Now, it is over, I hope this be useful for you, if it was the celebrate like Kashiyuka does :P, enjoy the video below and comment if this was useful.