ORA-1114 Error Reported on an Unknown File Id

小灰灰 2022-08-10 14:40 16阅读 0赞

impdp导入报错

ORA-27072: File I/O error  
Additional information: 4  
Additional information: 209606  
Additional information: 167936  
ORA-01114: IO error writing block to file 201 (block \# 2  
ORA-39083: Object type INDEX failed to create with error:  
ORA-01114: IO error writing block to file 201 (block \# 209757)

\----201号文件查询不存在原因如下：

In this Document  
Symptoms  
Cause  
Solution  
References  
  
  
APPLIES TO:  
  
  
Oracle Database - Enterprise Edition - Version 8.1.6.0 to 11.2.0.3 \[Release 8.1.6 to 11.2\]  
Information in this document applies to any platform.  
SYMPTOMS  
  
  
ORA-1114 error reported on an unknown file id:  
  
ORA-01114: IO error writing block to file 1519 (block \# 389478)  
ORA-27063: skgfospo: number of bytes read/written is incorrect  
SVR4 Error: 28: No space left on device  
   
However, there is no datafile which has file id equal to 1519:  
  
SQL> select file\_id, RELATIVE\_FNO, file\_name from dba\_data\_files  
where file\_id='1519';  
  
no rows selected  
SQL> select file\_id, RELATIVE\_FNO, file\_name from dba\_data\_files  
where RELATIVE\_FNO=1519;  
no rows selected    
   
How can we determine which file is affected by the error?  
  
CAUSE  
  
 This error is being reported on a temp file.  For TEMPFILES, the file numbering starts after the value of db\_files. In this case, the value of the parameter db\_files is 1500, so file 1519 is temp file number 19.  
  
SOLUTION  
  
 There are 2 ways to locate the temp file the error is being reported against:  
  
1.  Run the following query to get the file name.  
  
      select file\#, name from v$tempfile where file\#=<file id reported in ora-1114 error >-<value of db\_files>;  
  
     In this case, the sql statement should be  
  
      select file\#, name from v$tempfile where file\#= (1519 - 1500);  
  
2.  Check the controlfile dump to find the temp file.  
  
  a) Dump the control file.  
      e.g: SQL>alter session set events 'immediate trace name controlf level 10';  
  
  b) Open the dump file and go to the following section:  
      \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
      TEMP FILE RECORDS  
      \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
  c) Find the temp file by the file id in ora-1114 error.  
  
      In this case, the below information was found:  
     TEMP FILE \#19: External File \#1519  <==== here is the file number reported in the ora-1114 error, it points to a temp file.  
      (name \#1344) /paic/cx/epcis2/data/oradata/epcis/temp18.dbf  
      creation size=3670016 block size=8192 status=0xe head=1344 tail=1344 dup=1  
      tablespace 204, index=202 krfil=17 prev\_file=18  
      unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00