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