Tuesday, August 09, 2005

Drop Constraints.

Once i had faced problem in one table which have more than 287 duplicate constraints.

So i had buid one solution as below.

I had create one script and saved in c:\ REMOVECONSTRAINT.sql.

Contains of the script REMOVECONSTRAINT.sql as below.
SET HEADING OFF
SPOOL C:\DROPCONSTRAINT.SQL
select 'alter table 'OWNER'.'table_name' drop constraint 'constraint_name' cascade;'
from dba_constraints where owner=UPPER('&owner') and table_name=UPPER('&table_name')
/
SPOOL OFF;
@C:\DROPCONSTRAINT.SQL

if you go through with script you come to know it will generate dropconstraint.sql and execute it.

This script will remove all constraint from table.
After this you need to Re-create constraints your constraints.

No comments: