ORA-1114 Error Reported on an Unknown File Id

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号文件查询不存在原因如下：

ORA-1114 Error Reported on an Unknown File Id 

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