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 /
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_C001325Hi, 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
-----------------------------------------------------------------
---------------------------------------------------------------- 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 -----------------------------------------------------------------
