oracle disable/enable all constraints and triggers

disable script:

DECLARE
BEGIN
  FOR i IN
  (SELECT table_name,
    constraint_name --disable first the foreign key
     FROM user_constraints
    WHERE constraint_type ='R'
  AND status              = 'ENABLED'
  )
  LOOP
    EXECUTE IMMEDIATE 'alter table "' ||i.table_name|| '" disable constraint ' ||i.constraint_name;
  END LOOP i;
  FOR i IN
  (SELECT table_name,
    constraint_name -- then disable all constraints
     FROM user_constraints
    WHERE status = 'ENABLED'
  )
  LOOP
    EXECUTE IMMEDIATE 'alter table "' ||i.table_name|| '" disable constraint ' ||i.constraint_name;
  END LOOP i;
END;
/
DECLARE
BEGIN
  FOR i IN
  (SELECT table_name FROM user_tables
  )
  LOOP
    EXECUTE IMMEDIATE 'alter table "' ||i.table_name|| '" disable all triggers';
  END LOOP i;
END;
/

enable script:

DECLARE
BEGIN
  FOR i IN
  (SELECT table_name,
    constraint_name --enable first the primary key
     FROM user_constraints
    WHERE constraint_type ='P'
  AND status              = 'DISABLED'
  )
  LOOP
    EXECUTE IMMEDIATE 'alter table "' ||i.table_name|| '" enable constraint ' ||i.constraint_name;
  END LOOP i;
  FOR i IN
  (SELECT table_name,
    constraint_name -- then enable all constraints
     FROM user_constraints
    WHERE status = 'DISABLED'
  )
  LOOP
    EXECUTE IMMEDIATE 'alter table "' ||i.table_name|| '" enable constraint ' ||i.constraint_name;
  END LOOP i;
END;
/
DECLARE
BEGIN
  FOR i IN
  (SELECT table_name FROM user_tables
  )
  LOOP
    EXECUTE IMMEDIATE 'alter table "' ||i.table_name|| '" enable all triggers';
  END LOOP i;
END;
/

最后

以上就是无语绿茶为你收集整理的oracle disable/enable all constraints and triggers的全部内容，希望文章能够帮你解决oracle disable/enable all constraints and triggers所遇到的程序开发问题。

如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。