Scripts - Duplicate Records

Find duplicate records:
=======================

select count(0) from tab1 a where a.rowid > any (select b.rowid from tab1 b where a.col1 =b.col1)
/

select coloumn_name ,count(*) from table_name group by
coloumn_name having count(*) > 1
/


select sum(count(col1) -1) from emp group by col1 having count(col1) > 1
/

select count(*) from table_name group by column1,column2... having count(*) > 1
/

Delete duplicate records:-

Eg :-

Table Name : EMP
Columns :- EMPNO, ENAME

declare
v_rowid rowid;
v_count number;
procedure delete_rowid (v_pass_rowid rowid) as
begin
delete from emp where rowid = v_pass_rowid;
commit;
end delete_rowid;
begin
for i in (select empno,ename, count(*) from emp
group by empno, ename having count(*) > 1 ) loop
select count(*) into v_count from emp where empno = i.empno and ename = i.ename;
if v_count > 1 then
for j in (select rowid from emp where empno = i.empno and ename = i.ename) loop
delete_rowid (j.rowid);
v_count := v_count - 1;
exit when v_count = 1;
end loop;
end if;
end loop;
end;
/