RE: Help with sql identifying dup constraints

From: Jacques Kilchoer <Jacques.Kilchoer_at_quest.com>
Date: Wed, 9 Jun 2004 18:41:17 -0700
Message-ID: <B5C5F99D765BB744B54FFDF35F60262109F878D8@irvmbxw02>

Someone pointed out to me in an e-mail that my previous proposal for a solution to the problem made the (incorrect) assumption that one constraint was a not null constraint and the other a check constraint.
(That is the situation I had run into before.) It is more likely that
they are both check constraints. Then instead I would propose this query to attempt to identify those "duplicate" constraints:  

select

   a.owner || '.' || a.table_name || '.' || b.column_name as constraint_column,

   a.constraint_name as ck_constraint,
   a.search_condition as ck_text
 from
   dba_constraints a, dba_cons_columns b  where

• ** insert your where clauses here a.owner = user
• ** and a.constraint_type = 'C' and b.owner = a.owner and b.constraint_name = a.constraint_name and 1 = (select count (*) from dba_cons_columns c where c.owner = a.owner and c.constraint_name = a.constraint_name ) and exists (select null from dba_constraints d, dba_cons_columns e where d.owner = a.owner and d.table_name = a.table_name and d.constraint_type = 'C' and d.constraint_name != a.constraint_name and 1 = (select count (*) from dba_cons_columns f where f.owner = d.owner and f.constraint_name = d.constraint_name ) and e.owner = d.owner and e.constraint_name = d.constraint_name and e.column_name = b.column_name ) order by 1, 2 ;

-----Original Message-----

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org
<mailto:oracle-l-bounce_at_freelists.org> ] On Behalf Of Barbara Baker

I appear to have a bit of a mess on my hands. I've identified some tables that have a duplicate "not null" constraint on the same column. Only difference in the constraints is that one is generated and one is user named (even tho they're both sys_c00xxx constraints).

(I believe this happened when a vendor used a 3rd
party pkg to try to duplicate their schema in our database.)

I'd like to identify all of the tables with this condition. Any method I can think to do this requires comparing the search condition of dba_constraints, which is a LONG.

Can anyone think of a way to do this?

---

Please see the official ORACLE-L FAQ: http://www.orafaq.com

---

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Wed Jun 09 2004 - 20:38:33 CDT