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';

Comments