RE: check for (empty?) blocks which leads to ORA-8103 in a datafile?
Date: Wed, 9 Sep 2009 17:55:10 -0400
Sigh. I left out multiblock rows (ie. the kind of chained rows that are not simply "migrated". If I recall correctly multiblock rows born that way are contiguous blocks, but I can't recall (or maybe never thought about) rows that grow into being too big for a single block, or grow into additional blocks beyond the original multiblock existence. Hmm. I'll have to test that. I'd bet a donut Steve Adams has the covered somewhere on his site, but it might be easier to test than to search.
The other thing is if you have "out of band" storage of clobs, blobs, what-have-you.
So an analysis of the rowids in an index is not 100% guaranteed unless you're sure you don't have those worries.
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Mark W. Farnham
Sent: Wednesday, September 09, 2009 5:38 PM To: martin.a.berger_at_gmail.com; 'tim'
Cc: 'niall.litchfield'; 'ORACLE-L'
Subject: RE: check for (empty?) blocks which leads to ORA-8103 in a datafile?
If I understand you correctly, you've got a datafile that has some zeros smeared on it.
Now if your indexes happen to be in a different tablespace or by luck in a different datafile in the same tablespace, you *should* be able to get back the colummn values from those indexes by querying the column set from each index. Since the rowid also appears in the index, you should be able to get all the rowids back as well. So if you stuff those rowids into a table and allow the event to ignore table read errors (sorry, not in my head at this time and I'm not looking it up), then after you build the table of row ids you should be able look for them.
Or I suppose if you load up the rowids formatted by file and block (perhaps even using a group by to get it smaller if you're not trying to actually retrieve the data, then it would be pretty routine to examine the list of files and blocks that make up your table, except perhaps for migrated rows.
If I recall correctly the indexes keep the original rowid for migrated rows and the only place the relocated rowid exists is the original block. I could be wrong about this, I know I suggested they let dbserver idle time be used for cleaning indexes as time allowed and eliminating the original rowid and relocation pointer in, er, 1991, but as far as I know that never got to the top of the heap. (Pun intended.)
Okay, I just typed that off the top of my head. In summary, querying any fully surviving index for just its columns and the rowid pieces should work.
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Martin Berger
Sent: Wednesday, September 09, 2009 4:15 AM To: tim
Cc: niall.litchfield; ORACLE-L
Subject: Re: check for (empty?) blocks which leads to ORA-8103 in a datafile?
a really good idea!
On Tue, Sep 8, 2009 at 22:45, Tim Gorman<tim_at_evdbt.com> wrote:
unfortunately, it throwed me
ORA-00600: internal error code, arguments: , , , , , , , 
ORA-06512: at "SYS.DBMS_REPAIR", line 284 ORA-06512: at line 4
this will lead me to another SR ;-)
thanks for the suggestion!
-- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-lReceived on Wed Sep 09 2009 - 16:55:10 CDT