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