Disable_Truncatetable_Enable_constraints

Disable_Truncatetable_Enable_constraints

Disable Foreign Key and local constraints and truncate data - and Re-enable all constraints 

begin

for cur in (select fk.owner, fk.constraint_name , fk.table_name From All_Constraints Fk, All_Constraints Pk where fk.CONSTRAINT_TYPE = 'R' and Pk.Owner = '&OWNER' And fk.r_owner = pk.owner AND fk.R_CONSTRAINT_NAME = pk.CONSTRAINT_NAME and Pk.Table_Name = '&Table_NAME') Loop

execute immediate 'ALTER TABLE "'||cur.owner||'"."'||cur.table_name||'" MODIFY CONSTRAINT "'||cur.constraint_name||'" DISABLE';

End Loop;

End;

/

Begin

For Cur In (Select Owner, Constraint_Name , Table_Name From All_Constraints where owner = '&OWNER' and TABLE_NAME = '&Table_NAME') loop

execute immediate 'ALTER TABLE '||cur.owner||'.'||cur.table_name||' MODIFY CONSTRAINT "'||cur.constraint_name||'" DISABLE ';

End Loop;

End;

/

-- Truncate Table OWNER.table_name;

/

Begin

For Cur In (Select Owner, Constraint_Name , Table_Name From All_Constraints where owner = '&OWNER' and TABLE_NAME = '&Table_NAME') loop

execute immediate 'ALTER TABLE '||cur.owner||'.'||cur.table_name||' MODIFY CONSTRAINT "'||cur.constraint_name||'" ENABLE';

End Loop;

End;

/

begin

for cur in (select fk.owner, fk.constraint_name , fk.table_name From All_Constraints Fk, All_Constraints Pk where fk.CONSTRAINT_TYPE = 'R' and Pk.Owner = '&OWNER' And fk.r_owner = pk.owner AND fk.R_CONSTRAINT_NAME = pk.CONSTRAINT_NAME and Pk.Table_Name ='&Table_NAME') Loop

execute immediate 'ALTER TABLE "'||cur.owner||'"."'||cur.table_name||'" MODIFY CONSTRAINT "'||cur.constraint_name||'" ENABLE';

End Loop;

end;

/