Disable and Enable all Constraint

-- Disable and Enable Constraint

-- Option#1

-- Disable Constraint

CREATE OR REPLACE PROCEDURE DISABLE_ALL_CONSTRAINT

IS

BEGIN

-- Disable foreign key constraint

for i IN (select table_name, constraint_name

from user_constraints

where constraint_type ='R'

and status = 'ENABLED')

loop

EXECUTE IMMEDIATE 'alter table ' ||i.table_name|| ' disable constraint ' ||i.constraint_name;

end loop i;

-- Disable rest of the constraint

for i IN (select table_name, constraint_name

from user_constraints

where status = 'ENABLED')

loop

EXECUTE IMMEDIATE 'alter table ' ||i.table_name|| ' disable constraint ' ||i.constraint_name;

end loop i;

END;

/

execute DISABLE_ALL_CONSTRAINT;

-- Enable Constraint

CREATE OR REPLACE PROCEDURE ENABLE_ALL_CONSTRAINT

IS

BEGIN

-- Enable all constraint except foreign key

for i IN (select table_name, constraint_name

from user_constraints

where status = 'DISABLED'

and constraint_type!='R'

)

loop

EXECUTE IMMEDIATE 'alter table ' ||i.table_name|| ' enable novalidate constraint ' ||i.constraint_name;

end loop i;

-- Enable foreign key constraint

for i IN (select table_name, constraint_name

from user_constraints

where constraint_type ='R'

and status = 'DISABLED')

loop

EXECUTE IMMEDIATE 'alter table ' ||i.table_name|| ' enable novalidate constraint ' ||i.constraint_name;

end loop i;

END;

/

execute ENABLE_ALL_CONSTRAINT;

-- Option#2

-- Disable Constraint

-- Disable foreign key

select 'alter table '||table_name||' disable constraint '||constraint_name||';'

from user_constraints

where constraint_type ='R'

and status = 'ENABLED';

-- Disable rest of the constraint

select 'alter table '||table_name||' disable constraint '||constraint_name||';'

from user_constraints

where status = 'ENABLED';

-- Enable Constraint

-- enable all constraint except foreign key

select 'alter table '||table_name||' enable novalidate constraint '||constraint_name||';'

from user_constraints

where constraint_type !='R'

and status = 'DISABLED';

-- Enable foreign key constraint

select 'alter table '||table_name||' enable novalidate constraint '||constraint_name||';'

from user_constraints

where constraint_type ='R'

and status = 'DISABLED';