Sql 禁用Oracle中的所有表约束

最好避免写出临时假脱机文件.使用PL/SQL块.您可以从SQL*Plus运行它，或者将它放入一个包或过程中.连接到用户_表是为了避免视图约束.

您不太可能真的想要禁用所有约束(包括NOTNULL、主键等).您应该考虑在WHERE子句中添加约束类型.

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'
   AND NOT (t.iot_type IS NOT NULL AND c.constraint_type = 'P')
   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;
END;
/

再次启用约束有点难——在外键约束中引用它们之前，需要启用主键约束.这可以通过使用"按约束类型排序"来完成P'=主键，R'=外键.

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 = 'DISABLED'
   ORDER BY c.constraint_type)
  LOOP
    dbms_utility.exec_ddl_statement('alter table "' || c.owner || '"."' || c.table_name || '" enable constraint ' || c.constraint_name);
  END LOOP;
END;
/