Oracle ora01410,案例:Oracle报错ORA-01410: invalid ROWID

天萃荷净

模拟一次关于Oracle报错ORA-01410: invalid ROWID的案例

1、跟踪错误数据表

alter session set max_dump_file_size=unlimited;

alter session set db_file_multiblock_read_count=1;

alter session set events ’10200 trace name context forever, level 1′;

执行下面语句

SELECT COUNT(*) FROM QXTDEV1.TAB_SMS_MT_DOWN

报错

ORA-01410: invalid ROWID

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

2、查看数据库trace跟踪文件尾部

Consistent read started for block 0 : 00400652

env: (scn: 0x0abf.1ee04657 xid: 0×0000.000.00000000 uba: 0×00000000.0000.00 statement num=0 parent xid: xid: 0×0000.000.00000000 scn: 0×0000.00000000 0sch: scn: 0×0000.00000000)

Consistent read finished for block 0 : 400652

Consistent read finished for block 0 : 400652

通过这个文件看到文件读到400652的块的时候终止了，错误应该在这块上

3、分析400652块内容

–转换为10进制

SELECT to_number(’400652′,’xxxxxxxx’) FROM dual;

–求文件号和块号

SELECT DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(4195922),

DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(4195922)

FROM DUAL;

4、dump文件号为1，块号为1618的内容

alter system dump datafile 1 block 1618;

Block header dump: 0×00400652

Object id on Block? Y

seg/obj: 0xdecsc: 0xabf.1e400c0c itc: 2 flg: O typ: 1 – DATA

fsl: 0 fnx: 0×0 ver: 0×01

得到object_id为de，转化为十进制

SELECT to_number(‘de’,'xx’) FROM dual;

查询all_objects表

SELECT * FROM All_Objects WHERE object_id=222;

发现这个数据库(绝对块号400652，相对：文件号1，块号1618)指向的表为dual

5、案例分析结论

我们的QXTDEV1.TAB_SMS_MT_DOWN表中的数据块指向了sys.dual表，从而出现了ORA-01410错误

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之案例:Oracle报错ORA-01410: invalid ROWID