Oracle ways to delete duplicate records Oracle

create table temp_emp(
EMPNO  integer,
EMPNAME varchar2(20),
SALARY  number);

insert into temp_emp
values
(10  , 'Bill' ,   2000);
insert into temp_emp
values
(11  ,  'Bill' ,   2000);
insert into temp_emp
values
(12  ,  'Mark' ,   3000);
insert into temp_emp
values (12 ,   'Mark'  ,  3000);
insert into temp_emp
values (12  ,  'Mark'  ,  3000);
insert into temp_emp
values (13 ,   'Tom'  ,  4000);
insert into temp_emp
values (14  ,  'Tom'  ,  5000);
insert into temp_emp
values(15  ,  'Susan'  ,  5000);
1. Using rowid
SQL > delete from temp_emp
where rowid not in
(select max(rowid) from temp_emp group by empno);

delete from temp_emp a
where rowid <
(select max(rowid) from temp_emp b where a.empnno = b.empnno );


This technique can be applied to almost scenarios. Group by operation should be on the columns which identify the duplicates.
2. Using self-join
SQL > delete from temp_emp e1
where rowid not in
(select max(rowid) from temp_emp e2
where e1.empno = e2.empno );

3. Using row_number()
SQL > delete from emp where rowid in
(
select rid from
(
select rowid rid,
row_number() over(partition by empno order by empno) rn
from emp
)
where rn > 1
);
This is another efficient way to delete duplicates

4. Using dense_rank()
SQL > delete from emp where rowid in
(
select rid from
(
select rowid rid,
dense_rank() over(partition by empno order by rowid) rn
from emp
)
where rn > 1
);
Here you can use both rank() and dens_rank() since both will give unique records when order by rowid.
5. Using group by
Consider the EMP table with below rows
10    Bill    2000
11    Bill    2000
12    Mark    3000
13    Mark    3000
SQL > delete from emp where
(empno,empname,salary) in
(
select max(empno),empname,salary from emp
group by empname,salary
);
This technique is only applicable in few scenarios.

Always take extra caution while deleting records. 
1. First identify the duplicates using select.
2. Double verify those are actual  ‘duplicates’ or not
3. Take backup if necessary
4. Apply commit only if you are sure.
Did you find above post useful ? Your comments are highly valuable.