DSG realsync BUG 之ORA-00001: unique constraint
来源:互联网 发布:淘宝商户消失 编辑:程序博客网 时间:2024/05/16 02:00
现象模拟:
源端:
CREATE TABLE SCOTT.TEST_PK
( CATEGORY_ID VARCHAR2(40 BYTE) NOT NULL,
SEQUENCE_NUM NUMBER(38) NOT NULL,
CHILD_PRD_ID VARCHAR2(40 BYTE) NOT NULL);
CREATE INDEX SCOTT.CT_CHLDPRD_CID_IDX ON SCOTT.TEST_PK(CATEGORY_ID);
CREATE INDEX SCOTT.CT_CHLDPRD_CPI_IDX ON SCOTT.TEST_PK(CHILD_PRD_ID);
CREATE UNIQUE INDEX SCOTT.TEST_PK_P ON SCOTT.TEST_PK(CATEGORY_ID, SEQUENCE_NUM);
ALTER TABLE SCOTT.TEST_PK ADD (CONSTRAINT TEST_PK_P PRIMARY KEY (CATEGORY_ID, SEQUENCE_NUM) USING INDEX SCOTT.TEST_PK_P);
SQL> insert into TEST_PK values('1',5,'3');
1 row created.
SQL> insert into TEST_PK values('1',4,'2');
1 row created.
SQL> insert into TEST_PK values('1',3,'1');
1 row created.
SQL> commit;
Commit complete.
SQL> set lines 200
SQL> select * from TEST_PK;
CATEGORY_ID SEQUENCE_NUM CHILD_PRD_ID
---------------------------------------- ------------ ----------------------------------------
1 3 1
1 4 2
1 5 3
update
SQL> UPDATE TEST_PK SET SEQUENCE_NUM = CHILD_PRD_ID WHERE CATEGORY_ID = 1;
3 rows updated.
SQL> commit;
Commit complete.
SQL>
目标端日志:
Loader: 14.xf1 OCI Error -1 occurred at File xf1_to_oracle_upd_rid.c:1158.
ORA-00001: unique constraint (SCOTT_DSG.TEST_PK_P) violated
OCI Error -1 occurred at File xf1_to_oracle_upd_rid.c:1158.
ORA-00001: unique constraint (SCOTT_DSG.TEST_PK_P) violated
Error to execute SQL statement:
UPDATE "SCOTT_DSG"."TEST_PK" SET "SEQUENCE_NUM"=:1 WHERE ROWID=:2 RETURNING ROWID INTO :3
Error occurs at "UPDATE"
ERROR rowid: AAASABAAEAAAACsAAA (00012001.010000AC.0000)
: 00011FFA.00012001.010000AC.0000 B5(URP) --H-FL-- cc:1/3 len:6
: --H-FL-- cc:1 len:10
: 00011FFA.00012001.010000AC.0001 B5(URP) --H-FL-- cc:1/3 len:6
: --H-FL-- cc:1 len:10
: 00011FFA.00012001.010000AC.0002 B5(URP) --H-FL-- cc:1/3 len:6
: --H-FL-- cc:1 len:10
2012-12-11:05:01:21: (commit) SCN 0x0013.24330001, Time 2012-12-11 05:01:22
问题分析:
DSG在目标端加载是安装rowid一条一条记录加载,这样会出现一个问题就是组合列的主键或是唯一索引需要检查唯一性,这样有时就会违反约束
临时的解决方法:
将约束改为延迟校验,到提交是才验证唯一性,需要更改目标端的约束,比较麻烦
drop index TEST_PK_P;
ALTER TABLE SCOTT.TEST_PK ADD (CONSTRAINT TEST_PK_P PRIMARY KEY (CATEGORY_ID, SEQUENCE_NUM)(CATEGORY_ID,SEQUENCE_NUM) deferrable initially deferred ;
修改目标端的data_load.ini
全同步时,不drop index ,同步不做任何做的
[index]
# option: drop - drop index before full sync.
# unusable - set indexes unuseable before full sync.
# no - no action.
before_full_sync = no
# option: rebuild - rebuild index after full sync.
# create - no action.
# none - no action.
after_full_sync = none
重新全同步出问题的表,关于重新同步表,或是新加同步表可以参照:
http://blog.csdn.net/naguang/article/details/8006395
- DSG realsync BUG 之ORA-00001: unique constraint
- ORA-00001: unique constraint (.......) violated
- dsg realsync 新加表同步
- ORA-00001 unique constraint (string.string) violated
- ORA-00001: unique constraint (XINNET20100422.PK_T_THEME_ORDER) violated
- ORA-00001: unique constraint (...) violated解决方案
- ORA-00001: unique constraint (CONTRACT_MGR.PK_T_SUPPLIER) violated
- DSG RealSync VS. Quest Shareplex
- DSG realsync 表空间映射
- Statspack ORA-00001 unique constraint violated错误的解决
- ORA-00001 unique constraint violated错误的解决
- ORA-00001 unique constraint violated错误的解决 (
- ORA-00001: unique constraint violated并不一定是主键冲突
- ORA-00001: unique constraint (...) violated并不一定是数据冲突
- ORA-00001: unique constraint (...) violated并不一定是数据冲突
- impdp ORA-00001: unique constraint (SYS.I_JOB_JOB) violated
- ORA-00001: unique constraint (...) violated并不一定是数据冲突
- java.sql.BatchUpdateException: ORA-00001: unique constraint (IEVSP.PK_C_CSERVICES) violated
- 小语种网站的建站专家应该啥样?
- Java正则表达式——分组与捕获
- Linux/Windows下通过Console发送EOF
- Java正则表达式获取匹配结果
- SQL1072C异常
- DSG realsync BUG 之ORA-00001: unique constraint
- Blocks编程
- Asp.net中Json格式转换为DataTable
- android 解锁
- OSI七层模型概括,简单明了
- 各种图表控件推荐和对比评测
- -bash: wget: command not found 解决方法
- ibatis的SqlMap.xml中传递多参数
- load_sysfile;CreateService