Help with sql identifying dup constraints
- From: Barbara Baker <barbarabbaker@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Wed, 9 Jun 2004 13:07:49 -0700 (PDT)
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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
