Duplicate Constraints

From: al1 <allen.prinsloo_at_email.com>
Date: 19 Sep 2006 14:46:09 -0700
Message-ID: <1158702369.339587.301120@b28g2000cwb.googlegroups.com>

Oracle 9i

We have identified numerous duplicate constraints in our database on some tables, I read an article where this is related to exp and imp with unnamed constraints.

My question is how do we identify these duplicate constraints and remove them without having to manually go through every single table, I tried the query below to identify dups, but had no luck as search_condition is a long data type and has restrictions and wont allow a group by.

select count(*) , table_name , search_condition from sys.all_constraints
where owner = 'XXX'
group by table_name , search_condition
having count(*) > 1

Thx In advance Received on Tue Sep 19 2006 - 16:46:09 CDT