EXCEPTION TABLE을 이용하여 중복된 자료를 확인하는 방법
====================================================

table에 data가 들어 있는 상태에서 unique index를 생성하려는 경우 중복된
자료가 있다는 error를 만나는 경우가 있다.

ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found

이 경우에 어느 data가 중복되어 있는지 확인하고 정리하고자 한다면
alter table ... enable...명령을 사용하여 가능하다.


1) 우선 create index 명령을 사용하지 않고,
alter table 명령을 이용하여 unique index에 해당하는 constraint를 생성한다.

여기에서 UNIQUE대신에 PRIMARY KEY가 필요한 경우에는 사용할 수 있다.


SQL> ALTER TABLE dept
ADD CONSTRAINT un_deptno UNIQUE(deptno) disable;

이때 disable을 지정하지 않으면, 다음과 같은 error를 만나며 constraint가
생성되지 않는다.

ORA-02299: cannot enable (KHLEE.UN_DEPTNO) - duplicate keys found.


2) SQL> ALTER TABLE dept
ENABLE CONSTRAINT un_deptno
EXCEPTIONS INTO exceptions;

만약 예외 사항이 없다면 enable이 되고, 예외 사항이 있다면 다음의 error와 함께
disable인 상태로 남게 된다.

ORA-02299: cannot enable (KHLEE.UN_DEPTNO) - duplicate keys found.

예외 사항에 대한 정보는 exceptions라는 table에 들어가게 된다.

* enable을 하기 전에 enable의 exceptions options으로부터 정보를 수용하려면
적합한 예외 사항 보고 table인 exceptions라는 table을 생성하여야 한다.

이것은 $ORACLE_HOME/rdbms/admin/utlexcpt.sql을 해당 user에서 실행해주면
생성된다.
이 script 내에서 table의 이름을 변경하여 사용하여도 가능하다.


3) 예외사항의 확인.
아래와 같이 중복된 record에 대한 정보를 exceptions table에서 확인가능하다.

SQL> SELECT * FROM exceptions;

ROW_ID OWNER TABLE_NAME CONSTRAINT
------------------ -------- ---------- ----------
000024BB.0005.0005 KHLEE DEPT UN_DEPTNO
000024BB.0002.0005 KHLEE DEPT UN_DEPTNO


4) 자세한 sql을 이용하여 제약 조건에 위배되는 row들을 확인할 수 있다.

SQL> SELECT deptno, dname, row_id
FROM dept, exceptions
WHERE exceptions.constraint = 'UN_DEPTNO'
AND dept.rowid = exceptions.row_id;

DEPTNO DNAME ROW_ID
--------- -------------- -------------
30 XX 000024BB.0005.0005
30 SALES 000024BB.0002.0005


5) 해당 data를 확인한 후 제약조건에 위배되는 자료를 삭제한다.
다음과 같이 해당 table의 다른 컬럼을 이용하여 지울수도 있고 혹은
rowid값을 이용할 수 있다.

SQL> DELETE FROM dept
WHERE deptno = 30
AND dname = 'XX';

또는

SQL> delete from dept where rowid='000024BB.0005.0005';

6) 해당 constraint를 enable한다.

SQL> alter table dept enable constraint un_deptno;

Table altered.