Find and Delete duplicate check Constraints

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

-- Find duplicate check constraints in current user's schema that have Oracle

-- generated names (like 'SYS_C%'). A script (c:\drop_duplicate_constraints.sql)

-- is generated to drop those duplicates.

--

-- This removes those duplicated constraints that were probably created by

-- improper usage of the Oracle imp utility. Using CONSTRAINTS=N will not

-- create duplicate constraints when importing into an existing table.

--

-- Tables js_constraints and js_constraints_min are dropped and creaed

-- the current user's schema, drop them to clean up.

--

-- WARNING: Review this and the generated script

-- c:\drop_duplicate_constraints.sql before running it at YOUR RISK.

-- I do not accept any responsibility for what YOU DO TO YOUR DATABASES !

--

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

set linesize 120

set pagesize 50000

set timing on

drop table js_constraints

/

create table js_constraints

(

owner varchar2(30),

constraint_name varchar2(30),

table_name varchar2(30),

search_condition varchar2(2000)

)

/

declare

cursor js_cursor is

select

owner,

table_name,

constraint_name,

search_condition

from user_constraints

where owner = user

and constraint_type = 'C'

and constraint_name like 'SYS_C%';

js_rec js_cursor%rowtype;

commit_interval constant integer := 10000;

records_processed integer := 0;

begin

open js_cursor;

loop

fetch js_cursor into js_rec;

exit when js_cursor%notfound;

insert into js_constraints (

owner,

table_name,

constraint_name,

search_condition)

values (

js_rec.owner,

js_rec.table_name,

js_rec.constraint_name,

substr(js_rec.search_condition,1,2000)

);

records_processed := records_processed + 1;

if records_processed = commit_interval then

commit;

records_processed := 0;

end if;

end loop;

commit;

close js_cursor;

end;

/

create index js_constraints_x1 on js_constraints (owner, table_name, search_condition)

/

drop table js_constraints_min

/

create table js_constraints_min as

select

owner,

table_name,

search_condition,

min(translate(constraint_name, 'SYC_', '9999')) as min_constraint_name,

'at least 30 dummy characters !!!' as constraint_name

from js_constraints

group by owner, table_name, search_condition

/

update js_constraints_min cm

set constraint_name = (select constraint_name from js_constraints c

where c.owner = cm.owner

and c.table_name = cm.table_name

and translate(c.constraint_name, 'SYC_', '9999') = cm.min_constraint_name)

/

delete from js_constraints

where (owner, table_name, search_condition, translate(constraint_name, 'SYC_', '9999')) in

(select owner, table_name, search_condition, min_constraint_name

from js_constraints_min)

/

-------spool c:\js_drop_duplicate_constraints.sql

select

'alter table ' || c.owner || '.' || c.table_name ||

' drop constraint ' || c.constraint_name ||

' --duplicate of ' || cm.constraint_name || chr(13) || '/'

from js_constraints c, js_constraints_min cm

where c.owner = cm.owner

and c.table_name = cm.table_name

and c.search_condition = cm.search_condition

order by c.owner, c.table_name, cm.min_constraint_name, c.constraint_name

/

spool off