The Secrets of Handling Tricky Duplicate Data!!
Tips for Removing Duplicate Data and Oracle Software for Deleting Duplicate Records

 Author: Richard Kenny

Handling tricky duplicate data is quite frustrating for every database administrator. Sometimes we need to locate duplicate data so as to remove them while other times we have data that currently violates the constraint but we urgently need to enable the constraint for preventing further violations while keeping the existing duplications at its place yet another scenario can be where Oracle does not allow you to create unique index on a column due to the fact that it has duplicate rows.. Even during interviews one common question being asked is how to remove duplicate data from tables. And what we mostly do is to keep on searching some solution for it and today we will help you reveal some secrets of handling tricky duplicate data in your databases.

Identifying and Removing Duplicate Data by locating Duplicate Rows:

All Oracle tables have a pseudo column rowid that uniquely identifies a row within a table. You can display duplicate rows by using Aggregating functions such as MIN and MAX functions against the rowid column.

SELECT ROWID, COL_NO, COL1, COL2 FROM <TABLE_NAME> T1 WHERE T1.ROWID > (SELECT MIN(T2.ROWID) FROM <TABLE_NAME> T2 WHERE T2.COL_NO = T1.COL_NO);

Another solution is to group the rows by COL_NO and use HAVING to limit the grouped rows created by GROUP BY clause.

SELECT COUNT(*), COL_NO FROM <TABLE_NAME> GROUP BY COL_NO HAVING COUNT(*) > 1;

Now you can remove duplicate rows by following any of below approach.

Identifying and Removing Duplicate Data by using EXCEPTIONS INTO clause:

In this case we will first create EXCEPTIONS table by using Oracle script utlexcpt.sql in the $ORACLE_HOME/RDBMS/ADMIN DIRECTORY.

We will use the EXCEPTIONS INTO clause of the alter table command. Duplicate rows in your table will have their rows inserted into EXCEPTIONS table, even though the constraint addition fails.

ALTER TABLE <TABLE_NAME> ADD CONSTRAINT MY_I UNIQUE (COL_NO) EXCEPTIONS INTO exceptions;
alter table <TABLE_NAME>
*
ERROR at line 1:
ORA-02299: cannot enable (OPS$ORACLE.MY_I) - duplicate keys found

Then we will find the duplicate rows by joining the table to the exception table.

SELECT T1.COL_NO, T1.COL1 FROM <TABLE_NAME> T1, EXCEPTIONS E1 WHERE T1.ROWID = E1.ROW_ID;

Finally we will delete rows from the table.

DELETE FROM <TABLE_NAME> WHERE (COL_NO , ROWID) IN
(
SELECT COL_NO , MAX(ROWID) FROM <TABLE_NAME> WHERE ROWID IN
(
SELECT <TABLE_NAME>..ROWED FROM <TABLE_NAME> T1, EXCEPTIONS E1 WHERE T1.ROWID = E1.ROW_ID
) GROUP BY COL_NO HAVING COUNT(*) > 1
);

Enforcing Unique Constraint for New or Modified Records:

You can enforce uniqueness for new or modified records in Oracle table containing duplicate data by creating a unique constraint with DEFERRABLE and NOVALIDATE keywords.

First we will create table and insert some records into it including duplicate data.

CREATE TABLE <TABLE_NAME> (
COL1 INT
);
INSERT INTO <TABLE_NAME> VALUES (1);
INSERT INTO <TABLE_NAME> VALUES (2);
INSERT INTO <TABLE_NAME> VALUES (3);
INSERT INTO <TABLE_NAME> VALUES (1);
INSERT INTO <TABLE_NAME> VALUES (1);

By default, constraints are created as NON DEFERRABLE and VALIDATE. Now we will create a unique constraint with DEFERRABLE and NOVALIDATE keywords. But you should specify the constarint as INITIALLY DEFERRED DEFERRABLE or DEFERRABLE INITIALLY IMMEDIATE.

ALTER TABLE <TABLE_NAME> ADD CONSTRAINT MY_I UNIQUE (COL1) DEFERRABLE INITIALLY IMMEDIATE NOVALIDATE;

You can use below command to verify the status from dictionary table.

SELECT CONSTRAINT_TYPE, STATUS, DEFERRABLE, DEFERRED, VALIDATED FROM USER_CONSTRAINTS WHERE TABLE_NAME =<TABLE_NAME>;

Now we will enable our constraint to NOVALIDATE.

ALTER TABLE <TABLE_NAME> MODIFY CONSTRAINT MY_I ENABLE NOVALIDATE;

The existing duplicate records remain there but now if you attempt to insert any further duplicate records then you will get an error.

INSERT INTO <TABLE_NAME> VALUES (1);
insert into <TABLE_NAME> values (1)
*
ERROR at line 1:
ORA-00001: unique constraint (<TABLE_NAME>.MY_I) violated

Oracle Delete (Remove) Duplicate Entries Software 7.0:

Oracle Delete (Remove) Duplicate Entries Software 7.0 is great software by Sobolsoft that can help you search and delete duplicate data such as values, records, rows and numbers in your Oracle tables.

This software gives you the ability to specify which fields you want to be used as criteria. Only one unique entry is left and all other redundant entries are removed.

Click here to download Oracle Delete (Remove) Duplicate Entries Software 7.0

 More Oracle Articles, Database Articles and DBA Tips
   Database Security: Step by step guideline
   Important Concerns related to Oracle Compression!!
   Database Guide: Beware of Fetch across Commit!!
   Beware of Oracle Dual Table Costs!!
   Releasing Oracle Unused Space: Quick & Efficient Solution!!