Locating duplicate rows when trying to create UNIQUE INDEX

Presume you are trying to create a UNIQUE index on a column and get the following error:

SQL> desc emp

    Name                            Null?    Type

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

    EMPNO                                    NUMBER(15)

    NAME                                     VARCHAR2(20)

    DEPTNO                                   NUMBER(15)

   SQL> CREATE UNIQUE INDEX emp_i ON emp (empno);

   CREATE UNIQUE INDEX emp_i ON emp (empno)

                             *

   ERROR at line 1:

   ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found

Above error is encountered as Oralce couldn't create a UNIQUE INDEX on the table as it found duplicate records while trying to create the index. You know have to find the duplicate values. Below methods will be useful for locating duplicate rows in a table.

    Method #1 uses a self-join query. Oracle tables Consistently have one guaranteed unique column, the rowid column. Use of the MIN and MAX functions against the rowid column can easily display the duplicate rows.

    Method #2 uses a GROUP BY / HAVING query on the table.

    Method #3 uses the "exceptions into" clause of the alter table command. We need to first create the EXCEPTIONS table by running the script "utlexcpt.sql" inorder to use the "exceptions into" clause . On NT, this script is in your ORACLE_HOME/rdbms(73/80)/admin directory: On UNIX, it is in $ORACLE_HOME/rdbms/admin directory.

    Method #1 : Using a self join

    SELECT

          rowid

        , empno

        , name

      FROM emp a

      WHERE a.rowid > (SELECT MIN(b.rowid)

                       FROM emp b

                       WHERE b.empno = a.empno);

      ROWID                   EMPNO NAME

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

      0000059F.0001.0001        100 Jeff

    Method #2 : Using a GROUP BY

    SELECT 

          COUNT(*)

        , empno

      FROM emp

      GROUP BY empno

      HAVING COUNT(*) > 1;

        COUNT(*)      EMPNO

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

               2        100

    Method #3 : Using the "into exceptions" clause

    ALTER TABLE emp

      ADD CONSTRAINT emp_i UNIQUE (empno)

      EXCEPTIONS INTO exceptions;

      alter table emp

      *

      ERROR at line 1:

      ORA-02299: cannot enable (OPS$ORACLE.EMP_I) - duplicate keys found

      -- NOW JOIN THE exceptions TABLE TO THE emp TABLE BY rowid

      SELECT 

          a.empno

        , a.name

      FROM 

          emp a

        , exceptions b

      WHERE a.rowid = b.row_id;

           EMPNO NAME

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

             100 Mark

             100 Jeff