I would like to know the oracle (SQL) query to find the duplicate records in a oracle data base.
Kindly help me.
Thanks in advance,
Bye

: I would like to know the oracle (SQL) query to find the duplicate records in a oracle data base.
: Kindly help me.
: Thanks in advance,
: Bye
:

By duplicate records I presume you mean records with similar keys?
(Which begs the question as to why your primary key constraint didn't kick out the duplicate entries!)

Try this:-

select *
from table
group by key1, key2, ..., keyN
having count (*) > 1;

If you want to list them for a data cleanup excercise add an order by clause to list them in the order you want.

Gothmordrin
Think it through first - then pester someone.

U can find and delete the Duplicate files

delete from test a
where rowid <> ( select max(rowid)
from test b
where a.sno = b.sno
and a.sname = b.sname )

This querry will find duplicate rows and delete those.

Regards,
Pasha

This way worked for me:

SELECT COL_TO_CHECK, COUNT(COL_TO_CHECK)
FROM TABLE_NAME
GROUP BY COL_TO_CHECK
HAVING COUNT(COL_TO_CHECK) > 1

This post has been deleted.