Help with sql identifying dup constraints

From: Barbara Baker <barbarabbaker_at_yahoo.com>
Date: Wed, 9 Jun 2004 13:07:49 -0700 (PDT)
Message-ID: <20040609200749.64922.qmail@web50608.mail.yahoo.com>

Hi, all.
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? Perhaps I'm overlooking something simple. Thanks for any help. Barb

SYSTEM:ENT>select a.constraint_name,

  2         b.constraint_name,
  3         a.table_name,
  4         a.search_condition,
  5         b.search_condition
  6  from dba_constraints a,
  7       dba_constraints b
  8  where a.table_name  = b.table_name

9 and a.search_condition=b.search_condition 10 and a.table_name = 'ACTUALPAGES'
11 /
and a.search_condition=b.search_condition

*
ERROR at line 9:
ORA-00997: illegal use of LONG datatype

> select constraint_name, constraint_type,
search_condition, generated f
rom user_constraints where table_name='ACTUALPAGES';

  Constraint              Search
     Name      C         Condition         GENERATED
-------------- - -------------------------
--------------
SYS_C0010088   C "PAPER" IS NOT NULL       USER NAME
SYS_C0010089   C "PDATE" IS NOT NULL       USER NAME
SYS_C0013708   C "PAPER" IS NOT NULL       GENERATED
NAME
SYS_C0013709   C "PDATE" IS NOT NULL       GENERATED
NAME
                



	
		
__________________________________

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 - 15:04:43 CDT