Re: check for (empty?) blocks which leads to ORA-8103 in a datafile?
- From: Martin Berger <martin.a.berger@xxxxxxxxx>
- To: "Mark W. Farnham" <mwf@xxxxxxxx>, mark.powell@xxxxxxx
- Date: Thu, 10 Sep 2009 08:54:27 +0200
Mark and Mark,
thank you for your comments!
We have an index on this table, so I try to do a
'select index_col, rowid from tab where index_col is not null'.
the execution plan looks good. (chained rows, migrated rows and
multiblock rows should not be an issue here. The single row is short
enough to fit into a block and as the rows where only loaded once, I
do not expect any chained or migrated rows)
let's see hwow many rows are gone.
best regards,
Martin
On Wed, Sep 9, 2009 at 23:37, Mark W. Farnham<mwf@xxxxxxxx> wrote:
> 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
> --
> //www.freelists.org/webpage/oracle-l
>
>
>
>
>
--
Martin Berger martin.a.berger@xxxxxxxxx
Lederergasse 27/2/14 +43 660 660 83306
1080 Wien http://berx.at/
--
//www.freelists.org/webpage/oracle-l
Other related posts:
