Deduplication info/advice needed 
Author Message
 Deduplication info/advice needed
Does anyone have information and tips and tricks for me how to
deduplicate  records on an oracle database? I have to know this for a
project from school.

If anyone can help me please mail at: (or reply)


Sun, 29 Feb 2004 15:45:01 GMT
 Deduplication info/advice needed

Here is the Oracle recommeded strategy I learned in the
Oracle DBA Part 1 Admin and Architecture class.

First, you cannot enable a unique or primary key
constraint on a column with duplicate values. Thus you
will get errors when you try to create/enable such a
constraint. The only choice you have is to manually remove
or update the records so that the required unique columns
are not duplicated. The trick is to find out which columns
are affected. If you simply try to enable the constraint
in SQLPlus, you will find you only get one error at a
time, which you fix, try over, get another and so on,
making for a rather difficult and tedious process.
Instead, Oracle recommends you enable the constraint using
the optional EXEPTIONS INTO [schema].table clause. This
writes errors for all affected rows into a table (that you
will have to create). So you can use the info in this
table to manually fix the problem data.

1. Create the EXCEPTIONS table by executing
2. Execute the ALTER TABLE with EXCEPTIONS clause to
enable a constraint

In case you don't have the utlexcpt.sql script:
CREATE TABLE exceptions (row_id rowid,
                         owner varchar2(30),
                         table_name varchar2(30),
                         constraint varchar2(30));

ALTER TABLE scott.employees
EXCEPTIONS INTO scott.exceptions;

By the way, that usergroup you posted to was for Microsoft
SQL Server, not Oracle, so your post will probably be
moderated out as being off topic.
For oracle user groups, try, or is
the American version. is
the UK oracle users group, and there is a Netherlands
Oracle Users Group at

have fun!

>-----Original Message-----
>Does anyone have information and tips and tricks for me
how to
>deduplicate  records on an oracle database? I have to
know this for a
>project from school.

>If anyone can help me please mail at: (or reply)


Sun, 29 Feb 2004 18:27:49 GMT
 [ 2 post ] 

 Relevant Pages 

1. deduplication advice needed

2. Locking advice needed - are locking hints needed here?

3. Need Advice: Inventory Control Package Needed...

4. Looking for info/advice about KME

5. Looking for PhD position (+ info & advice)

6. Advice/info sought on Lotus Notes

7. Advice/info sought on Lotus Notes

8. Deduplication of rows

9. importing CSV files/Data deduplication

10. Deduplication of records

11. Deduplication of records

12. Deduplication of records

Powered by phpBB® Forum Software