« Oracle: info about the corrupted block | Home | Oracle: using substitution variables in sqlplus »
Oracle: disable all constraints referencing the table
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 = '&which_owner' AND
fk.R_CONSTRAINT_NAME = pk.CONSTRAINT_NAME
AND pk.TABLE_NAME = '&which_table'
) loop
execute immediate 'ALTER TABLE '||cur.owner||'.'||cur.table_name||' MODIFY CONSTRAINT '||cur.constraint_name||' DISABLE';
end loop;
end;
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 = '&which_owner' AND
fk.R_CONSTRAINT_NAME = pk.CONSTRAINT_NAME
AND pk.TABLE_NAME = '&which_table'
) loop
execute immediate 'ALTER TABLE '||cur.owner||'.'||cur.table_name||' MODIFY CONSTRAINT '||cur.constraint_name||' DISABLE';
end loop;
end;
Topics: Oracle | Submitter: checkthis
Comments
You must be logged in to post a comment.