| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> 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
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,
-- 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 16 2004 - 15:57:33 CDT
 |
 |