Menu
How can I disable all table constrains in Oracle with a single command? This can be either for a single table, a list of tables, or for all tables.
900
You can use the ALTER TABLE statement to enable, disable, modify, or drop a constraint. When the database is using a UNIQUE or PRIMARY KEY index to enforce a constraint, and constraints associated with that index are dropped or disabled, the index is dropped, unless you specify otherwise.
Best Answer 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; Note that this only does the relational constraints.
There are multiple ways to disable constraints in Oracle. constraint_name; Another way to enable and disable constraints in Oracle would be to either use a plsql block or write a script. Execute Immediate 'alter table '||:tab_name||' disable constraint '||tabCons(numCount);
You can actually disable all database constraints in a single SQL command and the re-enable them calling another single command.
It is better to avoid writing out temporary spool files. Use a PL/SQL block. You can run this from SQL*Plus or put this thing into a package or procedure. The join to USER_TABLES is there to avoid view constraints.
It's unlikely that you really want to disable all constraints (including NOT NULL, primary keys, etc). You should think about putting constraint_type in the WHERE clause.
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; / Enabling the constraints again is a bit tricker - you need to enable primary key constraints before you can reference them in a foreign key constraint. This can be done using an ORDER BY on constraint_type. 'P' = primary key, 'R' = foreign key.
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; / If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
