code_junkie

Disable all table constraints in Oracle

Nov 11th, 2011
98
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 2.03 KB | None | 0 0
  1. BEGIN
  2. FOR c IN
  3. (SELECT c.owner, c.table_name, c.constraint_name
  4. FROM user_constraints c, user_tables t
  5. WHERE c.table_name = t.table_name
  6. AND c.status = 'ENABLED'
  7. ORDER BY c.constraint_type DESC)
  8. LOOP
  9. dbms_utility.exec_ddl_statement('alter table ' || c.owner || '.' || c.table_name || ' disable constraint ' || c.constraint_name);
  10. END LOOP;
  11. END;
  12. /
  13.  
  14. BEGIN
  15. FOR c IN
  16. (SELECT c.owner, c.table_name, c.constraint_name
  17. FROM user_constraints c, user_tables t
  18. WHERE c.table_name = t.table_name
  19. AND c.status = 'DISABLED'
  20. ORDER BY c.constraint_type)
  21. LOOP
  22. dbms_utility.exec_ddl_statement('alter table ' || c.owner || '.' || c.table_name || ' enable constraint ' || c.constraint_name);
  23. END LOOP;
  24. END;
  25. /
  26.  
  27. set heading off
  28.  
  29. spool drop_constraints.out
  30.  
  31. select
  32. 'alter table ' ||
  33. owner || '.' ||
  34. table_name ||
  35. ' drop constraint ' ||
  36. constraint_name || ';'
  37. from
  38. user_constraints;
  39.  
  40. spool off
  41.  
  42. set heading on
  43.  
  44. @drop_constraints.out
  45.  
  46. SET Serveroutput ON
  47. BEGIN
  48. FOR c IN
  49. (SELECT c.owner,c.table_name,c.constraint_name
  50. FROM user_constraints c,user_tables t
  51. WHERE c.table_name=t.table_name
  52. AND c.status='ENABLED'
  53. ORDER BY c.constraint_type DESC,c.last_change DESC
  54. )
  55. LOOP
  56. FOR D IN
  57. (SELECT P.Table_Name Parent_Table,C1.Table_Name Child_Table,C1.Owner,P.Constraint_Name Parent_Constraint,
  58. c1.constraint_name Child_Constraint
  59. FROM user_constraints p
  60. JOIN user_constraints c1 ON(p.constraint_name=c1.r_constraint_name)
  61. WHERE(p.constraint_type='P'
  62. OR p.constraint_type='U')
  63. AND c1.constraint_type='R'
  64. AND p.table_name=UPPER(c.table_name)
  65. )
  66. LOOP
  67. dbms_output.put_line('. Disable the constraint ' || d.Child_Constraint ||' (on table '||d.owner || '.' ||
  68. d.Child_Table || ')') ;
  69. dbms_utility.exec_ddl_statement('alter table ' || d.owner || '.' ||d.Child_Table || ' disable constraint ' ||
  70. d.Child_Constraint) ;
  71. END LOOP;
  72. END LOOP;
  73. END;
  74. /
Add Comment
Please, Sign In to add comment