Pictures of chickens, flowers, wheat, a boy looking through a magnifying glass, irrigation pipe, soybean pods, and fruits and vegetables.		Cooperative Extension Service
Cooperative Extension Service		Agricultural Experiment Station
Steve Rea's Oracle Tips, Tricks, and Scripts		Search | Publications | Jobs | Personnel Directory | Links

Finding Duplicates In A Table

Here's an interesting way to find duplicates in a table (duplicate sets of fields) from the SolvIt.Net web site (no longer accessable).   It tries to create a unique constraint on a table, but, fails because of the duplicates, and puts the rowid of all of the duplicate records found in the exceptions table.  Using the rowid's, you can then select the rows to see what's in them and, possibly, delete some of the duplicates from the table (note that all rowid's for both the original record and the duplicate records are placed into the exceptions table).  You will need to be logged in as a DBA user ID, or as the owner of the table being checked, to run this method.  First, see if the exceptions table exists in your schema, and create it (using utlexcpt.sql) if it doesn't exist (be sure to reference the exceptions table with your userid prefix, since there may be a public synonym pointing to some other userid's exceptions table):

login sqlplus as myuserid
desc myuserid.exceptions
@$ORACLE_HOME/rdbms/admin/utlexcpt.sql

Then, try to create the unique constraint (such as on last, first, and middle name here), and, using the resulting exception rowid's, list the duplicated values along with the duplicated records/rowids:

truncate table myuserid.exceptions;
alter table saturn.spriden add constraint dupdummy1 unique
     (spriden_last_name,spriden_first_name,spriden_mi)
     exceptions into myuserid.exceptions;
column spriden_last_name format a25 trunc
select distinct spriden_last_name,spriden_first_name,spriden_mi
     from spriden where rowid in (select row_id from myuserid.exceptions);
select spriden_last_name,spriden_first_name,spriden_mi,rowid
     from spriden where rowid in (select row_id from myuserid.exceptions)
     order by 1,2,3;

You can delete the duplicate rows with SQL similar to the following, but, DON'T RUN THIS particular SQL on the spriden table!  This is just an example.   (If you do run it, enter the "rollback;" command immediately after this to bring back the deleted rows.)

delete from spriden where rowid in
     (select row_id from myuserid.exceptions where row_id not in
          (select min(a.row_id) from
               (select spriden_last_name,spriden_first_name,spriden_mi,rowid row_id
                    from spriden where rowid in (select row_id from myuserid.exceptions)) a
               group by a.spriden_last_name,a.spriden_first_name,a.spriden_mi));

If there were no duplicates, a constraint will be created.  You will need to drop that constraint to clean up after this (don't leave the constraint laying around - it might prevent some future inserts and updates):

alter table spriden drop constraint dupdummy1;

This Page was Last Updated on 06/04/07

You Are Visitor Number

© 2006 University of Arkansas Division of Agriculture All rights reserved. Last Date Modified 02/09/2008 Webmaster
	University of Arkansas • Division of Agriculture Cooperative Extension Service 2301 South University Avenue Little Rock, Arkansas 72204 • USA Phone (501) 671-2000 • Fax (501) 671-2209	Mission • Disclaimer • EEO • Privacy • FOI