oracle查询报8103,select 操作报ora-8103

按metalink 说

In 9.2.0.8 or above if ktrget is in the call stack trace for an ORA-8103, setting Event 10236 will include the block number causing the error:

alter session set events '8103 trace name errorstack level 3';

alter session set events '10236 trace name context forever, level 1';

alter session set tracefile_identifier='ORA8103';

run the query that produces the error ORA-8103 and identify the trace with the form of _ora__ORA8103.trc

Open the trace file and if function ktrget is in the call stack trace, then the trace file may have:

KTRVAC: path typ=22, rdba=32810e06

Meaning that the error is produced in rdba=0x32810e06 . Convert that number to decimal and get the relative_fno, block#:

select dbms_utility.data_block_address_file(847318534) relative_fno

,dbms_utility.data_block_address_block(847318534) block#

from dual;

RELATIVE_FNO BLOCK#

------------ ----------

202 69126

Then use Note <> to identify the object; section "Identify the Corrupt Object".

Identify the object with event 10200.

Event 10200 can be used to identify the last accessed block when this error is produced. event trace_buffer_on can be used to generate a smaller trace file.  Errorstack and event 10236 can be added to get a more complete trace file:

alter session set max_dump_file_size=unlimited;

alter session set db_file_multiblock_read_count=1;

alter session set events 'immediate trace name trace_buffer_on level 1048576';

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

alter session set events '8103 trace name errorstack level 3';

alter session set events '10236 trace name context forever, level 1';

alter session set tracefile_identifier='ORA8103';

run the query that produces the error ORA-8103

alter session set events 'immediate trace name trace_buffer_off';

exit

Identify the trace with the form of _ora__ORA8103.trc

In the trace file generated locate last information about:

"Consistent read started for block : "

where XXX is the tablespace number and YYY is the rdba for that block.  Normally this is the affected block.