I want to know if data exists in the db that would get a constraint violation for all disabled constraints. (if the constraints were enabled)
I get all disabled constraints like this:
select * from all_constraints where STATUS='DISABLED';
Is there a solution?
Advertisement
Answer
I have found a simple solution for me:
spool 'disabled_constraint_violations.txt'; set serverout on size unlimited DECLARE sql_enable varchar2(400); sql_disable varchar2(400); BEGIN FOR c IN (select OWNER, TABLE_NAME, CONSTRAINT_NAME from all_constraints where STATUS='DISABLED') LOOP BEGIN sql_enable := ' alter table ' || c.owner||'.'||c.table_name || ' enable constraint ' || c.constraint_name; execute immediate sql_enable ; sql_disable := ' alter table ' || c.owner||'.'||c.table_name || ' disable constraint ' || c.constraint_name; execute immediate sql_disable ; EXCEPTION WHEN OTHERS THEN dbms_output.put_line('table: '||c.table_name||' || constraint: '||c.constraint_name||' || error message: '||sqlerrm); END; END LOOP; END; set serveroutput off spool off