반응형
ORA-00001: unique constraint primary key violated
테이블 컬럼작업을 하는 중 데이터가 들어있는 테이블이 있어서,
데이터 백업 후 삭제 -> 테이블 작업 -> 데이터 복구 순서로 작업을 수행하려고 했습니다.
작업 후 복구 중에 데이터가 들어와서 기존 데이터와 중복되는 상황이 발생!
그래서 기존 데이터가 하나도 안들어가는 경우가 발생하였습니다.
여러 테이블의 작업을 수행했는데, 두가지 방법으로 해결하였습니다.
1. INSERT 시 exist 문을 활용하여 PK중복제거 한 데이터만 INSERT하기
2.임시 테이블을 만들어 임시테이블에 INSERT 후 RENAME 하기.
아래는 최대한 비슷하게 구성한 테스트 시나리오입니다.
준비
테스트 테이블 생성.
CREATE TABLE TB_A (
ID NUMBER(10) NOT NULL,
NAME VARCHAR2(20),
AA VARCHAR2(100),
BB VARCHAR2(100)
) TABLESPACE MY_DATA;
--PK 생성
CREATE UNIQUE INDEX PK_TB_A ON TB_A(ID) TABLESPACE MY_DATA;
ALTER TABLE TB_A ADD CONSTRAINT PK_TB_A primary key (ID) USING INDEX TABLESPACE MY_DATA;
--테스트 데이터 100,000 삽입
BEGIN
FOR i IN 1..1000000 LOOP
INSERT INTO TB_A(id,name,AA,BB) VALUES (i, 'aaaaa','BAE','9999');
END LOOP;
COMMIT;
END;
/
1.INSERT 시 데이터의 중복여부를 추가하여 중복이지 않은 데이터만 INSERT
1)
세션1.
기존 테이블의 데이터를 백업 후 -> 삭제 -> 다시 삽입을 하여 PK 에러를 발생시킵니다.
SQL> select count(*) from tb_a;
COUNT(*)
---------
1000000
--백업
CREATE TABLE TB_B
AS SELECT * FROM TB_A;
--데이터 truncate
TRUNCATE TABLE TB_A;
--데이터 확인
SELECT COUNT(*) FROM TB_A
union all
SELECT COUNT(*) FROM TB_B;
COUNT(*)
---------
0
1000000
2)
세션2.
자동적으로 데이터가 들어오는 상황을 가정하여 테이블에 2초쉬고 데이터를 INSERT합니다.
동시에 세션1에서 데이터를 다시 INSERT 합니다.
BEGIN
FOR i IN 1..100 LOOP
INSERT INTO TB_A(id,name,AA,BB) VALUES (i,dbms_random.string('a',10),dbms_random.string('a',10),dbms_random.string('a',10));
commit;
DBMS_LOCK.SLEEP(2);
END LOOP;
END;
/
세션1.
INSERT INTO TB_A
(
ID,
NAME,
AA,
BB
)
SELECT
ID,
NAME,
AA,
BB
FROM TB_B B;
PK 에러가 발생함.
ORA-00001: unique constraint (DEV.PK_TB_A) violated
3)
세션1.
쿼리를 통해 PK가 있는 값을 제외하고 INSERT
INSERT INTO TB_A
(
ID,
NAME,
AA,
BB
)
SELECT
ID,
NAME,
AA,
BB
FROM TB_B B
WHERE NOT EXISTS (SELECT 1 FROM TB_A A
WHERE A.ID = B.ID ---PK로 수행
)
;
999983 rows created.
commit;
Commit complete.commit이 되면서 동시에 세션2에서 PK 에러가 발생함.
세션2.
BEGIN
*
ERROR at line 1:
ORA-00001: unique constraint (DEV.PK_TB_A) violated
ORA-06512: at line 3
4)
테이블의 데이터 확인
데이터를 확인해보면 NAME이 aaaa인 데이터들은 최초의 데이터들이고, RANDOM하게 들어간 데이터들은 실시간으로 한건씩 추가한 데이터들입니다.(실무에서 실시간으로 들어오는 데이터를 표현)
데이터를 넣을때 까지의 데이터만 들어가서 PK중복이 안되는 데이터를 삽입할 수 있었고, 결론적으로는 10만건의 데이터가 동일하게 있을 수 있게 되었습니다.
(실제 테이블의 데이터를 확인해보면 ID가 17번인 데이터는 새로 추가된 데이터이고 18번부터는 기존의 데이터입니다.)
SQL> SELECT COUNT(*) FROM TB_A
union all
SELECT COUNT(*) FROM TB_B;
COUNT(*)
---------
1000000
1000000
2 rows selected.
-------------------------[Start Time: 2023/01/13 16:55:40]-------------------------
SQL> select * From TB_A
WHERE ID <= 20;
ID NAME AA BB
--------- -------------------- ---------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------
1 BDPcswdfGC jTtdTDXqJG untWeeKKzf
2 vKJnpaqyZq TWKKiKNWvj EwujwfiZns
3 bYqQqYukbt tqMTQdyHsZ OOHuOsqsNc
4 SSXPGJuEuK DHMIDUZtrp FxoumqRgID
5 BMCyIAzHYL nBOFhSjgnF hvpBXFJovP
6 NwRxJMHMPc fMuGumPXaZ wybyFYRLnE
7 QNEpIwNWRx uPsiMciFZy fFmXOTVjJw
8 QYZTRoJIcM jKmTIIaKEf WxRZbegZYR
9 teurQMDCro oSQMRnNNhS ysgaWsCGtp
10 FCnXEAWpWz xPcUrokfnJ xeSErPWxxK
11 BIEeMsSnCn mLdJmqhQFJ BzkLtxRJVC
12 LYyadJIvHu cWqQTjVzUw YYxiWvFWoi
13 veLBOQUlEu kJmvZVjGLO uqhTkhTExS
14 rDZSvrnKBY dGJHsythoh JzMVBgMfFL
15 iMpeMgBaGb tNMUVmobAZ PdEJotjlxA
16 CVXrCqmKhy amuBCfmBxB FcIJVWGYPK
17 hEveSwDzfD lPOvbSIqKl vpNGKYsePS
18 aaaaa BAE 9999
19 aaaaa BAE 9999
20 aaaaa BAE 9999
2 백업테이블 생성 후 -> 작업 -> RENAME
준비
테이블 데이터 삭제
TRUNCATE TABLE TB_A;
1)
세션1.
백업 테이블 생성 후 데이터 INSERT
동시에 세션2에서는 실시간으로 데이터 INSERT하게 함.
CREATE TABLE DEV.TB_A_2
(
ID NUMBER(10) NOT NULL,
NAME VARCHAR2(20),
AA VARCHAR2(100),
BB VARCHAR2(100)
)
TABLESPACE MY_DATA
STORAGE
(
INITIAL 64K
NEXT 1M
)
NOCOMPRESS;
CREATE UNIQUE INDEX DEV.PK_TB_A_2
ON DEV.TB_A_2 (ID)
TABLESPACE MY_DATA
STORAGE
(
INITIAL 64K
NEXT 1M
);
ALTER TABLE DEV.TB_A_2
ADD CONSTRAINT PK_TB_A_2 PRIMARY KEY (ID);
INSERT INTO TB_A_2
(
ID,
NAME,
AA,
BB
)
SELECT
ID,
NAME,
AA,
BB
FROM TB_B;
COMMIT;
세션2.
BEGIN
FOR i IN 1..100 LOOP
INSERT INTO TB_A(id,name,AA,BB) VALUES (i,dbms_random.string('a',10),dbms_random.string('a',10),dbms_random.string('a',10));
commit;
DBMS_LOCK.SLEEP(2);
END LOOP;
END;
/
2)
RENAME 작업 수행
세션1.
ALTER TABLE TB_A RENAME TO TB_A_ASIS;
ALTER INDEX PK_TB_A RENAME TO PK_TB_A_ASIS;
ALTER TABLE TB_A_ASIS RENAME CONSTRAINT PK_TB_A TO PK_TB_A_ASIS;
ALTER TABLE TB_A_2 RENAME TO TB_A;
ALTER INDEX PK_TB_A_2 RENAME TO PK_TB_A;
ALTER TABLE TB_A RENAME CONSTRAINT PK_TB_A_2 TO PK_TB_A;
세션2.
PK 에러 발생
ORA-00001: unique constraint (DEV.PK_TB_A_2) violated
3) 데이터 확인
RENAME한 테이블에서는 TB_A에는 기존의 백업 데이터가 다 들어가있습니다.
RENAME하기 직전까지의 데이터는 TB_A_ASIS 테이블(직전까지 원본테이블)에 PK가 10번까지 저장되어 있습니다.
SQL> select * From TB_A
WHERE ID <= 20;
ID NAME AA BB
--------- -------------------- ---------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------
1 aaaaa BAE 9999
2 aaaaa BAE 9999
3 aaaaa BAE 9999
4 aaaaa BAE 9999
5 aaaaa BAE 9999
6 aaaaa BAE 9999
7 aaaaa BAE 9999
8 aaaaa BAE 9999
9 aaaaa BAE 9999
10 aaaaa BAE 9999
11 aaaaa BAE 9999
12 aaaaa BAE 9999
13 aaaaa BAE 9999
14 aaaaa BAE 9999
15 aaaaa BAE 9999
16 aaaaa BAE 9999
17 aaaaa BAE 9999
18 aaaaa BAE 9999
19 aaaaa BAE 9999
20 aaaaa BAE 9999
20 rows selected.
SQL> select * from TB_A_ASIS;
ID NAME AA BB
--------- -------------------- ---------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------
1 ySixRRACUP HJCNJSBZWw JrJahOguuS
2 kkwdgjdBks oqXxybVDMs JSbTnggOrL
3 kknQtnsgOt LqPttHSfdu GqNVmXQcsY
4 vNmXJheQdM VgtcmDauyR bnEEgWIEtl
5 MotBRUYuJW UnLXXstnPk orPxdckvbC
6 TqEXOmBRWv auhKoygysw GxwIjpQdwt
7 XrkOuiaNpu oThxudqvvV YrsYLDfUBK
8 fYJHKUcIXe sURZtBamEe KpVoRjvQjv
9 tFmzCznKFS HiQMuSsTKQ hqJWzxHEYt
10 PVOJduTEky TiVVrFHNIc zcGtRsCuEa
10 rows selected.
SQL> SELECT COUNT(*) FROM TB_A
union all
SELECT COUNT(*) FROM TB_B;
COUNT(*)
---------
1000000
1000000
2 rows selected.
결론.
해당 경우가 발생하면 PK가 중복되는 데이터에 대해서 매뉴얼하게 검증이 반드시 필요할 것입니다.
PK는 겹칠때, 이전의 데이터가 필요한 것인지, 새로 들어온 데이터가 필요한것인지 성격에 따라 반드시 확인이 필요하며,
2번의 경우가 좀더 데이터의 유실 없이 안전하게 작업가능 할 것입니다.
이유는, 백업 테이블을 rename하는것이기 때문에 교체시간이 최소화이고, 그 동안에 ASIS 테이블에 새로들어온 데이터들이 그대로 보존되어 있기 때문입니다.
아래는 데이터의 중복이 있는지 체크해보는 쿼리입니다.
SQL> SELECT *
FROM (SELECT 1 flag , ----기존 데이터 중, truncate 후에 새로 들어온 데이터와 PK가 겹치는 데이터 확인.
A.ID,
A.NAME,
A.AA,
A.BB
FROM TB_A A
, TB_B B
WHERE A.ID = B.ID
AND EXISTS (SELECT 1
FROM TB_A_ASIS c
WHERE c.ID = B.ID
)
UNION ALL
SELECT 2 flag , ---TRUNCATE 후 새로 들어온 데이터
A.ID,
A.NAME,
A.AA,
A.BB
FROM TB_A_ASIS A
)
order by 2,1;
FLAG ID NAME AA BB
---- --------- -------------------- ---------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------
1 1 aaaaa BAE 9999
2 1 ySixRRACUP HJCNJSBZWw JrJahOguuS
1 2 aaaaa BAE 9999
2 2 kkwdgjdBks oqXxybVDMs JSbTnggOrL
1 3 aaaaa BAE 9999
2 3 kknQtnsgOt LqPttHSfdu GqNVmXQcsY
1 4 aaaaa BAE 9999
2 4 vNmXJheQdM VgtcmDauyR bnEEgWIEtl
1 5 aaaaa BAE 9999
2 5 MotBRUYuJW UnLXXstnPk orPxdckvbC
1 6 aaaaa BAE 9999
2 6 TqEXOmBRWv auhKoygysw GxwIjpQdwt
1 7 aaaaa BAE 9999
2 7 XrkOuiaNpu oThxudqvvV YrsYLDfUBK
1 8 aaaaa BAE 9999
2 8 fYJHKUcIXe sURZtBamEe KpVoRjvQjv
1 9 aaaaa BAE 9999
2 9 tFmzCznKFS HiQMuSsTKQ hqJWzxHEYt
1 10 aaaaa BAE 9999
2 10 PVOJduTEky TiVVrFHNIc zcGtRsCuEa
20 rows selected.
반응형
댓글