ORA-00376: file 36 cannot be read at this time

今天检查经营分析数据库alert日志，发现报有如下错误：

Sat Feb 11 06:00:22 2012
GATHER_STATS_JOB encountered errors.  Check the trace file.
Sat Feb 11 06:00:22 2012
Errors in file /home/oracle/admin/tjdb/bdump/tjdb1_j000_23954.trc:
ORA-00376: file 36 cannot be read at this time
ORA-01110: data file 36: '+DATA/tjdb/datafile/sysaux.1339.753914553'

再查看/home/oracle/admin/tjdb/bdump/tjdb1_j000_23954.trc:
Unix process pid: 23954, image: oracle@tjdb01 (J000)

*** 2012-01-16 22:00:46.607
*** ACTION NAME:(GATHER_STATS_JOB) 2012-01-16 22:00:46.606
*** MODULE NAME:(DBMS_SCHEDULER) 2012-01-16 22:00:46.606
*** SERVICE NAME:(SYS$USERS) 2012-01-16 22:00:46.606
*** CLIENT ID:() 2012-01-16 22:00:46.606
*** SESSION ID:(376.6707) 2012-01-16 22:00:46.606
ORA-00376: file 36 cannot be read at this time
ORA-01110: data file 36: '+DATA/tjdb/datafile/sysaux.1339.753914553'
*** 2012-01-16 22:00:46.607
GATHER_STATS_JOB: GATHER_TABLE_STATS('"SYS"','"WRI$_OPTSTAT_HISTHEAD_HISTORY"','""', ...)
ORA-00376: file 36 cannot be read at this time
ORA-01110: data file 36: '+DATA/tjdb/datafile/sysaux.1339.753914553'
*** 2012-01-17 22:01:06.841
*** ACTION NAME:(GATHER_STATS_JOB) 2012-01-17 22:01:06.841
*** MODULE NAME:(DBMS_SCHEDULER) 2012-01-17 22:01:06.841
*** SERVICE NAME:(SYS$USERS) 2012-01-17 22:01:06.841
*** CLIENT ID:() 2012-01-17 22:01:06.841
*** SESSION ID:(376.7745) 2012-01-17 22:01:06.841
ORA-00376: file 36 cannot be read at this time
ORA-01110: data file 36: '+DATA/tjdb/datafile/sysaux.1339.753914553'
*** 2012-01-17 22:01:06.851
GATHER_STATS_JOB: GATHER_TABLE_STATS('"SYS"','"WRI$_OPTSTAT_HISTHEAD_HISTORY"','""', ...)
ORA-00376: file 36 cannot be read at this time
ORA-01110: data file 36: '+DATA/tjdb/datafile/sysaux.1339.753914553'

这是oracle每天晚上自动收集统计信息报出来的，查看数据文件编号36的文件，发现这个文件是去年当初sysaux表空间不够，为其添加的数据文件，文件编号正是36，一段时间后将sysaux表空间下的一些数据清掉后，将这个数据文件offline了，查看：

sql>SELECT t.owner,t.segment_name,t.tablespace_name,t.relative_fno
            FROM dba_segments t
         WHERE t.segment_name='WRI$_OPTSTAT_HISTHEAD_HISTORY';

owner   segment_name                                           tablespace_name     relative_fno

SYS       WRI$_OPTSTAT_HISTHEAD_HISTORY SYSAUX                         3

WRI$_OPTSTAT_HISTHEAD_HISTORY表根本不在第36号文件里面的啊，

sql> select * from WRI$_OPTSTAT_HISTHEAD_HISTORY ;

1 177551 16 04-7月 -11 10.00.55.513857 下午 +08:00 10 0 0 41 21 0.0476190476190476 80 C12A 3 21 11783 2011-7-2 6:00:39      
2 177551 17 04-7月 -11 10.00.55.513857 下午 +08:00 10 0 0 1764 81 0.0123456790123457 80 C21241 3 81 11783 2011-7-2 6:00:39      
3 177551 18 04-7月 -11 10.00.55.513857 下午 +08:00 10 0 0 13 10 0.1 80 C10E 3 10 11783 2011-7-2 6:00:39      

能查出数据来，但是通过select count(*) from WRI$_OPTSTAT_HISTHEAD_HISTORY ;

却也是报ORA-00376: file 36 cannot be read at this time
ORA-01110: data file 36: '+DATA/tjdb/datafile/sysaux.1339.753914553'

说明表WRI$_OPTSTAT_HISTHEAD_HISTORY ;有一部分数据是存储在36号数据文件里面了，而收集统计信息时是对全表做的，因此当读到36号数据文件时，就报这个错误了。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/12129601/viewspace-716156/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/12129601/viewspace-716156/