How to find duplicate rows in source tables

Problem Description

When there is no Primary Key (PK) or Unique Key (UK) defined on a table, the data can still be successfully replicated if there are no duplicate rows in the source table. Below article provides queries that can be used to see if there are any duplicate values.

Solution

You can use the following query to find duplicate rows:

SELECT c1, c2, c3, ..., cn, count(*)
FROM T
GROUP BY c1, c2, c3, ...., cn
HAVING count(*) > 1

Where c1, c2, .. cn are all the columns in the table.

The following query can also be used:

SELECT OWNER, TABLE_NAME FROM DBA_LOGSTDBY_NOT_UNIQUE
WHERE (OWNER, TABLE_NAME) NOT IN
(SELECT DISTINCT OWNER, TABLE_NAME FROM DBA_LOGSTDBY_UNSUPPORTED)
AND BAD_COLUMN = 'Y'

Arjen Visser February 13, 2014 15:32