How to find duplicates in a table?

Answer 1

select column from table
group by column
having count(*)>1

Answer 2

CREATE TABLE <sample table>;
ALTER TABLE <sample table> ADD (SELECT DISTINCT * FROM <table>);
DELETE FROM <table>
WHERE (SELECT * FROM <sample table>);

Back up the unique entries into another table and delete them from the old one. What you'll have left is the duplicates. Test this on a dummy table first as I can't in this moment.

Answer 3

question was to find the duplicates not remove them

Answer 4

select * from dept where rowid not in (select max(rowid) from dept group by deptno,dname,loc);

the above will show you all duplicate excluding unique in oracle database Scott schema table dept

Answer 5

Select count(column) from table group by column

Answer 6

@Manish didn't quite get you.. what was the question?
To remove duplicates you should be using DISTINCT