Re: duplicates

michel.lalonde_at_sit.ulaval.ca (Michel Lalonde) wrote:
>
> In article <1995Aug1.115212.57550_at_ucl.ac.uk> ucfaabe_at_ucl.ac.uk (Mr Angus Beare) writes:
> >From: ucfaabe_at_ucl.ac.uk (Mr Angus Beare)
> >Subject: duplicates
> >Summary: how do I find duplicates in large oracle tables?
> >Date: Tue, 1 Aug 1995 11:52:12 GMT
> >Keywords: how to find duplicate recs
>
>
> >I have a table that I want to create a unique index for
> >but I can't because of duplicates. Oracle won't tell
> >me where they are.
 

> >Does anyone know a quick way to find duplicate records?
 

> >thanks for any suggestions
 

> >Gus
 

> >--
> >Angus Beare
>
> Add a unique constraint with the "exceptions into" option (the unique index
> will be created automaticaly). The duplicates will be listed in the exceptions
> table.
>
> Michel Lalonde.
>

Another possible solution is the following script which will list the values of the columns that are duplicates. This has proven useful to me many times.

select column_name,count(*) from owner.table

group by column_name having count(*) > 1           
/                                                     

Chris Woodyard Received on Thu Aug 10 1995 - 00:00:00 CEST