Find duplicate rows in Oracle table
You are an Oracle DBA or developer and want to determine the location of duplicate rows in an Oracle table. You need it for example before attempting to place a unique index on the table. See below how can this be done.
Oracle tables always have one guaranteed unique column, the rowid column. If you use a min/max function against your rowid and then select against the proposed primary key you can squeeze out the rowids of the duplicate rows pretty quick. For example:
SELECT * FROM customers o
WHERE rowid > (SELECT min(rowid) FROM customers i
WHERE o.city = i.city and o.rating = i.rating)
In the situation where multiple columns make up the proposed key,
they must all be used in the WHERE clause.
Here is another simple and dirty way to accomplish the same:
SELECT city, rating, count(*)
GROUP BY city, rating
HAVING count(*) > 1