一次ORA-8103 :object no longer exists 问题处理

首先，这个问题瞎折腾了一上午，最后直接用expdp导出然后重新导入解决。

问题描述：

一生产库上有张表，做关联查询的时候便报ora-8103的错误，怀疑是有坏块，但是select count(*) 该表，不报错。

但是create table tt as 该表，依然报错ora-8103.

关于这个错误，MOS上面有专门的文章DOC8103.1 来处理该类问题。详情可参阅该文档。

有些同学没有账号，我把该文档贴下来，以供各位同学参考学习：

---------------------------------------------------------------------------------

APPLIES TO:

Oracle Database - Enterprise Edition - Version 8.1.5.0 to 12.2.0.1 [Release 8.1.5 to 12.2]
Information in this document applies to any platform.

PURPOSE

This article provides information about error ORA-8103 and possible actions.

SCOPE

This note is intended for general audience as initial starting point for beginning diagnosis of ORA-8103.

DETAILS

 

Error: ORA 8103 
Text: object no longer exists 
-------------------------------------------------------------------------------
Cause: The object has been deleted by another user since the operation began.
Action: Remove references to the object.

 

Description

ORA-8103 is reporting that a SQL statement found a block that no longer belongs to the object referenced in the statement. 

Cause

ORA-8103 is caused by an invalid block type. The block header has an invalid block type or the block type inside the block is not expected; e.g. a data block (Type = 6) was expected but the actual block information is not a data block (Type != 6).

ORA-8103 is also caused by an unexpected data_object_id where it is concurrently changing for the involved object while the affected SQL statement is executed.

These two causes might be due to an expected behavior or other problems.  Details are:

Expected behavior.

Tables are being dropped/truncated while a SQL statement for those tables is still in execution. In the case of an index, it might be caused by an index rebuild. In other words the object has been deleted by another session since the operation began.

Look if dba_objects.data_object_id is changing for the affected object while queries are being executed.

data_object_id is changed by DDL statements like:

truncate table
alter index .. rebuild
alter table .. move
alter table .. exchange partition
alter table .. split partition
etc.

For a truncate look for column TRUNCATED in DBA_TAB_MODIFICATIONS. Note that it indicates whether the table has been truncated since the last analyze.  See documentation.

In 11g+ parameter enable_ddl_logging can be set to TRUE to print DDL statements in the alert log or in 12c in the log/ddl sub-directory of the ADR home (example <diagostic_dest>/rdbms/<sid>/<dbname>/log/ddl_$ORACLE_SID.log) and identify what DDL's are run that may potentially cause this error.

Block Corruption.

Hardware, IO subsystem or OS problems may cause block corruptions overwriting the Block Type in the block heade