Re: ORA-1578 Corrupt database block in Oracle 7

: In article <3eehqh$lsq_at_news.kth.se> d89-pra_at_dront.nada.kth.se (Per Rasmussen)
: writes:
 

: One block is corrupt and I wish to read the rest of the data to
: save it in an other table.
:
: Oracle Support has suggested me to set certain events in the
: init.ora file to skip corrupt blocks and then do a select (full table scan)
: to retrieve all rows. The event is supposed to skip the corrupt
: block and continue fetching rows through the whole table.

I assume that you are refering to event 10231:' dump corrupted blocks on tablescans'. I believe that I did this on a v7.0.13 database about 9 months ago on an HP 9000.

I didn't actually set the event in the init.ora, however; I included it in a simple SQL*plus session, probably as:

   alter session set events '10231 trace name context forever, level 10';

: On this occasion it does not work. I still get ORA-1578 and the search
: stops. A testcase on oracle7 on unix doesn't work either.

I would be interested to know how you 'created a test case'.

If the event no longer works, you might reduce the effort of copying by working through your table an extent at a time, i.e. look up the extent definitions for the table in user_extents (file id, block_id, and number of blocks) then for each extent copy the rows where the 'file-substr'||'block-substring' is between the starting and ending 'file'||'block' for that extent.

When you hit the extent with the corrupt block you could do the same sort of thing with a 'binary chop' through the extent.

BTW: if the event does work, then

        create table as select from table;
is much more efficient on redo log than

	create table;
	insert into table;

-- 
Jonathan Lewis