This Post Presents A  Demo to find duplicate records by running a SQL Query  In A Oracle DB (12c) Table.

Oracle DB Used : Oracle Database 12c Enterprise Edition Release

Suppose A Database Table is Like :

CREATE TABLE DUPLICATE_TEST
(
  A                VARCHAR2(10),
  B                VARCHAR2(10),
  C                VARCHAR2(10),
  D                VARCHAR2(10),
  E                VARCHAR2(10),
  F                NUMBER(5)
)
/

(This table is not with any unique or primary key constraints)

Have following Rows (Three Rows with field value 1,2,3,4,5,6 each), So 3 Duplicate Records (Rows):

A,B,C,D,E,F

1 2 3 4 5 6
1 2 3 4 5 6
1 2 3 4 5 6

 

On executing the following :

SELECT
    A, B,C,D,E,F, count(*) no_of_records
FROM DUPLICATE_TEST
GROUP BY A, B,C,D,E,F
HAVING count(*) > 1;

 

Would Return The Following :

A,B,C,D,E,F,NO_OF_RECORDS

1 2 3 4 5 6 3


See Last Value As 3 :NO_OF_RECORDS

Here are the relevant screenshots :

Duplicate Records Count in Oracle DB Table

 

• Database Technologies