|
这几天在做Oracle分区技术的培训稿，为了保证培训效果，在9i,10g,11g上分别对各种分区技术和管理方法进行了详细的测试，没想到就踩到了这个雷.下面是详细的过程,希望对后学者有所帮助.
下面是这个bug的再现过程：
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.3.0
Connected as study
SQL> create table part_demo
2 (
3 region number(4),
4 recdate date,
5 servnumber varchar2(11)
6 ) tablespace users;
Table created
SQL> create index g_range_part_demo on part_demo(region,servnumber)
2 global
3 partition by hash (region)
4 partitions 4;
Index created
SQL> select index_name,partition_name,status,tablespace_name,partition_position
2 from user_ind_partitions
3 where index_name=upper('g_range_part_demo')
4 order by partition_position;
INDEX_NAME PARTITION_NAME STATUS TABLESPACE_NAME PARTITION_POSITION
------------------- ---------------- -------- ---------------- ------------------
G_RANGE_PART_DEMO SYS_P125 USABLE STUDY 1
G_RANGE_PART_DEMO SYS_P126 USABLE STUDY 2
G_RANGE_PART_DEMO SYS_P127 USABLE STUDY 3
G_RANGE_PART_DEMO SYS_P128 USABLE STUDY 4
SQL>
SQL> select obj#,dataobj#,bo#,part#,flags
2 from SYS.INDPART$
3 where obj# in (select object_id from dba_objects where object_name='G_RANGE_PART_DEMO');
OBJ# DATAOBJ# BO# PART# FLAGS
---------- ---------- ---------- ---------- ----------
52650 52650 52649 1 0
52652 52652 52649 3 0
52651 52651 52649 2 0
52653 52653 52649 4 0
SQL> select obj#,dataobj#,bo#,part#,flags
2 from sys.indpartv$
3 where obj# in (select object_id from dba_objects where object_name='G_RANGE_PART_DEMO');
OBJ# DATAOBJ# BO# PART# FLAGS
---------- ---------- ---------- ---------- ----------
52650 52650 52649 1 0
52651 52651 52649 2 0
52652 52652 52649 3 0
52653 52653 52649 4 0
SQL>
SQL> alter index g_range_part_demo drop partition sys_p125;
Index altered
SQL> select index_name,partition_name,status,tablespace_name,partition_position
2 from user_ind_partitions
3 where index_name=upper('g_range_part_demo')
4 order by partition_position;
INDEX_NAME PARTITION_NAME STATUS TABLESPACE_NAME PARTITION_POSITION
------------------- ---------------- -------- ---------------- ------------------
G_RANGE_PART_DEMO SYS_P126 USABLE STUDY 1
G_RANGE_PART_DEMO SYS_P127 USABLE STUDY 2
G_RANGE_PART_DEMO SYS_P128 USABLE STUDY 3
SQL>
SQL> select obj#,dataobj#,bo#,part#,flags
2 from SYS.INDPART$
3 where obj# in (select object_id from dba_objects where object_name='G_RANGE_PART_DEMO')
4 order by obj#;
OBJ# DATAOBJ# BO# PART# FLAGS
---------- ---------- ---------- ---------- ----------
52651 52651 52649 2 0
52652 52652 52649 3 0
52653 52653 52649 4 0
SQL> select obj#,dataobj#,bo#,part#,flags
2 from sys.indpartv$
3 where obj# in (select object_id from dba_objects where object_name='G_RANGE_PART_DEMO')
4 order by obj#;
OBJ# DATAOBJ# BO# PART# FLAGS
---------- ---------- ---------- ---------- ----------
52651 52651 52649 1 0
52652 52652 52649 2 0
52653 52653 52649 3 0
SQL> alter index g_range_part_demo modify partition SYS_P126 unusable;
Index altered
SQL> alter index g_range_part_demo modify partition SYS_P127 unusable;
Index altered
SQL> select index_name,partition_name,status,tablespace_name,partition_position
2 from user_ind_partitions
3 where index_name=upper('g_range_part_demo')
4 order by partition_position;
INDEX_NAME PARTITION_NAME STATUS TABLESPACE_NAME PARTITION_POSITION
------------------- ---------------- -------- ----------------- ------------------
G_RANGE_PART_DEMO SYS_P126 UNUSABLE STUDY 1
G_RANGE_PART_DEMO SYS_P127 UNUSABLE STUDY 2
G_RANGE_PART_DEMO SYS_P128 USABLE STUDY 3
SQL>
SQL> select obj#,dataobj#,bo#,part#,flags
2 from SYS.INDPART$
3 where obj# in (select object_id from dba_objects where object_name='G_RANGE_PART_DEMO')
4 order by obj#;
OBJ# DATAOBJ# BO# PART# FLAGS
---------- ---------- ---------- ---------- ----------
52651 52651 52649 2 1
52652 52652 52649 3 1
52653 52653 52649 4 0
SQL> select obj#,dataobj#,bo#,part#,flags
2 from sys.indpartv$
3 where obj# in (select object_id from dba_objects where object_name='G_RANGE_PART_DEMO')
4 order by obj#;
OBJ# DATAOBJ# BO# PART# FLAGS
---------- ---------- ---------- ---------- ----------
52651 52651 52649 1 1
52652 52652 52649 2 1
52653 52653 52649 3 0
SQL>
SQL> alter index g_range_part_demo rebuild partition sys_p127;
alter index g_range_part_demo rebuild partition sys_p127
*
第 1 行出现错误:
ORA-00604: 递归 SQL 级别 1 出现错误
ORA-00001: 违反唯一约束条件 (SYS.I_INDPART_BOPART$)
SQL> select index_owner,index_name,table_name,column_name
2 from dba_ind_columns where index_name='I_INDPART_BOPART$'
3 order by column_position;
INDEX_OWNER INDEX_NAME TABLE_NAME COLUMN_NAME
-------------- ------------------ ------------ -----------
SYS I_INDPART_BOPART$ INDPART$ BO#
SYS I_INDPART_BOPART$ INDPART$ PART#
SQL>
SQL>
SQL> select object_name,subobject_name,object_id
2 from dba_objects where object_name='G_RANGE_PART_DEMO'
3 order by object_id;
OBJECT_NAME SUBOBJECT_NAME OBJECT_ID
-------------------- ---------------- ----------
G_RANGE_PART_DEMO 52649
G_RANGE_PART_DEMO SYS_P126 52651
G_RANGE_PART_DEMO SYS_P127 52652
G_RANGE_PART_DEMO SYS_P128 52653
SQL>
做一个trace，可以看到里面有这样的SQL语句：
update indpart$ set dataobj# = :1, part# = :2, flags = :3, ts# = :4, file# = :5,
block# = :6, pctfree$ = :7, initrans = :8, maxtrans = :9, analyzetime = :10,
samplesize = :11, rowcnt = :12, blevel = :13, leafcnt = :14, distkey = :15,
lblkkey = :16, dblkkey = :17, clufac = :18, pctthres$ = :19
where obj# = :20
...
Bind#0
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=0001 frm=00 csi=00 siz=216 off=0
kxsbbbfp=070db6bc bln=22 avl=04 flg=05
value=52656
Bind#1
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=0001 frm=00 csi=00 siz=0 off=24
kxsbbbfp=070db6d4 bln=22 avl=02 flg=01
value=2
Bind#2
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=0001 frm=00 csi=00 siz=0 off=48
kxsbbbfp=070db6ec bln=22 avl=02 flg=01
value=2
Bind#3
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=0001 frm=00 csi=00 siz=0 off=72
kxsbbbfp=070db704 bln=22 avl=02 flg=01
value=6
Bind#4
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=0001 frm=00 csi=00 siz=0 off=96
kxsbbbfp=070db71c bln=22 avl=02 flg=01
value=5
Bind#5
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=0001 frm=00 csi=00 siz=0 off=120
kxsbbbfp=070db734 bln=22 avl=03 flg=01
value=1179
Bind#6
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=0001 frm=00 csi=00 siz=0 off=144
kxsbbbfp=070db74c bln=22 avl=02 flg=01
value=10
Bind#7
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=0001 frm=00 csi=00 siz=0 off=168
kxsbbbfp=070db764 bln=22 avl=02 flg=01
value=2
Bind#8
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=0001 frm=00 csi=00 siz=0 off=192
kxsbbbfp=070db77c bln=22 avl=03 flg=01
value=255
Bind#9
oacdty=12 mxl=07(07) mxlc=00 mal=00 scl=00 pre=00
oacflg=10 fl2=0001 frm=00 csi=00 siz=8 off=0
kxsbbbfp=658addd9 bln=07 avl=07 flg=09
value="12/7/2007 13:57:7"
Bind#10
oacdty=02 mxl=22(00) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=0001 frm=00 csi=00 siz=240 off=0
kxsbbbfp=070db5c0 bln=22 avl=00 flg=05
Bind#11
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=0001 frm=00 csi=00 siz=0 off=24
kxsbbbfp=070db5d8 bln=22 avl=01 flg=01
value=0
Bind#12
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=0001 frm=00 csi=00 siz=0 off=48
kxsbbbfp=070db5f0 bln=22 avl=01 flg=01
value=0
Bind#13
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=0001 frm=00 csi=00 siz=0 off=72
kxsbbbfp=070db608 bln=22 avl=01 flg=01
value=0
Bind#14
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=0001 frm=00 csi=00 siz=0 off=96
kxsbbbfp=070db620 bln=22 avl=01 flg=01
value=0
Bind#15
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=0001 frm=00 csi=00 siz=0 off=120
kxsbbbfp=070db638 bln=22 avl=01 flg=01
value=0
Bind#16
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=0001 frm=00 csi=00 siz=0 off=144
kxsbbbfp=070db650 bln=22 avl=01 flg=01
value=0
Bind#17
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=0001 frm=00 csi=00 siz=0 off=168
kxsbbbfp=070db668 bln=22 avl=01 flg=01
value=0
Bind#18
oacdty=02 mxl=22(00) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=0001 frm=00 csi=00 siz=0 off=192
kxsbbbfp=070db680 bln=22 avl=00 flg=01
Bind#19
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=0001 frm=00 csi=00 siz=0 off=216
kxsbbbfp=070db698 bln=22 avl=04 flg=01
value=52652
从这个trace中可以看到，实际上就是和第一个分区的记录信息重复了.从上面的记录中可以看到
原来SYS.INDPART$中有如下记录：
52651 52651 52649 2 1
52652 52652 52649 3 1
而这个rebuild的过程要把obj#=52652(对应SYS_P127分区）这行记录的dataobj#修改为52656，part#修改为2，而bo#不变，而SYS.I_INDPART_BOPART$这个唯一索引建立在：
create unique index SYS.I_INDPART_BOPART$ on SYS.INDPART$ (BO#, PART#)
所以出现这个错误也就可以理解了。
到metalink上查了一下，Oracle是这么解释的 ：
Public support update: the issue was fixed by patch 3748430 only after mandatory patch 5117016 had been installed, therefore this was closed as 'not a bug'
进一步查询得志，这个问题到10.2.0.4才真正fix.
除了上面的解决方法，按照如下的方法(按照顺序，从最小的object开始，按顺序做)也可以解决，
或许是一种巧合，太深的东西实在不想费力研究下去了，懒人有懒福！
SQL> select index_name,partition_name,status,tablespace_name,partition_position
2 from user_ind_partitions
3 where index_name=upper('g_range_part_demo')
4 order by partition_position;
INDEX_NAME PARTITION_NAME STATUS TABLESPACE_NAME PARTITION_POSITION
-------------------- ----------------- -------- ---------------- ------------------
G_RANGE_PART_DEMO SYS_P126 UNUSABLE STUDY 1
G_RANGE_PART_DEMO SYS_P127 UNUSABLE STUDY 2
G_RANGE_PART_DEMO SYS_P128 USABLE STUDY 3
SQL>
SQL> alter index G_RANGE_PART_DEMO rebuild partition sys_p127;
alter index G_RANGE_PART_DEMO rebuild partition sys_p127
*
第 1 行出现错误:
ORA-00604: 递归 SQL 级别 1 出现错误
ORA-00001: 违反唯一约束条件 (SYS.I_INDPART_BOPART$)
SQL> alter index G_RANGE_PART_DEMO rebuild partition sys_p126;
索引已更改。
SQL> alter index G_RANGE_PART_DEMO rebuild partition sys_p127;
索引已更改。
SQL>
SQL> select index_name,partition_name,status,tablespace_name,partition_position
2 from user_ind_partitions
3 where index_name=upper('g_range_part_demo')
4 order by partition_position;
INDEX_NAME PARTITION_NAME STATUS TABLESPACE_NAME PARTITION_POSITION
------------------- ---------------- -------- ------------------ ------------------
G_RANGE_PART_DEMO SYS_P126 USABLE STUDY 1
G_RANGE_PART_DEMO SYS_P127 USABLE STUDY 2
G_RANGE_PART_DEMO SYS_P128 USABLE STUDY 3
SQL>
好了，到这里这个问题就算是基本解决了...
|
