看了很多文章关于 ora-8102的错误,于是自己也想尝试 8102的错误,看看自己能不能解决。昨天在10Gsolaris 上操作好像没有效果,oracle 10g 没有这个索引I_obj4,今天在oracle11g 试了一把,结果真的出现如下错误了。
select max(dataobj#) from obj$
--------------------------------------------------------
84559
通过 dbms_rowid 找到 87559 在哪个块哪个行上
dle@0> select dbms_rowid.rowid_relative_fno(rowid) file#,dbms_rowid.rowid_block_number(rowid) block#,
2 dbms_rowid.rowid_row_number(rowid) row#
3 from obj$ where DATAOBJ#=84559;
FILE# BLOCK# ROW#
--------- ---------- ----------
1 241 27
因为是Windows所以 实际块号为 242,1号块是头块。
然后通过bbed 修改 改成 84576
set file 1 block 242
p *kdbr[27]
BBED> x/rnnnncnnnnnncn
rowdata[0] @383
----------
flag@383: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@384: 0x01
cols@385: 18
col 0[2] @386: 1
col 1[4] @389: 84559
col 2[1] @394: 0
col 3[12] @396: -0
col 4[2] @409: ?
col 5[0] @412: *NULL*
col 6[1] @413: 0
col 7[7] @415: -0
col 8[7] @423: -0
col 9[7] @431: -0
col 10[1] @439: 0
col 11[0] @441: *NULL*
col 12[0] @442: *NULL*
col 13[1] @443: 0
col 14[0] @445: *NULL*
col 15[1] @446: 0
col 16[4] @448: 65535
col 17[1] @453: 0
BBED> x/rnnc
rowdata[0] @383
----------
flag@383: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@384: 0x01
cols@385: 18
col 0[2] @386: 1
col 1[4] @389: 84576
col 2[1] @394: .
col 3[12] @396: _NEXT_OBJECT
col 4[2] @409: ?
col 5[0] @412: *NULL*
col 6[1] @413: .
col 7[7] @415: xn....&
col 8[7] @423: xs...1.
col 9[7] @431: xn....&
col 10[1] @439: .
col 11[0] @441: *NULL*
col 12[0] @442: *NULL*
col 13[1] @443: .
col 14[0] @445: *NULL*
col 15[1] @446: .
col 16[4] @448: ?8$
col 17[1] @453: .
BBED> set offset 389
OFFSET 389
BBED> d count 20
File: D:\ORACLE\ORADATA\MYORACLE\SYSTEM01.DBF (1)
Block: 242 Offsets: 389 to 408 Db
-----------------------------------------------------------
04c3092e 4d01800c 5f4e4558 545f4f42 4a454354
执行语句果然报如下错误
sys@MYORACLE@125> create table t2 as select * from dba_objects;
create table t2 as select * from dba_objects
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-08102: index key not found, obj# 39, file 1, block 61002 (2)
select /*+ full(t1) */ DATAOBJ#,type#,owner# from obj$ t1
minus
select /*+ index(t i_obj4) */ DATAOBJ#,type#,owner# from obj$ t;
--------------------------------------
84576
通过 10046或者 errstack 如下trace
oer 8102.2 - obj# 39, rdba: 0x0040ee4a(afn 1, blk# 61002)
kdk key 8102.2:
ncol: 4, len: 16
key: (16): 04 c3 09 2e 4d 01 80 01 80 06 00 40 00 f1 00 1b
mask: (2048):
dump 索引kuai
alter system dump datafile 1 block 61002;
row#259[1207] flag: ------, lock: 0, len=18
col 0; len 4; (4): c3 09 2e 3c
col 1; len 1; (1): 80
col 2; len 1; (1): 80
col 3; len 6; (6): 00 40 00 f1 00 1b
找到 c3092e3c 并修改成 c3 09 2e 4d
就好了。
起初 我想 把 obj$ 上 242 数据块 我改成 84576 改成回去 变成原来的值 84599,创建表还好报错 ora-08102 ,可能我库没有关,buffer里的块值还是 84576 。
select max(dataobj#) from obj$
--------------------------------------------------------
84559
通过 dbms_rowid 找到 87559 在哪个块哪个行上
dle@0> select dbms_rowid.rowid_relative_fno(rowid) file#,dbms_rowid.rowid_block_number(rowid) block#,
2 dbms_rowid.rowid_row_number(rowid) row#
3 from obj$ where DATAOBJ#=84559;
FILE# BLOCK# ROW#
--------- ---------- ----------
1 241 27
因为是Windows所以 实际块号为 242,1号块是头块。
然后通过bbed 修改 改成 84576
set file 1 block 242
p *kdbr[27]
BBED> x/rnnnncnnnnnncn
rowdata[0] @383
----------
flag@383: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@384: 0x01
cols@385: 18
col 0[2] @386: 1
col 1[4] @389: 84559
col 2[1] @394: 0
col 3[12] @396: -0
col 4[2] @409: ?
col 5[0] @412: *NULL*
col 6[1] @413: 0
col 7[7] @415: -0
col 8[7] @423: -0
col 9[7] @431: -0
col 10[1] @439: 0
col 11[0] @441: *NULL*
col 12[0] @442: *NULL*
col 13[1] @443: 0
col 14[0] @445: *NULL*
col 15[1] @446: 0
col 16[4] @448: 65535
col 17[1] @453: 0
BBED> x/rnnc
rowdata[0] @383
----------
flag@383: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@384: 0x01
cols@385: 18
col 0[2] @386: 1
col 1[4] @389: 84576
col 2[1] @394: .
col 3[12] @396: _NEXT_OBJECT
col 4[2] @409: ?
col 5[0] @412: *NULL*
col 6[1] @413: .
col 7[7] @415: xn....&
col 8[7] @423: xs...1.
col 9[7] @431: xn....&
col 10[1] @439: .
col 11[0] @441: *NULL*
col 12[0] @442: *NULL*
col 13[1] @443: .
col 14[0] @445: *NULL*
col 15[1] @446: .
col 16[4] @448: ?8$
col 17[1] @453: .
BBED> set offset 389
OFFSET 389
BBED> d count 20
File: D:\ORACLE\ORADATA\MYORACLE\SYSTEM01.DBF (1)
Block: 242 Offsets: 389 to 408 Db
-----------------------------------------------------------
04c3092e 4d01800c 5f4e4558 545f4f42 4a454354
执行语句果然报如下错误
sys@MYORACLE@125> create table t2 as select * from dba_objects;
create table t2 as select * from dba_objects
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-08102: index key not found, obj# 39, file 1, block 61002 (2)
select /*+ full(t1) */ DATAOBJ#,type#,owner# from obj$ t1
minus
select /*+ index(t i_obj4) */ DATAOBJ#,type#,owner# from obj$ t;
--------------------------------------
84576
通过 10046或者 errstack 如下trace
oer 8102.2 - obj# 39, rdba: 0x0040ee4a(afn 1, blk# 61002)
kdk key 8102.2:
ncol: 4, len: 16
key: (16): 04 c3 09 2e 4d 01 80 01 80 06 00 40 00 f1 00 1b
mask: (2048):
dump 索引kuai
alter system dump datafile 1 block 61002;
row#259[1207] flag: ------, lock: 0, len=18
col 0; len 4; (4): c3 09 2e 3c
col 1; len 1; (1): 80
col 2; len 1; (1): 80
col 3; len 6; (6): 00 40 00 f1 00 1b
找到 c3092e3c 并修改成 c3 09 2e 4d
就好了。
起初 我想 把 obj$ 上 242 数据块 我改成 84576 改成回去 变成原来的值 84599,创建表还好报错 ora-08102 ,可能我库没有关,buffer里的块值还是 84576 。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/21980353/viewspace-1790601/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/21980353/viewspace-1790601/