This is a very simple script I developed to enable all disabled constaints for any schema in the database using EXECUTE IMMEDIATE (for dynamic execution of the DDL statement) and also the invoker's right option in PL/SQL. This means that execution is based on invoker right; all that is required is for the owner of the object to grant an execution privilege on the object and also create a public synonym on that object, which is optional. I developed this script because frankly, I don't know if Oracle has an inbuilt package to enable all constraints for a schema, something similar to dbms_utility.compile_schema for compiling all invalid objects. I hope you will find these useful.
CREATE OR REPLACE PROCEDURE pr_enable_constraint AUTHID CURRENT_USER /* ** AUTHOR : AKINODE BABATUNDE (OCP DBA) ** ** ** PURPOSE : DYNAMICALLY ENABLING CONSTRAINTS IN ANY SCHEMA ** ** ** DATE : 20-MAY-2003 ** ** */ AS -- CURSOR cr_db_objects IS SELECT constraint_name,table_name FROM all_constraints WHERE owner = USER AND status = 'DISABLED' ORDER BY DECODE(r_constraint_name,NULL,1,2); -- v_object_name VARCHAR2(40); v_object_tab VARCHAR2(40); v_exec_count NUMBER := 0; -- PROCEDURE pr_execute IS -- v_sql_stmnt VARCHAR2(300); -- BEGIN -- v_sql_stmnt := 'ALTER TABLE '||v_object_tab||' ENABLE CONSTRAINT '||v_object_name; EXECUTE IMMEDIATE v_sql_stmnt; -- EXCEPTION WHEN OTHERS THEN NULL; END; -- BEGIN -- WHILE v_exec_count <= 2 LOOP -- FOR cur IN cr_db_objects LOOP -- v_object_name := cur.constraint_name; v_object_tab := cur.table_name; pr_execute; -- END LOOP; -- v_exec_count := v_exec_count + 1; -- END LOOP; -- EXCEPTION WHEN NOT_LOGGED_ON THEN RAISE_APPLICATION_ERROR (-20999 ,SQLERRM); -- WHEN OTHERS THEN RAISE_APPLICATION_ERROR (-20999, SQLERRM ); -- END pr_enable_constraint;
For More Information
- Feedback: E-mail the editor with your thoughts about this tip.
- More tips: Hundreds of free Oracle tips and scripts.
- Tip contest: Have an Oracle tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize -- submit your tip today!
- Ask the Experts: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.
- Forums: Ask your technical Oracle questions--or help out your peers by answering them--in our active forums.
- Best Web Links: Oracle tips, tutorials, and scripts from around the Web.