use except to find dupliated records

SQL> create table stu (f1 number);
insert table st
Table created.

SQL> insert into stu values (100);

1 row created.

SQL> insert into stu values (101);

1 row created.

SQL> insert into stu values (101);

1 row created.

SQL> insert into stu values (102);

1 row created.

SQL> insert into stu values (103);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from stu;

F1
----------
100
101
101
102
103


SQL> alter table stu add constraint f1_uk unique(f1) using index tablespace users;
alter table stu add constraint f1_uk unique(f1) using index tablespace users
*
ERROR at line 1:
ORA-02299: cannot validate (SYS.F1_UK) - duplicate keys found


SQL> desc exceptions
ERROR:
ORA-04043: object exceptions does not exist


SQL> @?/rdbms/admin/utlexcpt

Table created.

SQL> desc exceptions
Name Null? Type
----------------------------------------- -------- ----------------------------
ROW_ID ROWID
OWNER VARCHAR2(30)
TABLE_NAME VARCHAR2(30)
CONSTRAINT VARCHAR2(30)


SQL> alter table stu add constraint f1_uk unique(f1) using index tablespace users exceptions into exceptions;
alter table stu add constraint f1_uk unique(f1) using index tablespace users exceptions into exceptions
*
ERROR at line 1:
ORA-02299: cannot validate (SYS.F1_UK) - duplicate keys found


SQL> desc exceptions
Name Null? Type
----------------------------------------- -------- ----------------------------
ROW_ID ROWID
OWNER VARCHAR2(30)
TABLE_NAME VARCHAR2(30)
CONSTRAINT VARCHAR2(30)

SQL> select * from exceptions
2 /

ROW_ID OWNER TABLE_NAME
------------------ ------------------------------ ------------------------------
CONSTRAINT
------------------------------
AAAMfdAABAAANsSAAC SYS STU
F1_UK

AAAMfdAABAAANsSAAB SYS STU
F1_UK


SQL> select * from user_constraints where table_name='STU';

no rows selected

SQL> SELECT * FROM STU where rowid in ('AAAMfdAABAAANsSAAC','AAAMfdAABAAANsSAAB');

F1
----------
101
101

SQL> DELETE FROM STU where rowid in ('AAAMfdAABAAANsSAAB');
1 row deleted.

SQL> COMMIT;

Commit complete.

SQL> alter table stu add constraint f1_uk unique(f1) using index tablespace users exceptions into exceptions;

Table altered.


SQL> SET PAGES 1000
SQL> select * from user_constraints where table_name='STU';

OWNER CONSTRAINT_NAME C
------------------------------ ------------------------------ -
TABLE_NAME
------------------------------
SEARCH_CONDITION
--------------------------------------------------------------------------------
R_OWNER R_CONSTRAINT_NAME DELETE_RU STATUS
------------------------------ ------------------------------ --------- --------
DEFERRABLE DEFERRED VALIDATED GENERATED BAD RELY LAST_CHAN
-------------- --------- ------------- -------------- --- ---- ---------
INDEX_OWNER INDEX_NAME INVALID
------------------------------ ------------------------------ -------
VIEW_RELATED
--------------
SYS F1_UK U
STU

ENABLED
NOT DEFERRABLE IMMEDIATE VALIDATED USER NAME 14-JUL-07
SYS F1_UK