Re: troubleshooting an error with dbms

From: <fraund_at_uiuc.edu>
Date: 30 Mar 2007 21:17:22 -0700
Message-ID: <1175314642.416497.228580@e65g2000hsc.googlegroups.com>

Continuing this post, I've discovered the error that was hanging me up. I was using the tablespace name instead of the schema name, but now i'm running into another problem. The block error that I am encountering is preventing the dbms_repair utility from working. There are at least 5 tables affected by this block error and for most operations (for instance drop table) on them I get the ORA-00604, 01578, and 01111 sequence of errors. Here is an example of dbms_repair not working:

SQL> SET SERVEROUTPUT ON
SQL> DECLARE num_corrupt INT;
  2 BEGIN
  3 num_corrupt := 0;
  4 DBMS_REPAIR.CHECK_OBJECT (

  5     SCHEMA_NAME => 'OFOR98',
  6     OBJECT_NAME => 'UIUC_SLO_PARAMETERS',
  7     REPAIR_TABLE_NAME => 'REPAIR_TEST',
  8     CORRUPT_COUNT =>  num_corrupt);

9 DBMS_OUTPUT.PUT_LINE('number corrupt: ' || TO_CHAR (num_corrupt));
10 END;
11 /
DECLARE num_corrupt INT;
*
ERROR at line 1:

ORA-00604: error occurred at recursive SQL level 1
ORA-01578: ORACLE data block corrupted (file # 1, block # 50619)
ORA-01110: data file 1: '/home/oracle/databases/WG73/system.dbf'
ORA-06512: at "SYS.DBMS_REPAIR", line 293
ORA-06512: at line 4

According to the example, I should be receiving a 'number corrupt: 1' message.

Is there any way to find out if any tables other than the 5 I've discovered are corrupted by this block? Has anyone seen dbms_repair not work in this fashion and is there a workaround?

Philipp Received on Fri Mar 30 2007 - 23:17:22 CDT

Re: troubleshooting an error with dbms_repair