2017  Kodetalk | Feedback | Privacy Policy | Terms | About
userimage

Disable all table constraints in Oracle

I want to disable all the constrains in my database with single query.I don"t want to pass table name,constraint name etc to my query.

Please help any one.

userimage

To do this task you need to write a procedure.

BEGIN

  FOR i 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 "' || i.owner || '"."' || i.table_name || '" disable constraint ' || i.constraint_name);

  END LOOP;

 

Other wise you can use below process it is not a single query but can be useful.


select

    'alter table ' ||     owner || '.' ||     table_name ||     ' disable constraint ' ||

-- or 'drop' if you want to permanently remove

    constraint_name || ';' from     user_constraints;

It will give some statements.

copy the result and run it.

Answer is