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内容请搜索靠谱客的其他文章。