Re: Corrupt System table

On Jul 6, 10:04 pm, Paul <paulwragg2..._at_hotmail.com> wrote:
> Thanks Vladimir,
>
> I did wonder whether the data in this table could be binned, and then
> recreated by recompiling everything but I wasn't 100% certain and I
> didn't want to risk it. I haven't actually got around to trashing the
> instance yet, so I may experiment first to see what happens!
>
> Paul

Well, a corrupt block is still a corrupt block. It may get a little tricky to fix it: if you try to delete from it as utlip.sql does, you will eventually hit the corrupt block and get an ORA-1578 or ORA-600, depending on which sort of corruption it is - soft or hard. Now, Oracle provides DBMS_REPAIR package, which allows you to set "skip corrupt blocks" flag on a segment and then do a full scan of the segment to copy all good data out of it skipping bad blocks; and the package also allows to turn hard corruption into soft for the purpose of skipping corrupt blocks with FIX_CORRUPT_BLOCKS procedure.

So you could try to set the skip flag on IDL_UB2$, CREATE TABLE idl_ub2_copy AS SELECT * FROM idl_ub2$; TRUNCATE TABLE idl_ub2$ DROP STORAGE hoping that the corrupt block is not in the first extent of the segment; and then INSERT INTO idl_ub2$ SELECT * FROM idl_ub2_copy followed by utlirp. If the block is in the first extent of the table, you can try ALTER TABLE IDL_UB2$ MOVE with skip flag on and then rebuild the index on the table, but I am not sure if skip flag is honored by the move operation. You can try moving the table first and see if this works. Remember that the index on the table will get invalidated and will need to be rebuilt if the move indeed works.

Corrections and additions welcome.

Regards,

   Vladimir M. Zakharychev
   N-Networks, makers of Dynamic PSP(tm)    http://www.dynamicpsp.com Received on Sat Jul 07 2007 - 04:02:31 CDT