Ya, the first query gave me some unexpected results.
But this one is returning both constraints! Just what
I was after.
Thank you so very much for taking the time and effort
to put this query together for me.
You 've come through for me several times in the past.
I really do appreciate your willingness to share your
expertise.
Can 't thank you enough!
Regards,
Barb
--- Jacques Kilchoer <Jacques.Kilchoer@(protected) >
wrote:
> 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?
>
__ ____ ____ ____ ____ ____ ______
Do you Yahoo!?
Friends. Fun. Try the all-new Yahoo! Messenger.
http://messenger.yahoo.com/
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ------
To unsubscribe send email to: oracle-l-request@(protected)
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
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- --
