RE: Help with sql identifying dup constraints
- From: "Jacques Kilchoer" <Jacques.Kilchoer@xxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 9 Jun 2004 18:41:17 -0700
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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx
<mailto:oracle-l-bounce@xxxxxxxxxxxxx> ] 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?
Other related posts:
