Re: How do I locate duplicate rows

I usually do this with a WHERE EXISTS clause and check for rows in the table with the same value in the column but a different rowid, for example, if I want to find dups in my column, I write the query:

SELECT count(*)
  FROM My_Table a
 WHERE EXISTS (SELECT 'x'

                 FROM My_Table b
                WHERE a.some_column = b.some_column
                  and a.rowid != b.rowid);

This query is slow on large tables. When I have a large table, I might right a procedure to find the dups.

Anthony Valentine wrote:
>
> Hello All!
>
> I am trying to add a unique constraint to a table, but I get the
> following error:
>
> ORA-02299: cannot validate (ORACLE.LOS160MASTER_UK) - duplicate keys
> found
>
> Is there any way to get Oracle to tell me which rows contain the
> duplicate keys?
>
>
> Thanks in advance!
>
> Anthony Valentine
>
>

--
Posted via CNET Help.com
http://www.help.com/