Analyzing a system state dump for an ORA-01410 error
Date: Tue, 17 Mar 2009 08:53:44 -0500
Oracle 18.104.22.168/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:
- 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
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:
to_char(block_id+blocks-1,'XXXXXX') last_block, blocks
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?