RE: check for (empty?) blocks which leads to ORA-8103 in a datafile?

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.

-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Mark W. Farnham
Sent: Wednesday, September 09, 2009 5:38 PM
To: martin.a.berger@xxxxxxxxx; '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.

mwf

-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
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?

Tim,

a really good idea!

On Tue, Sep 8, 2009 at 22:45, Tim Gorman<tim@xxxxxxxxx> wrote:
> DBMS_REPAIR.CHECK_OBJECT?

unfortunately, it throwed me
ORA-00600: internal error code, arguments: [4555], [0], [], [], [], [], [],
[]
ORA-06512: at "SYS.DBMS_REPAIR", line 284
ORA-06512: at line 4

this will lead me to another SR ;-)

thanks for the suggestion!
 Martin
--
http://www.freelists.org/webpage/oracle-l




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




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

Other related posts: