Re: Help with sql identifying dup constraints

For the past few days, I have been waging war with dbms_metadata (and I'm still standing!). One of the issues I had to deal with was the incorrect parsing of a view for output into a ddl call. Finally got that a workaround coded for that one this morning. Whew!

Okay, so what does this have to do with Barb's problem? (Barb, you should stop reading now if you don't want to run up a serious tab at Pint's Pub). dba_views.text is a long...dba_constraints.search_condition is a long. I needed to perform character operations on the text, so I needed to convert it to a character string...not possible in one step. However, if you create a table (gtt in this case) where the text is stored as a CLOB, you can use the TO_LOB function on the insert to perform this conversion. Once you convert it to a CLOB, you can use TO_CHAR and apply character functions. Including = in a predicate!

Here you go...it works for me..in a 9i db. (8i has clobs, but to_char does not appear to be able to handle them). If you are on 8i, sorry...

create global temporary table gtt_constraints
( owner varchar2(30),
  table_name varchar2(30),
  constraint_name varchar2(30),
  search_condition clob)
on commit preserve rows
/

insert into gtt_constraints (select owner, table_name, constraint_name, 
to_lob(search_condition) from dba_constraints)
/

commit
/

select count(*) from gtt_constraints
/

set linesize 132 pages 45 feedback off
col owner format a15
col table_name format a30
col search_condition format a40 word_wrap
col constraint_name1 format a15
col constraint_name2 format a15


select g1.owner, g1.table_name, g1.search_condition, g1.constraint_name constraint_name1, g2.constraint_name constraint_name1 from gtt_constraints g1, gtt_constraints g2 where g1.owner = g2.owner and g1.table_name = g2.table_name and to_char(g1.search_condition) = to_char(g2.search_condition) and g1.constraint_name != g2.constraint_name /



and the output...

SQL> @get_dup_constraints
SQL> create global temporary table gtt_constraints
  2  ( owner varchar2(30),
  3    table_name varchar2(30),
  4    constraint_name varchar2(30),
  5    search_condition clob)
  6  on commit preserve rows
  7  /
SQL>
SQL> insert into gtt_constraints (select owner, table_name, constraint_name, 
to_lob(search_condition) from dba_constraints)
  2  /
SQL>
SQL> commit
  2  /
SQL>
SQL> select count(*) from gtt_constraints
  2  /

  COUNT(*)
----------
      2970
SQL>
SQL> set linesize 132 pages 45 feedback off
SQL> col owner format a15
SQL> col table_name format a30
SQL> col search_condition format a40 word_wrap
SQL> col constraint_name1 format a15
SQL> col constraint_name2 format a15
SQL>
SQL>
SQL> select g1.owner, g1.table_name, g1.search_condition, g1.constraint_name 
constraint_name1, g2.constraint_name constraint_name1
  2  from gtt_constraints g1,
  3       gtt_constraints g2
  4  where g1.owner = g2.owner
  5    and g1.table_name = g2.table_name
  6    and to_char(g1.search_condition) = to_char(g2.search_condition)
  7    and g1.constraint_name != g2.constraint_name
  8  /

OWNER           TABLE_NAME                     SEARCH_CONDITION                 
        CONSTRAINT_NAME CONSTRAINT_NAME
--------------- ------------------------------ 
---------------------------------------- --------------- ---------------
SYSTEM          DEF$_PUSHED_TRANSACTIONS       disabled IN ('T', 'F')           
        SYS_C001325     SYS_C00761
SYSTEM          DEF$_PUSHED_TRANSACTIONS       disabled IN ('T', 'F')           
        SYS_C00761      SYS_C001325




Barbara Baker wrote:
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 http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

---------------------------------------------------------------- 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 http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------

Other related posts: