开始了19c的躺雷模式， 再次建议选择ORACLE 19C版本时安装19.11 以上RU。 最近一客户升级了19C, 本月拆分区时遇到了ora-1 内部字典表数据唯一性冲突， 下面简单记录，报错信息如下:
ALTER TABLE ANBOB.TLOG SPLIT PARTITION PART_110_MAX AT (110, TO_DATE(' 2022-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')) INTO (PARTITION PART_110_202201 ,PARTITION PART_310_MAX )
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-00001: unique constraint (SYS.I_INDPART_BOPART$) violated
分析递归SQL,当然做个10046 event trace 就可以，从报错的索引也能知道对象。 下面先看10046 trace
-- 先找exec error ERROR #140737256599760:err=1 tim=3429453098383 -- 根据cursor# 找binds EXEC #140737256599760:c=184,e=184,p=0,cr=2,cu=7,mis=0,r=1,dep=1,og=4,plh=2739666332,tim=3429453097448 CLOSE #140737256599760:c=1,e=1,dep=1,type=3,tim=3429453097465 PARSE #140737256599760:c=4,e=3,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=2739666332,tim=3429453097475 BINDS #140737256599760: Bind#0 oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00 oacflg=00 fl2=1000001 frm=00 csi=00 siz=48 off=0 kxsbbbfp=7ffff2f4b010 bln=22 avl=03 flg=05 value=220 Bind#1 oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00 oacflg=00 fl2=1000001 frm=00 csi=00 siz=0 off=24 kxsbbbfp=7ffff2f4b028 bln=22 avl=05 flg=01 value=5832439 -- 根据 currsor# 找sql parse PARSING IN CURSOR #140737256599760 len=46 dep=1 uid=0 oct=6 lid=0 tim=3429453095567 hv=3952657371 ad='559374eb0' sqlid='gk5aj2zptjhyv' update indpart$ set part# = :1 where obj# = :2 END OF STMT
Note:
可见是在update indpart$表，把part#更新为220， obj#能确认业务索引对象， 违反了indpart$的唯一约束。
SQL> @ind SYS.I_INDPART_BOPART$ Display indexes where table or index name matches %SYS.I_INDPART_BOPART$%... TABLE_OWNER TABLE_NAME INDEX_NAME POS# COLUMN_NAME DSC -------------------- ------------------------------ ------------------------------ ---- ------------------------------ ---- SYS INDPART$ I_INDPART_BOPART$ 1 BO# 2 PART# INDEX_OWNER TABLE_NAME INDEX_NAME IDXTYPE UNIQ STATUS PART TEMP H LFBLKS NDK NUM_ROWS CLUF LAST_ANAL DEGREE VISIBILIT -------------------- ------------------------------ ------------------------------ ---------- ---- -------- ---- ---- -- ---------- ------------- ---------- ---------- --------- ------ --------- SYS INDPART$ I_INDPART_BOPART$ NORMAL YES VALID NO N 1 1 332 332 170 28-DEC-21 1 VISIBLE SQL> @desc indpart$ Name Null? Type ------------------------------- -------- ---------------------------- 1 OBJ# NOT NULL NUMBER 2 DATAOBJ# NUMBER 3 BO# NOT NULL NUMBER 4 PART# NOT NULL NUMBER 5 HIBOUNDLEN NOT NULL NUMBER 6 HIBOUNDVAL LONG 7 FLAGS NOT NULL NUMBER 8 TS# NOT NULL NUMBER 9 FILE# NOT NULL NUMBER 10 BLOCK# NOT NULL NUMBER 11 PCTFREE$ NOT NULL NUMBER 12 PCTTHRES$ NUMBER 13 INITRANS NOT NULL NUMBER 14 MAXTRANS NOT NULL NUMBER 15 ANALYZETIME DATE 16 SAMPLESIZE NUMBER 17 ROWCNT NUMBER 18 BLEVEL NUMBER 19 LEAFCNT NUMBER 20 DISTKEY NUMBER 21 LBLKKEY NUMBER 22 DBLKKEY NUMBER 23 CLUFAC NUMBER 24 SPARE1 NUMBER 25 SPARE2 NUMBER 26 SPARE3 NUMBER 27 INCLCOL NUMBER 28 BHIBOUNDVAL BLOB -- cdpart.bsq create table indpart$ ( obj# number not null, /* object number of partition */ /* DO NOT CREATE INDEX ON DATAOBJ# AS IT WILL BE UPDATED IN A SPACE * TRANSACTION DURING TRUNCATE */ dataobj# number, /* data layer object number */ bo# number not null, /* object number of base index */ part# number not null, /* partition number (see discussion under TABPART$) */ hiboundlen number not null, /* length of high bound value expression */ hiboundval long , /* text of high bound value expression */ ...
NOTE:
内部在更新 indpart$时，因为违反了bo#, part#唯一性冲突。索引基于的对象bo#是不变的，拆分区增加的就是part#. 查询indpart$表，确实记录已存在。 正常情况下基于某个段分区local索引和段分区顺序相同的part#也相同，如表分区part# 对应的分区索引part#是相等的。 这里是因为local索引更新出现了不一致。
下面手动创建个表并还原一下问题。
CREATE TABLE "ANBOB"."T"
( "ID" NUMBER(*,0),
"CTIME" DATE,
"NAME" VARCHAR2(100)
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
STORAGE(
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS"
PARTITION BY RANGE ("CTIME")
(PARTITION "P1" VALUES LESS THAN (TO_DATE(' 2015-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION DEFERRED
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
...
;
CREATE INDEX "ANBOB"."T_I1" ON "ANBOB"."T" ("CTIME", "NAME") LOCAL;
SQL> select obj#,part# ,subobject_name from indpart$ a,dba_objects b where a.obj#=b.object_id and b.object_name='T_I1' and part#>150 order by 2;
OBJ# PART# SUBOBJECT_NAME
---------- ---------- ------------------------------
78804 160 P16
78805 170 P17
78806 180 P18
78807 190 P19
79152 199 P19_1
79156 200 P19_2
79160 201 P19_3
79164 202 P19_4
79168 203 P19_5
79172 204 P19_6
79176 205 P19_7
79180 206 P19_8
79184 207 P19_9
79188 208 P19_10
79192 209 P19_11
79196 219 P19_12
79200 228 P19_13
79199 230 P20
79112 239 P21
79116 249 P22
79120 259 P23
79124 269 P24
79128 279 P25
79132 289 P26
79136 300 P27
79140 311 P28
79144 321 P29
79148 331 P29_1
79147 341 P30
29 rows selected.
SQL> update indpart$ set part#=280 where obj#=79132;
1 row updated.
SQL> commit;
Commit complete.
SQL> alter system flush shared_pool;
System altered.
SQL> alter table anbob.t split partition p25 into(partition p24_1 values less than (to_date('20190401 01','yyyymmdd hh24')),partition p25);
Table altered.
SQL> select obj#,part# ,subobject_name from indpart$ a,dba_objects b where a.obj#=b.object_id and b.object_name='T_I1' and part#>150 order by 2;
OBJ# PART# SUBOBJECT_NAME
---------- ---------- ------------------------------
78804 160 P16
78805 170 P17
78806 180 P18
78807 190 P19
79152 199 P19_1
79156 200 P19_2
79160 201 P19_3
79164 202 P19_4
79168 203 P19_5
79172 204 P19_6
79176 205 P19_7
79180 206 P19_8
79184 207 P19_9
79188 208 P19_10
79192 209 P19_11
79196 219 P19_12
79200 228 P19_13
79199 230 P20
79112 239 P21
79116 249 P22
79120 259 P23
79124 269 P24
79206 278 P24_1
79205 280 P25
79132 289 P26
79136 300 P27
79140 311 P28
79144 321 P29
79148 331 P29_1
79147 341 P30
SQL> alter table anbob.t split partition p25 into(partition p24_2 values less than (to_date('20190401 02','yyyymmdd hh24')),partition p25);
Table altered.
SQL> select obj#,part# ,subobject_name from indpart$ a,dba_objects b where a.obj#=b.object_id and b.object_name='T_I1' and part#>150 order by 2;
OBJ# PART# SUBOBJECT_NAME
---------- ---------- ------------------------------
78804 160 P16
78805 170 P17
78806 180 P18
78807 190 P19
79152 199 P19_1
79156 200 P19_2
79160 201 P19_3
79164 202 P19_4
79168 203 P19_5
79172 204 P19_6
79176 205 P19_7
79180 206 P19_8
79184 207 P19_9
79188 208 P19_10
79192 209 P19_11
79196 219 P19_12
79200 228 P19_13
79199 230 P20
79112 239 P21
79116 249 P22
79120 259 P23
79124 269 P24
79206 278 P24_1
79210 279 P24_2
79209 281 P25
79132 289 P26
79136 300 P27
79140 311 P28
79144 321 P29
79148 331 P29_1
79147 341 P30
31 rows selected.
SQL> update indpart$ set part#=280 where obj#=79148;
1 row updated.
SQL> commit;
Commit complete.
SQL> select obj#,part# ,subobject_name from indpart$ a,dba_objects b where a.obj#=b.object_id and b.object_name='T_I1' and part#>150 order by 2;
OBJ# PART# SUBOBJECT_NAME
---------- ---------- ------------------------------
78804 160 P16
78805 170 P17
78806 180 P18
78807 190 P19
79152 199 P19_1
79156 200 P19_2
79160 201 P19_3
79164 202 P19_4
79168 203 P19_5
79172 204 P19_6
79176 205 P19_7
79180 206 P19_8
79184 207 P19_9
79188 208 P19_10
79192 209 P19_11
79196 219 P19_12
79200 228 P19_13
79199 230 P20
79112 239 P21
79116 249 P22
79120 259 P23
79124 269 P24
79206 278 P24_1
79210 279 P24_2
79148 280 P29_1
79209 281 P25
79132 289 P26
79136 300 P27
79140 311 P28
79144 321 P29
79147 341 P30
31 rows selected.
SQL> alter table anbob.t split partition p25 into(partition p24_3 values less than (to_date('20190401 03','yyyymmdd hh24')),partition p25);
alter table anbob.t split partition p25 into(partition p24_3 values less than (to_date('20190401 03','yyyymmdd hh24')),partition p25)
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-00001: unique constraint (SYS.I_INDPART_BOPART$) violated
SQL> select obj#,part# ,subobject_name from indpart$ a,dba_objects b where a.obj#=b.object_id and b.object_name='T_I1' and part#>150 order by 2;
OBJ# PART# SUBOBJECT_NAME
---------- ---------- ------------------------------
78804 160 P16
78805 170 P17
78806 180 P18
78807 190 P19
79152 199 P19_1
79156 200 P19_2
79160 201 P19_3
79164 202 P19_4
79168 203 P19_5
79172 204 P19_6
79176 205 P19_7
79180 206 P19_8
79184 207 P19_9
79188 208 P19_10
79192 209 P19_11
79196 219 P19_12
79200 228 P19_13
79199 230 P20
79112 239 P21
79116 249 P22
79120 259 P23
79124 269 P24
79206 278 P24_1
79210 279 P24_2
79148 280 P29_1
79209 281 P25
79132 289 P26
79136 300 P27
79140 311 P28
79144 321 P29
79147 341 P30
31 rows selected.
note:
因为oracle part#增长的控制分几种情况， 前2次没有触发,下一篇再分享part#的生成方法， 第3次模拟出了问题。
下面分析问题
select i.obj#, ip.obj#, tp.phypart#, ip.phypart#
from tabpartv$ tp, indpartv$ ip, ind$ i
where ip.bo# = i.obj#
and tp.bo# = i.bo#
and tp.part# = ip.part#
6 and tp.phypart# != ip.phypart#;
OBJ# OBJ# PHYPART# PHYPART#
---------- ---------- ---------- ----------
78788 79148 281 280
78788 79209 289 281
78788 79132 300 289
78788 79136 311 300
78788 79140 321 311
78788 79144 331 321
6 rows selected.
create or replace view tabpartv$
(obj#, dataobj#, bo#, part#, hiboundlen, hiboundval, ts#, file#, block#,
pctfree$, pctused$, initrans, maxtrans, flags, analyzetime, samplesize,
rowcnt, blkcnt, empcnt, avgspc, chncnt, avgrln, phypart#)
as select obj#, dataobj#, bo#,
row_number() over (partition by bo# order by part#),
hiboundlen, hiboundval, ts#, file#, block#, pctfree$, pctused$,
initrans, maxtrans, flags, analyzetime, samplesize, rowcnt, blkcnt,
empcnt, avgspc, chncnt, avgrln, part#
from tabpart$
where bitand(flags, 8388608) = 0 /* filter out hidden partitions */
/
create or replace view indpartv$
(obj#, dataobj#, bo#, part#, hiboundlen, hiboundval, flags, ts#, file#,
block#, pctfree$, pctthres$, initrans, maxtrans, analyzetime, samplesize,
rowcnt, blevel, leafcnt, distkey, lblkkey, dblkkey, clufac, spare1,
spare2, spare3, inclcol, phypart#)
as select obj#, dataobj#, bo#,
row_number() over (partition by bo# order by part#),
hiboundlen, hiboundval, flags, ts#, file#, block#,
pctfree$, pctthres$, initrans, maxtrans, analyzetime, samplesize,
rowcnt, blevel, leafcnt, distkey, lblkkey, dblkkey, clufac, spare1,
spare2, spare3, inclcol, part#
from indpart$
where bitand(flags, 8388608) = 0 /* filter out hidden partitions */
/
note：
这里用到了2个内部视图tabpartv$ 和indpartv$，也是基于tabpart$和indpart$， 列出了问题part#是从拆的分区开始到倒数第二个分区(最大分区除外).
这里能想到的有3种解决方法：
1， 把索引删除，重新创建(not rebuild)
但是有特殊情况，对于本次出问题的是个lob index（SYS_ILxxxxx）, 不可以删除index。并且move lob级联rebuild lob index也无法解决。
2， 安装one-off patch
Bug 32259535 ORA-1/ORA-00001: unique constraint (sys.i_indpart_bopart$) during ALTER TABLE SPLIT PARTITION, 影响范围RU<=19.10.
3, 更新字典基表
下面用使用最快的第3种方法
-- s1
SQL> lock table tabpart$ in exclusive mode;
Table(s) Locked.
SQL> lock table partobj$ in exclusive mode;
Table(s) Locked.
-- s2
create table bakpart_byanbob(ind_obj#, ip_obj#, tbl_phypart#, idx_phypart#) as
select i.obj#, ip.obj#, tp.phypart#, ip.phypart#
from tabpartv$ tp, indpartv$ ip, ind$ i
where ip.bo# = i.obj#
and tp.bo# = i.bo#
and tp.part# = ip.part#
and tp.phypart# != ip.phypart#;
update indpart$ ip
set part# = (select tbl_phypart# from bakpart_byanbob
where ip.obj# = ip_obj#)
where ip.obj# in (select ip_obj# from bakpart_byanbob);
SQL> commit;
Commit complete.
SQL> alter system flush shared_pool;
System altered.
-- s1
SQL> commit;
Commit complete.
SQL> alter table anbob.t split partition p25 into(partition p24_3 values less than (to_date('20190401 03','yyyymmdd hh24')),partition p25);
Table altered.
注意：
问题解决。
以上操作危险，未在专业人员指导下，请勿操作。