ORA-01410: invalid ROWID

最新推荐文章于 2024-07-30 08:30:00 发布

原创最新推荐文章于 2024-07-30 08:30:00 发布 · 2.2k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#索引 #cache #object #数据库

ORACLE备份恢复专栏收录该内容

1 篇文章

订阅专栏

本文介绍了当遇到ORA-01410错误时，如何通过调整会话设置、启用跟踪、执行全表扫描等步骤进行问题排查。通过查看跟踪文件发现，错误源于索引的逻辑坏块，最终定位到特定索引块并解决了问题。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

关于 ORA-01410一般由于通过索引访问数据库的时候,索引出现了逻辑坏块导致的。

19:42:33 NC5011@ganso> alter session set db_file_multiblock_read_count=1;  

Session altered.

Elapsed: 00:00:00.02
19:42:48 NC5011@ganso> alter session set events 'immediate trace name trace_buffer_on level 1048576';

Session altered.

Elapsed: 00:00:00.00
19:42:58 NC5011@ganso> alter session set events '10200 trace name context forever, level 1';

Session altered.

Elapsed: 00:00:00.00
19:43:07 NC5011@ganso> select /*+ full(a) */ count(*) from to_bill_b a; 

select /*+ full(a) no_index(a) */ count(*) from to_bill_b a
                                                *
ERROR at line 1:
ORA-01410: invalid ROWID


Elapsed: 00:09:06.99
19:52:35 NC5011@ganso>  alter session set events 'immediate trace name trace_buffer_off';

Session altered.


alter session set events '10200 trace name context off';


Elapsed: 00:00:00.12
19:53:07 NC5011@ganso> @gettrc


TRACE_FILE_NAME
--------------------------------------------------------------------------------
/oracle/admin/ganso/udump/ganso_ora_300220.trc


Elapsed: 00:00:01.55


CR exa ret 9 on:  700000010013898  scn: 0xffff.ffffffff  xid: 0x0000.000.00000000  uba: 0x00000000.0000.00  scn: 0xffff.ffffffff  sfl: 0
Consistent read finished for block 0 : 40f59f
Consistent read finished for block 0 : 40f59f
Consistent read started for block 0 : 0040081a
  env: (scn: 0x0002.8016b27f  xid: 0x0000.000.00000000  uba: 0x00000000.0000.00  statement num=0  parent xid: xid: 0x0000.000.00000000  scn: 0x0000.00000000 0sch: scn: 0x0000.00000000)
CR exa ret 2 on:  700000010013898  scn: 0xffff.ffffffff  xid: 0x0000.000.00000000  uba: 0x00000000.0000.00  scn: 0xffff.ffffffff  sfl: 0
Consistent read finished for block 0 : 40081a
Consistent read started for block 0 : 0040f59f
  env: (scn: 0x0002.8016b27f  xid: 0x0000.000.00000000  uba: 0x00000000.0000.00  statement num=0  parent xid: xid: 0x0000.000.00000000  scn: 0x0000.00000000 0sch: scn: 0x0000.00000000)
CR exa ret 9 on:  700000010013898  scn: 0xffff.ffffffff  xid: 0x0000.000.00000000  uba: 0x00000000.0000.00  scn: 0xffff.ffffffff  sfl: 0
Consistent read finished for block 0 : 40f59f
Consistent read finished for block 0 : 40f59f


19:56:24 NC5011@ganso> select to_number('40f59f','xxxxxx')  from dual;


TO_NUMBER('40F59F','XXXXXX')
----------------------------
                     4257183


Elapsed: 00:00:00.00


19:56:40 NC5011@ganso> SELECT DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(4257183),DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(4257183) FROM DUAL;


DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(4257183)
---------------------------------------------
DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(4257183)
----------------------------------------------
                                            1
                                         62879



19:57:25 NC5011@ganso> alter  system  dump datafile 1 block 62879;


System altered.


Elapsed: 00:00:00.03
19:59:13 NC5011@ganso> @gettrc


TRACE_FILE_NAME
--------------------------------------------------------------------------------
/oracle/admin/ganso/udump/ganso_ora_300220.trc


Elapsed: 00:00:00.92


Block header dump:  0x0040f59f
 Object id on Block? Y
 seg/obj: 0x101  csc: 0x02.7f7e97d2  itc: 2  flg: O  typ: 2 - INDEX
     fsl: 0  fnx: 0x0 ver: 0x01
 
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0003.004.000b8669  0x2a4012bd.9aee.01  CB--    0  scn 0x0002.7f7e9499
0x02   0x001e.007.0005e016  0x2a40137b.c498.09  --U-    8  fsc 0x0000.7f7e97df
 
Leaf block dump
===============
header address 4567838812=0x11043b05c
kdxcolev 0
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y
kdxconco 3
kdxcosdc 90
kdxconro 346
kdxcofbo 728=0x2d8
kdxcofeo 1702=0x6a6
kdxcoavs 974
kdxlespl 0
kdxlende 0
kdxlenxt 0=0x0
kdxleprv 4257181=0x40f59d
kdxledsz 0
kdxlebksz 8032
row#0[4510] flag: ------, lock: 0, len=18
col 0; len 5; (5):  c4 03 34 2b 07
col 1; len 2; (2):  c1 0b
col 2; len 6; (6):  00 41 5a f1 00 50
row#1[4528] flag: ------, lock: 0, len=18
col 0; len 5; (5):  c4 03 34 2b 07
col 1; len 2; (2):  c1 0c
col 2; len 6; (6):  00 40 f4 47 00 10
row#2[4546] flag: ------, lock: 0, len=18
col 0; len 5; (5):  c4 03 34 2b 07
col 1; len 2; (2):  c1 0d
col 2; len 6; (6):  00 41 5a f1 00 53
row#3[4564] flag: ------, lock: 0, len=18
col 0; len 5; (5):  c4 03 34 2b 07
col 1; len 2; (2):  c1 0e
col 2; len 6; (6):  00 41 5a f1 00 2e
row#4[4582] flag: ------, lock: 0, len=18
col 0; len 5; (5):  c4 03 34 2b 07


20:01:07 NC5011@ganso> select to_number('101','xxx') from dual;


TO_NUMBER('101','XXX')
----------------------
                   257


Elapsed: 00:00:00.00
20:01:17 NC5011@ganso> select object_name,object_type  from dba_objects where object_id=257;


OBJECT_NAME
--------------------------------------------------------------------------------
OBJECT_TYPE
-------------------
I_HH_OBJ#_INTCOL#  INDEX

Elapsed: 00:00:00.25

可以看到当前的一致性读到的 dba 40f59f 为  I_HH_OBJ#_INTCOL#  INDEX 明显是由于内容的缪误导致的


alter  system flush buffer_cache; 解决