-
Hi all,
Friday I did an import (high import) that failed sometimes but finally runs "correctly". Now I have detected that there are some tables with duplicated check constraints and I want to know all duplicated check constraints on the database to drop it.
Does anybody how to do it?
Thanks in advance and best regards
Angel
P.S. Database Oracle 8.1.7.1.1 on Windows NT
-
cant you check user_constraints.SEARCH_CONDITION?
-
Yes, but the problem is this check constraint haven´t name, only sys00xxxx... Then is difficult to know wich are duplicated.
Thanks anyway Pando
-
quoted from asktom
Code:
create function search_cond( p_cons_name in varchar2 ) return varchar2
as
l_search_condition varchar2(4000);
begin
select SEARCH_CONDITION into l_search_condition
from user_constraints
where constraint_name = p_cons_name;
return l_search_condition;
end;
/
and then
select search_cond_vc, count(*)
from (
select search_cond( constraint_name ) search_cond_vc
from user_constraints
where constraint_name like 'SYS%'
)
group by search_cond_vc
having count(*) > 1;
The reason of using a function is to convert long to varchar2 otherwise we cannot compare data
-
Thank you very much Pando. I will try it.
Regards
Angel
-
Hi again,
The result of the above query is the search_condition and the number of times it is duplicated, but it not tell the table_name ...
Do you know how could I compare with a long type:
select owner,constraint_name,constraint_type,table_name
from user_constraints
where search_condition like 'ANO IS NOT NULL';
Where ANO IS NOT NULL is the SEARCH_CONDITION from USER_CONSTRAINTS.
Thanks in advance and best regards
Angel
-
Code:
select table_name
from user_constraints where
search_cond(constraint_name)='"ANO" IS NOT NULL';
-
Thanks for your quickly reply Pando, but it doesn't work:
SQL> select table_name
2 from user_constraints where
3 search_condition='"ANO" IS NOT NULL';
search_condition='"ANO" IS NOT NULL'
*
ERROR en lĂ*nea 3:
ORA-00997: uso no válido del tipo de datos LONG
(invalid use of datatype LONG)
(I think you understand spanish no? :-) )
Regards
Angel
-
it works if you sue the function I posted yesterday and execute the query I posted
example:
Code:
SQL> r
1 select table_name, search_cond( constraint_name ) search_cond_vc
2 from user_constraints
3* where constraint_name like 'SYS%'
TABLE_NAME SEARCH_COND_VC
------------------------------ ----------------------------------------
CON "OWNER#" IS NOT NULL
CON "NAME" IS NOT NULL
CON "CON#" IS NOT NULL
EMP "EMPNO" IS NOT NULL
MD_EJECUCION_PROCESO "ID_EJECUCION_PROCESO" IS NOT NULL
MV_FASTDEPTEMP "EMPNO" IS NOT NULL
MV_TEST "EMPNO" IS NOT NULL
SQL>
1 select table_name, search_condition
2 from user_constraints where
3* search_cond(constraint_name)='"EMPNO" IS NOT NULL'
TABLE_NAME SEARCH_CONDITION
------------------------------ --------------------------------------------------------------------------------
EMP "EMPNO" IS NOT NULL
MV_FASTDEPTEMP "EMPNO" IS NOT NULL
MV_TEST "EMPNO" IS NOT NULL
spanish yes
-
Thanks again Pando. It works fine ;-)
Regards
Angel
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|