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;

Copyright © 2009 by Maristream.   All information, scripts, forms, and other material
on this web site are freely available to all Banner and Oracle Database Administrators,
Systems Administrators, Programmers, and others that may need it.

The webmaster who maintains this web site may be reached at srea@maristream.org. Visit our other web sites:

Disclaimer:  As with all software, especially where it affects your vital data, make sure that you examine theses scripts and that you understand what they do before you use them to see if they would have any adverse effect on your particular setup or database layout.  Make a full backup of your database in case you have to revert to your original copy of the database before the scripts were run.  Use these scripts at your own risk.  As a condition of using these scripts, you agree to hold harmless both Maristream and Stephen Rea for any problems that they may cause or other situations that may arise from their use, and that neither Maristream nor I will be held liable for those consequences.