database.sarang.net
UserID
Passwd
Database
DBMS
MySQL
PostgreSQL
Firebird
ㆍOracle
Informix
Sybase
MS-SQL
DB2
Cache
CUBRID
LDAP
ALTIBASE
Tibero
DB 문서들
스터디
Community
공지사항
자유게시판
구인|구직
DSN 갤러리
도움주신분들
Admin
운영게시판
최근게시물
Oracle Tutorials 9058 게시물 읽기
 News | Q&A | Columns | Tutorials | Devel | Files | Links
No. 9058
EXCEPTION TABLE을 이용하여 중복된 자료를 확인하는 방법
작성자
정재익(advance)
작성일
2001-12-25 14:08
조회수
6,184

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.

이 글에 대한 댓글이 총 1건 있습니다.

2건 이상된거 (중복된건) 2건 모두 지웁니다. 조심하시길...

중복된 건 중 그 중에서 나중에 들어온 녀석만 지울려고 할려면

지우는 부분에서 쿼리를 좀 수정하셔야 될 것입니다.

 

그럼이만

오라클님이 2005-04-15 16:59에 작성한 댓글입니다. Edit
[Top]
No.
제목
작성자
작성일
조회
9061NT 에서 자동 백업을 받을 수 있는 방법
정재익
2001-12-25
6492
9060Rollback segment의 status가 FULL로 되는 경우와 조치 사항
정재익
2001-12-25
5657
9059PIPE 와 COMPRESS 를 사용한 export
정재익
2001-12-25
4401
9058EXCEPTION TABLE을 이용하여 중복된 자료를 확인하는 방법 [1]
정재익
2001-12-25
6184
9057ORA-1555에 관해
정재익
2001-12-25
4687
9054ora-01034 처리방법
정재익
2001-12-25
7098
9053Data와 Index가 같은 tablespace에 있는 경우 를 분리하는 방법
정재익
2001-12-25
4377
Valid XHTML 1.0!
All about the DATABASE... Copyleft 1999-2021 DSN, All rights reserved.
작업시간: 0.012초, 이곳 서비스는
	PostgreSQL v13.3으로 자료를 관리합니다