查找某一行索引位于那个块并dump , 修改该块模拟ORA-600(原创)

最新推荐文章于 2024-03-24 20:34:46 发布

congbian2246

最新推荐文章于 2024-03-24 20:34:46 发布

阅读量108

点赞数

创建表和索引
create table temp as select rownum rn,owner username,created dt from all_objects where rownum<500;
alter table temp add constraint pk_temp primary key (rn);

10046查看到增加约束与删除经束时的一些与con$相关的语句.
select con# from con$ where owner#=58 and name='PK_TEMP';

STAT #2 id=1 cnt=0 pid=0 pos=1 bj=28 p='TABLE ACCESS BY INDEX ROWID CON$ (cr=2 pr=0 pw=0 time=896 us)'
STAT #2 id=2 cnt=0 pid=1 pos=1 bj=48 p='INDEX UNIQUE SCAN I_CON1 (cr=2 pr=0 pw=0 time=846 us)'

update con$ set con#='_NEXT_CONSTRAINT' where owner#=8581 and name=0

insert into con$(owner#,name,con#)values(58,'PK_TEMP',8580);

drop 的过程
select name from con$ where con#=8580;

delete from con$ where owner#=58 and name='PK_TEMP';

确认查找约束时会用到索引I_CON1,确认索引用到的列.

INDEX_NAME COLUMN_NAME
---------- --------------------
I_CON2 CON#
I_CON1 OWNER#
I_CON1 NAME

select rowid from con$ where name='PK_TEMP';
AAAAAcAABAAAN4nABt

将rowid转换成file,block,然后dump block,再用
select dump(58,16),dump('PK_TEMP',16) from dual;

得出的结果到trace file中寻找相对应的记录.

tab 0, row 109, @0xa6a
tl: 18 fb: --H-FL-- lb: 0x1 cc: 3
col 0: [ 2] c1 3b
col 1: [ 7] 50 4b 5f 54 45 4d 50
col 2: [ 3] c2 56 52

现在开始寻找index 记录所在的块
首先dump index.

sys@TONY> select object_id from dba_objects where object_name='I_CON1';

OBJECT_ID
----------
48

sys@TONY> alter session set events 'immediate trace name treedump level 48';
trace file 结果如下
branch: 0x40013a 4194618 (0: nrow: 32, level: 1)
leaf: 0x40013b 4194619 (-1: nrow: 247 rrow: 246)
leaf: 0x401cba 4201658 (0: nrow: 171 rrow: 170)
leaf: 0x401cbb 4201659 (1: nrow: 118 rrow: 109)
leaf: 0x401cbe 4201662 (2: nrow: 71 rrow: 70)
leaf: 0x401cbf 4201663 (3: nrow: 166 rrow: 166)

做tree dump后你可以得到root接点的DBA,转换为file,block,再dump这个block

begin
dbms_output.put_line(dbms_utility.data_block_address_file(dba=>4194618) );
dbms_output.put_line(dbms_utility.data_block_address_block(dba=>4194618));
end;
结果 1 314

alter system dump datafile 1 block 314;

SQL> select owner#,name from con$ where rowid='AAAAAcAABAAAN4nABt';

OWNER# NAME
---------- ------------------------------------------------------------
58 PK_TEMP
SQL> select dump(58,16),dump('PK_TEMP',16) from dual;

DUMP(58,16)
------------------------------------
DUMP('PK_TEMP',16)
--------------------------------------------------------------------
Typ=2 Len=2: c1,3b
Typ=96 Len=7: 50,4b,5f,54,45,4d,50

然后看刚才dump出来的文件,查找接近c1 3b的记录
row#28[7592] dba: 4251168=0x40de20
col 0; len 2; (2): c1 34
col 1; len 10; (10): 53 59 53 5f 43 30 30 35 30 34
row#29[7577] dba: 4256825=0x40f439
col 0; len 2; (2): c1 3f
col 1; len 7; (7): 53 54 41 54 53 24 4c

这里是 dba: 4251168=0x40de20
然后转化为文件和块号
begin
dbms_output.put_line(dbms_utility.data_block_address_file(dba=>4251168) );
dbms_output.put_line(dbms_utility.data_block_address_block(dba=>4251168));
end;
用结果
alter system dump datafile 1 block 56864;

查看trace 文件找到

row#139[1930] flag: ------, lock: 2, len=19, data:(6): 00 40 de 27 00 6d
col 0; len 2; (2): c1 3b
col 1; len 7; (7): 50 4b 5f 54 45 4d 50

可以确认到index 在 datafile 1 block 56864 . 记录在块中的地址为
1930+44+24*ITL数即 1930+44+48=2022

用bbed查看

set file 1
set block 56884
dump offset 2022

00000040 de27006d 02c13b07

可以观察到00 40 de 27 00 6d 这个指向表块的rowid
如果要模拟
alter table tony.temp drop primary key
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [atbdro4], [], [], [], [], [], [], []

可以这样
m /x 03 offset 2029 (这样就将006d 修改为0003)
sum apply

存盘退出,并重启DB,执行上面的命令即可产生上述的错误.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/12482/viewspace-709848/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/12482/viewspace-709848/