Enable or Disable Oracle constraints..

Created by Tech Team on Fri, 2013-10-25 00:44

If you want to enable or disable all constraints which is normally done in datawarehousing jobs you are likely to get two errors ORA-02270: no matching unique or primary key for this column-list or ORA-02297 cannot disable constraint (SYS_C0010965) - dependencies.

To avoind this you should enable or disable constraint in a order. To disable all constraints use "order by constraint_type desc" ie. disable foreign ket first and then Primary key. Likewise if you are enabling do it in reverse order. for eg. "order by constraint_type"

DISABLE constraints

for i in (select 'ALTER TABLE '||ac.owner||'.'||ac.table_name||' DISABLE CONSTRAINT '
             ||constraint_name  as sql_string,constraint_name,ac.table_name
          from all_constraints ac,all_tables at
          where ac.table_name = at.table_name
          and ac.owner=at.owner
          and ac.owner=p_owner order by constraint_type desc) loop
  --dbms_output.put_line(current_timestamp||':'||i.sql_string);
  
 execute immediate i.sql_string;
  --dbms_output.put_line( 'Constraint '||constraint_name ||' on '||table_name ||' is ' || ' Disabled.');
 
end loop; 
 
-- Enable 
for i in (select 'ALTER TABLE '||ac.owner||'.'||ac.table_name||' ENABLE novalidate CONSTRAINT  '||
                  constraint_name  as sql_string,constraint_name,ac.table_name
          from all_constraints ac ,all_tables at
          where ac.table_name = at.table_name
          and ac.owner=at.owner
          and ac.owner=p_owner  order by constraint_type) loop
         dbms_output.put_line(i.sql_string);
         execute immediate i.sql_string;
  
  end loop;