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@(protected)
[mailto:oracle-l-bounce@(protected)
<mailto:oracle-l-bounce@(protected) > ] 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?
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN " >
<HTML > <HEAD > <TITLE > </TITLE >
<META http-equiv=Content-Type content= "text/html; charset=us-ascii " >
<META content= "MSHTML 6.00.2800.1400 " name=GENERATOR > </HEAD >
<BODY >
<DIV dir=ltr align=left > <FONT size=2 > <FONT size=3 > <SPAN
class=615013801-10062004 > <FONT size=2 >Someone pointed out to me in an e-mail
that my previous proposal for a solution to the problem made the
(incorrect) assumption </FONT > <FONT size=2 >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: </FONT > </SPAN > </FONT > </FONT > </DIV >
<DIV dir=ltr align=left > <FONT size=2 > <SPAN class=615013801-10062004 > </SPAN > <SPAN
class=615013801-10062004 > <FONT size=3 > </FONT > </SPAN > </FONT > </DIV >
<DIV dir=ltr align=left > <FONT > <SPAN class=615013801-10062004 > <FONT
face= "Courier New " size=2 >select <BR > a.owner || '. ' || a.table_name
|| '. ' || b.column_name as constraint_column, <BR > a.constraint_name
as ck_constraint, <BR > a.search_condition as
ck_text <BR > from <BR > dba_constraints a, dba_cons_columns
b <BR > where <BR > -- ** insert your where clauses
here <BR > a.owner = user <BR > -- ** <BR > and
a.constraint_type = 'C ' <BR > and b.owner = a.owner <BR >
and b.constraint_name = a.constraint_name <BR > and 1 = (select count
(*) <BR >
from dba_cons_columns
c <BR >
where c.owner =
a.owner <BR >
and c.constraint_name =
a.constraint_name <BR >
) <BR > and exists (select
null <BR >
from dba_constraints d, dba_cons_columns
e <BR >
where d.owner =
a.owner <BR >
and d.table_name =
a.table_name <BR >
and d.constraint_type =
'C ' <BR >
and d.constraint_name !=
a.constraint_name <BR >
and 1 = (select count
(*) <BR >
from dba_cons_columns
f <BR >
where f.owner =
d.owner <BR >
and f.constraint_name =
d.constraint_name <BR >
) <BR >
and e.owner =
d.owner <BR >
and e.constraint_name =
d.constraint_name <BR >
and e.column_name =
b.column_name <BR >
) <BR >order by 1, 2 ; </FONT > </SPAN > </FONT > </DIV >
<DIV dir=ltr align=left > <FONT size=2 > <SPAN
class=615013801-10062004 > </SPAN > <BR >-- --Original Message-- -- <BR >From:
oracle-l-bounce@(protected) [ <A
href= "mailto:oracle-l-bounce@(protected) " > <FONT
color=#000000 >mailto:oracle-l-bounce@(protected) </FONT > </A >] On Behalf Of
Barbara Baker <BR > <BR >I appear to have a bit of a mess on my hands.
I 've <BR >identified some tables that have a duplicate "not <BR >null " constraint on
the same column. Only difference <BR >in the constraints is that one is
generated and one is <BR >user named (even tho they 're both
sys_c00xxx <BR >constraints). <BR > <BR >(I believe this happened when a vendor used a
3rd <BR >party pkg to try to duplicate their schema in
our <BR >database.) <BR > <BR >I 'd like to identify all of the tables with
this <BR >condition. Any method I can think to do this requires <BR >comparing the
search condition of dba_constraints, <BR >which is a LONG. <BR > <BR >Can anyone think
of a way to do this? </FONT > </DIV > </BODY > </HTML >
