Jacques:
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_at_quest.com>
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_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?
>
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_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 - 21:43:50 CDT
