Help with sql identifying dup constraints 2004-06-09 - By Barbara Baker
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 (See ORA-00997.ora-code.com): 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@(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
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- --
|
|
