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.
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.
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/25618347/viewspace-713687/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/25618347/viewspace-713687/