Two ways of deleting duplicated rows in Oracle

The most effective way to detect duplicate rows in Oracle is to join the table against itself as shown below. Assume table_name has two columns col1 and col2

SELECT *
  FROM table_name A
 WHERE A.rowid > (SELECT min(B.rowid)
          FROM table_name B
         WHERE A.col1 = B.col1
           AND A.col2 = B.col2)

To delete the duplicate rows:

DELETE FROM table_name A
 WHERE A.rowid > (SELECT min(B.rowid)
          FROM table_name B
         WHERE A.col1 = B.col1
           AND A.col2 = B.col2)

You can also detect and delete duplicate rows using Oracle analytic functions:

delete from table_name
 where rowid in (select rowid
                   from (select rowid,
                                row_number() over(partition by col1, col2 order by col1, col2) dup
                           from table_name)
                  where dup > 1);