Re: Extract data from index

Peter Mubaiter wrote:
> Suppose the following situation:
>
> You have got an 8.0.5 database with an index which has a corrupted
> block. The table on which the index was created does not exist
anymore,
> due to a storage hardware failure. Only the index tablespace is left.
> What you want to do now, is
>
> select /*+ index (c INDEX_NAME)*/ indexed_column
> from affected_table where indexed_column is not null;
>
> to get all the data from the index (to save as much data as
possible).
> Unfortunately there is a corrupted block in this index. The question
is
> now: Can you navigate around the corrupted block? Possibly with dirty

> tricks like changes in the data dictionary?
>
> Thanks for any ideas,
>
> Peter

If I understand this correctly, you are stuck with an index tablespace and minus the data tablespace due to a hardware failure. Rebuilding the control file to eliminate the missing data file and then starting the instance may get you up and running, but creating this tablesapce anew and then creating the missing table sounds like a risky way to recover any data from the associated 'disassociated' index. It appears, however, from your post this is exactly what you've done, possibly from a recent export of the schema. If, in fact, you've succeeded in 'reclaiming' this tahle and can now use, albeit in a limited fashion, the index left behind due to a corrupted block I really see no way to go 'around' this boulder in the road, at least not with 8.0.5. With 8.1.6 and later you have the DBMS_REPAIR package, which may or may not have done you any service; if the corruption is 'soft' (the contents are unusable but the underlying disk block is fine) the DBMS_REPAIR package would have either repaired the corruption or marked the block so it would be skipped with the FIX_CORRUPT_BLOCKS procedure. Then the DBMS_REPAIR package would be called with the SKIP_CORRUPT_BLOCKS procdure to set a flag to skip unusable (corrupt) blocks marked by the FIX_CORRUPT_BLOCKS procedure. No such option exists in 8.0.5. And the question now before me is: if you DID rebuild this table from an export why did you not rebuild the index associated with it? And, if you didn't use an export, and tried to rebuild this table from 'scratch' I don't understand how you can access the index as you'd have no data in the table. A third situation arises: you've rebuilt the table AND the index from an export, and have run across a bad disk block in the index tablespace. This you can do nothing about, really, except replace the disk and re-run the import to restore the structures. And, if you have an export why are you worried about a corrupt block in an index?

All in all this situation sounds too ludicrous to be true. So I would, if I were you, reveal the true nature and goal of this exercise. Otherwise stop trying to drop 'bombs' in the newsgroup to see how much who knows. Such acts are rude and serve no purpose.

David Fitzjarrell Received on Thu Jan 13 2005 - 22:03:45 CST