How to Find Duplicate Rows in a Table:
======================================
Do the following:
1. Create primary table:
create table test (a number);
Table created.
insert into test values (1);
insert into test values (1); /* Duplicate of First row */
insert into test values (2);
insert into test values (3);
insert into test values (3); /* Duplicate of Fourth row */
2. Create the exceptions table by running the script "utlexcpt.sql". On
Windows NT, the script is in %ORACLE_HOME%\RDBMSxx\ADMIN directory
(where xx is 73 for version 7.3, 80 for 8.0, and empty for 8i). On UNIX,
it is in the $ORACLE_HOME/rdbms/admin directory.
create table exceptions(row_id rowid,
owner varchar2(128),
table_name varchar2(128),
constraint varchar2(128));
3. Add the constraint:
SQL> alter table test add
(constraint test_pk primary key (a) using index
storage (initial 5K) exceptions into exceptions);
alter table test add
*
ERROR at line 1:
ORA-02437: cannot enable (SYSTEM.TEST_PK) - primary key violated
3. Check the exceptions table. It will list all duplicate rows.
SQL> select * from exceptions;
ROW_ID OWNER TABLE_NAME CONSTRAINT
------------------ ----------- ----------- -----------
AAAAkbAABAAACmRAAA SYSTEM TEST TEST_PK
AAAAkbAABAAACmRAAB SYSTEM TEST TEST_PK
AAAAkbAABAAACmRAAD SYSTEM TEST TEST_PK
AAAAkbAABAAACmRAAE SYSTEM TEST TEST_PK
4. Join the exceptions table with the test table to find duplicate values.
select *from test;
SQL> select zzz.a from test zzz, exceptions xxx
where zzz.rowid = xxx.row_id;
A
----------
1
1
3
3
References:
===========