| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: How to remove corrupted blocks from datafile.
This what I did last time it happened to me. (Bear in mind that this was Oracle 7.3 and so rowid format is different for later releases of Oracle).
In sqlplus select the following from the table with the corruption
SQL> SELECT /*+ ROWID(TABLE_NAME) */ DOC_ID, ROWID
2 FROM TABLE_NAME
3 WHERE ROWID BETWEEN '00000000.0000.0000' AND 'FFFFFFFF.FFFF.FFFF';
This will stop at the corrupted row.
eg. 00007085.4234.0009 - this is a hexidecimal representation
The first 8 digits are the block number (Block no. 28805 in hex is 7085).
The next four are the row and the final four are the file number.
The last record before the corrupted block in this case will be
00007084.FFFF.0009.
The first record of the corrupted block in this case will be
00007085.0000.0009.
The first record after the corrupted block in this case will be
00007086.0000.0009.
To get these unaffected rows back into the table do the following.
create table table_name_tmp
as select * from table_name
where 1 = 2;
This will create a temporary table the same as your original. Next backup
the original table structure so you can recreate it.
exp username/password file=table_name.dmp rows=n tables=TABLE_NAME
then
imp username/passwd file=table_name.dmp indexfile=table_name.sql
Make the necessary amendments to the indexfile (take REM statements and connect statements out)
Next insert the uncorrupted rows into the temporary table.
SQL> INSERT INTO TABLE_NAME_TMP SELECT /*+ ROWID(TABLE_NAME) */ *
2 FROM TABLE_NAME
3 WHERE ROWID BETWEEN '00000000.0000.0000'
4 AND '00007084.FFFF.0009';
Next insert the rows after the corruption
SQL> INSERT INTO TABLE_NAME_TMP SELECT /*+ ROWID(TABLE_NAME) */ *
2 FROM TABLE_NAME
3 WHERE ROWID BETWEEN '00007086.0000.0009'
4 AND 'FFFFFFFF.FFFF.FFFF';
Next drop the original table
drop table table_name;
and then run your sql file (indexfile) to create the table and associated
indexes.
@table_name
After this reapply all grants (run the recreate grant script) and finally insert new rows.
insert into table_name
select * from table_name_tmp;
Lee Robertson
The information contained in this communication is
confidential, is intended only for the use of the recipient
named above, and may be legally privileged. If the reader
of this message is not the intended recipient, you are
hereby notified that any dissemination, distribution or
copying of this communication is strictly prohibited.
If you have received this communication in error, please
re-send this communication to the sender and delete the
original message or any copy of it from your computer
system.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Robertson Lee - lerobe
INET: lerobe_at_acxiom.co.uk
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists --------------------------------------------------------------------To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Wed Jun 06 2001 - 02:25:24 CDT
 |
 |