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);