This content has been marked as final. Show 3 replies
There is no such command, for obvious reasons.
Also best practice is not to load directly in the target table, but instead use a staging table without constraints, clean up the staging table, and then copy to the target table.
Disabling all constraints could also cause big trouble if you are not the only one performing DML on that table.
Senior Oracle DBA
No there is no universal constraint disablement. What you can do is loop round the data dictionary view and use dynamic SQL:
Note that this only does the relational constraints. Remove the filter on CONSTRAINT_TYPE if you want to include check constraints as well.
begin for all_cons in ( select table_name, constraint_name from user_constraints where constraint_type in ('U', 'P', 'R') ) loop execute immediate 'alter table '||all_cons||' disable constraint '||all_cons.constraint_name; end loop; end;
I whole-heartedly endorse Sybrand's warnings. You need to ensure you are the only user of the database when you do this loading.
Edited by: APC on Mar 28, 2013 1:59 PM
In addition to the suggestion made by Sybrand, you would have a problem to "clean up" the new data which has been directly added to the table(instead of using a staging table) if the constraints fail to get enabled later. MORE the data BIGGER the cleanup !