Analyzing a system state dump for an ORA-01410 error

From: Schauss, R. Peter (IT Solutions) <"Schauss,>
Date: Tue, 17 Mar 2009 08:53:44 -0500
Message-ID: <CD9150D80CFCFB42BC73C40791C1E0190314443C_at_XMBIL112.northgrum.com>

Oracle 8.1.7.4/Solaris-SunOS 5.9

I am still trying to produce definitive proof to my management that the ORA-01410 errors we get on our data warehouse reports are caused by running them at the same time that that the ETL runs (tables being truncated, indexes being dropped and rebuilt...).

I added a login trigger to the user that runs the reports to force a system state dump whenever an ORA-01410 occurs. Now I need to interpret the dump and I have a few questions:

1. Lines 16 and 17 look like this:
   • 2009-03-17 09:50:55.272
   • SESSION ID:(85.16274) 2009-03-17 09:50:55.257

Does this mean that the dump was taken at 09:50:55.272 and that the process initiating the dump started at 2009-03-17 09:50:55.257?

2. When I go down to the section for the process which caused the dump there is a line which starts "last wait for 'db file sequential read'" followed by "file#=6, block#=aeb73, blocks=1". Is this supposed to be the location of the invalid rowid? When I check this location several hours later using the query:

select owner,segment_name,segment_type,file_id, to_char(block_id,'XXXXXX')first_block,
to_char(block_id+blocks-1,'XXXXXX') last_block, blocks
from
dba_extents
where
file_id=6
and to_number ('AEB73','XXXXX') between block_id and block_id+blocks-1 order by block_id;

I get a valid and plausible table name. (Or am I missing something here?).

3. Did the "last wait" information pertain to the last successful call in which case I should be looking further down in the section for this process?

Thanks,
Peter Schauss

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Mar 17 2009 - 08:53:44 CDT