Identify and Remove the duplicate rows in a Table

In case if we want to identify duplicates rows of a table and want to remove them from a table.  Below are the command which will  identify and remove the duplicare rows from a Table.

1.) Identify duplicates

SQL> select count(*) from ADDRESS2 WHERE ROWID IN (select rowid from ADDRESS2
MINUS
select max(rowid) from ADDRESS2
GROUP BY CALENDAR_YEAR_MONTH,BUSINESS_UNIT_NO,BUSINESS_UNIT_NAME);

COUNT(*)
———-
251

Here 251 duplicates exist – these can be deleted with command below :

2.) Removing the Rows :

SQL> delete from ADDRESS2 WHERE ROWID IN (select rowid from ADDRESS2

MINUS

select max(rowid) from ADDRESS2

GROUP BY CALENDAR_YEAR_MONTH,BUSINESS_UNIT_NO,BUSINESS_UNIT_NAME);

Enjoy  :-)