关于ora-08103错误，其实在熊哥的博客以及dbsnake的博客都曾经写过一篇。我这里写这篇的目的不是为了
模拟ora-8103错误，而是为了测试在该种情况下的expdp和exp。
SQL> create table t1
2 as select *
3 from dba_objects
4 where rownum analyze table t1 compute statistics;
Table analyzed.
SQL> select owner, segment_name, EXTENT_ID, FILE_ID, BLOCK_ID, BLOCKS
2 from dba_extents
3 where segment_name='T1';
OWNER SEGMENT_NAME EXTENT_ID FILE_ID BLOCK_ID BLOCKS
-------------------- --------------- ---------- ---------- ---------- ----------
ROGER T1 1 5 433 8
ROGER T1 3 5 441 8
ROGER T1 0 6 521 8
ROGER T1 2 6 529 8
SQL> select distinct dbms_rowid.rowid_block_number(rowid) blk#,
2 dbms_rowid.rowid_relative_fno(rowid) file#
3 from t1
4 order by 2,1;
BLK# FILE#
---------- ----------
433 5
434 5
435 5
436 5
437 5
438 5
439 5
440 5
441 5
442 5
443 5
444 5
445 5
524 6
525 6
526 6
527 6
528 6
530 6
531 6
532 6
533 6
534 6
535 6
536 6
25 rows selected.
SQL> conn /as sysdba
Connected.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> !
[ora10g@killdb ~]$ dd if=/dev/zero of=/home/ora10g/oradata/roger/roger02.dbf bs=8192 seek=536 count=1 conv=notrunc
1+0 records in
1+0 records out
[ora10g@killdb ~]$ exit
exit
SQL> startup
ORA-00000: normal, successful completion
SQL> conn /as sysdba
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 1272600 bytes
Variable Size 96470248 bytes
Database Buffers 67108864 bytes
Redo Buffers 2920448 bytes
Database mounted.
Database opened.
SQL> select count(*) from roger.t1;
select count(*) from roger.t1
*
ERROR at line 1:
ORA-08103: object no longer exists
[ora10g@killdb ~]$ exp system/oracle file=t1.dmp tables=roger.t1
Export: Release 10.2.0.5.0 - Production on Mon Dec 12 05:41:25 2011
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses ZHS16GBK character set (possible charset conversion)
About to export specified tables via Conventional Path ...
Current user changed to ROGER
. . exporting table T1
EXP-00056: ORACLE error 8103 encountered
ORA-08103: object no longer exists
Export terminated successfully with warnings.
[ora10g@killdb ~]$ expdp roger/roger directory=DATA_PUMP_DIR dumpfile=t1.dmp tables=t1 content=data_only
Export: Release 10.2.0.5.0 - Production on Monday, 12 December, 2011 5:45:13
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "ROGER"."SYS_EXPORT_TABLE_01": roger/******** directory=DATA_PUMP_DIR dumpfile=t1.dmp tables=t1 content=data_only
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 256 KB
ORA-31693: Table data object "ROGER"."T1" failed to load/unload and is being skipped due to error:
ORA-02354: error in exporting/importing data
ORA-08103: object no longer exists
Master table "ROGER"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for ROGER.SYS_EXPORT_TABLE_01 is:
/home/ora10g/product/10.2/rdbms/log/t1.dmp
Job "ROGER"."SYS_EXPORT_TABLE_01" completed with 1 error(s) at 05:45:16
使用bbed 将该block 修改为坏块，然后重启实例。
SQL> shutdown abort;
ORACLE instance shut down.
SQL> startup
ORA-00000: normal, successful completion
SQL> conn /as sysdba
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 1272600 bytes
Variable Size 104858856 bytes
Database Buffers 58720256 bytes
Redo Buffers 2920448 bytes
Database mounted.
Database opened.
SQL> select count(*) from roger.t1;
select count(*) from roger.t1
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 6, block # 536)
ORA-01110: data file 6: '/home/ora10g/oradata/roger/roger02.dbf'
SQL> drop table REPAIR_TABLE;
Table dropped.
SQL> BEGIN
2 DBMS_REPAIR.ADMIN_TABLES (
3 TABLE_NAME => 'REPAIR_TABLE',
4 TABLE_TYPE => DBMS_REPAIR.repair_table,
5 ACTION => DBMS_REPAIR.create_action,
6 TABLESPACE => 'ROGER'
7 );
8 END;
9 /
PL/SQL procedure successfully completed.
SQL> SET serveroutput ON
SQL> DECLARE
2 num_corrupt INT;
3
4 BEGIN
5 num_corrupt := 0;
6 DBMS_REPAIR.CHECK_OBJECT (
7 SCHEMA_NAME => 'ROGER',
8 OBJECT_NAME => 'T1',
9 REPAIR_TABLE_NAME => 'REPAIR_TABLE',
10 corrupt_count => num_corrupt
11 );
12 DBMS_OUTPUT.PUT_LINE('number corrupt: ' || TO_CHAR (num_corrupt));
13 END;
14 /
number corrupt: 1
PL/SQL procedure successfully completed.
SQL> SELECT object_id, RELATIVE_FILE_ID, block_id, CORRUPT_TYPE, object_name
2 from REPAIR_TABLE;
OBJECT_ID RELATIVE_FILE_ID BLOCK_ID CORRUPT_TYPE OBJECT_NAME
---------- ---------------- ---------- ------------ ------------------------------
52470 6 536 6148 T1
SQL> DECLARE
2 fix_count int;
3
4 BEGIN
5 fix_count := 0;
6 DBMS_REPAIR.fix_corrupt_blocks (
7 schema_name => 'ROGER',
8 object_name => 'T1',
9 object_type => DBMS_REPAIR.table_object,
10 repair_table_name => 'REPAIR_TABLE',
11 fix_count => fix_count
12 );
13 DBMS_OUTPUT.put_line('fix count: ' || TO_CHAR(fix_count));
14 END;
15 /
fix count: 0
PL/SQL procedure successfully completed.
SQL> BEGIN
2 DBMS_REPAIR.skip_corrupt_blocks (
3 schema_name => 'ROGER',
4 object_name => 'T1',
5 object_type => DBMS_REPAIR.table_object,
6 flags => DBMS_REPAIR.skip_flag
7 );
8 END;
9 /
PL/SQL procedure successfully completed.
SQL> select count(*) from roger.t1;
COUNT(*)
----------
1917
当然，最后你可以使用cats 或基于rowid方式将数据抽取出来，然后将表rename，我这里模拟的
ora-08103可能跟实际遇到的情况有些差别，群中的网友说使用expdp是可以进行导出的，我这里
模式发现是不行的，有点怪，欢迎大家一起探讨这个问题！
补充：对于未格式化的坏块，oracle是如何判断的，我还不太清楚，正在研究。
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
