Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: troubleshooting an error with dbms_repair
fraund_at_uiuc.edu wrote:
> 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
The corruption is in your SYSTEM tablespace and I would hope that the only schema owning objects within it are SYS, SYSTEM, and OUTLN.
So why: SCHEMA_NAME => 'OFOR98'
And ... if you only have one corrupt block then you can only have one corrupt table unless that block is part of a cluster so what you are saying, and what you are posting, don't add up. Which indicates to me that you are likely making things worse rather than better.
Open an SR immediately and send your RDA. You do have an RDA don't you. <g>
-- Daniel A. Morgan University of Washington damorgan_at_x.washington.edu (replace x with u to respond) Puget Sound Oracle Users Group www.psoug.orgReceived on Sat Mar 31 2007 - 11:20:58 CDT