ORA-01114: IO error writing block to file

今天同事在qq上问我，怎么查找一个block属于哪个file，感觉很奇怪，就让他把报错信息发出来。

ORA-01114: IO error writing block to file 1001 (block # 1397385)
ORA-27063: number of bytes read/written is incorrect
SVR4 Error: 28: No space left on device
Additional information: -1
Additional information: 253952
ORA-01114: IO error writing block to file 1001 (block # 1397385)
ORA-27063: number of bytes read/written is incorrect
SVR4 Error: 28: No space left on device
Additional information: -1
Additional information: 253952
ORA-01114: IO error writing block to file 1001 (block # 1397385)
ORA-27063: number of bytes read/written is incorrect
SVR4 Error: 28: No space left on device
Additional information: -1
Additional information: 253952
ORA-01114: IO error writing block to file 1001 (block # 1397385)
ORA-27063: number of bytes read/written is incorrect
SVR4 Error: 28: No space left on device
Additional information: -1
Additional information: 253952

他既然问我查找block属于哪个file，肯定是找不到这个file，但是第一感觉是，可能datafile一共有1000个，然后1001可能是tempfile，然后tempfile没有办法扩展。

然后问他有多少个datafile，他说一共323个，感觉很奇怪，搜索metalink，发现如下这篇文章：

Problem Description
-------------------
You have a temporary tablespace of type TEMPORARY.

You are running a SQL statement using temporary storage (for example, a sort
segment to process a GROUP BY or ORDER BY ), and get these errors :

   ORA-01114: IO error writing block to file 5015 (block # 199528)                
   ORA-27063: skgfospo: number of bytes read/written is incorrect                 
   SVR4 Error: 28: No space left on device                                     
   Additional information: -1                                                     
   Additional information: 49152 

Solution Description
--------------------
The files of the temporary tablespace of type TEMPORARY are in a full file
system (no free space available).

Make free space in this file system.

Explanation
-----------
On certain file systems, creation of tempfiles does not guarantee the allocation
of the actual disk space of the file size specified.
The disk space is allocated later when the tempfile blocks are accessed.
This has the advantage of fast tempfile creation (as compared to datafile
creation). However, the disk could run out of space when tempfiles are accessed
later. The same thing happens when tempfiles are resized (to a larger size:
either by ALTER DATABASE ... TEMPFILE ... RESIZE command or by autoextend) i.e.
resize does not guarantee the actual disk space allocation on certain file
systems.

Tempfiles are currently not initialized, and (at least on Unix) this means they
are not pre-allocated. So even if a file system has enough free space at the
time the tempfile is created, later on the user may get write errors due to the
file system being full while writing to the tempfile.

DATAFILE FOR TEMPORARY TABLESPACE IS CREATED AS A SPARSE FILE

问他是不是temp表空间在的文件系统慢了，导致temp无法扩展？一查，果然是；但是不知道为什么tempfile的文件号被写为1001，很奇怪。

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

转载于:http://blog.itpub.net/10805681/viewspace-625810/