oracle壞塊問題的解決

1.在alter.log 中找到錯誤block
grep blocknum alter**.log |sort |uniq
2.使用SELECT SEGMENT_TYPE,OWNER||'.'||SEGMENT_NAME FROM DBA_EXTENTS WHERE file = FILE_ID AND block BETWEEN BLOCK_ID AND BLOCK_ID+BLOCKS -1
查出出錯的表
3.使用db_repair修復,雖然沒有成功.db_repair沒有發現錯誤.但
select count(*) 就報錯.
connect sys/chang_on_install
a. create_admin.sql
declare
begin
dbms_repair.admin_tables(
        table_name =>'REPAIR_TABLE',
        table_type=>dbms_repair.repair_table,
        action=> dbms_repair.create_action,
        tablespace => 'system');
end;
/
b. orphan_create

declare
begin
dbms_repair.admin_tables (
table_type=> dbms_repair.orphan_table,
action => dbms_repair.create_action,
tablespace =>'SYSTEM');
end;
/


c. check_object

declare
rpr_count int;
begin
rpr_count :=0;
  dbms_repair.check_object(
        schema_name=>'OBS56',
        object_name=> 'ABS_PROD_BILL_20020521',
        repair_table_name=> 'REPAIR_TABLE',
        corrupt_count => rpr_count);
        dbms_output.put_line('repair count:' || to_char(rpr_count));
end;
/

d.  fix_object

declare
fix_count int;
begin
fix_count :=0;
  dbms_repair.fix_corrupt_blocks(
        schema_name=>'OBS56',
        object_name=> 'ABS_PROD_BILL_20020521',
        object_type=>dbms_repair.table_object,
        repair_table_name=> 'REPAIR_TABLE',
        fix_count => fix_count);
        dbms_output.put_line('repair count:' || to_char(fix_count));
end;
/

最後使用.
1. 停應用
2.rename  A  to  B;
ALTER SESSION SET EVENTS '10231 TRACE NAME CONTEXT FOREVER, LEVEL 10';
3.create table  A as select * from B;
4.刪除A上的index.
退出
重進入sqlplus
重新建立A上的index.