Error as follows.
ORA-01114: IO error writing block to file (block #
)
ORA-01114: IO error writing block to file 201 (block #
2321408)
ORA-27072: File I/O error
Additional information: 4
Additional information: 2321408
Additional information: -1
Invoked oerr to lookup the error message, the file id complained is 201, which is not found in dba_data_files.
dbsvr9:ADMB2:/software/oracle1/admin/scripts/performance> oerr ora 1114
01114, 00000, "IO error writing block to file %s (block # %s)"
// *Cause: The device on which the file resides is probably offline. If the
// file is a temporary file, then it is also possible that the device
// has run out of space. This could happen because disk space of
// temporary files is not necessarily allocated at file creation time.
// *Action: Restore access to the device or remove unnecessary files to free
// up space.
Then focuse on checking temporary files.
However, the tempfile is about 18GB already and almost 100% free as show below.
and, the mount pointing of tempfile is 100% used.
SYS@ADMB2> select * from dba_temp_files;
FILE_NAME
------------------------------------------------------------------------------------------------------------------------------------
FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS RELATIVE_FNO AUT MAXBYTES MAXBLOCKS INCREMENT_BY
---------- ------------------------------ ---------- ---------- ------- ------------ --- ---------- ---------- ------------
USER_BYTES USER_BLOCKS
---------- -----------
/admadm/ADMB2/temp01.dbf
1 TEMP 1.9018E+10 2321536 ONLINE 1 YES 3.4360E+10 4194302 80
1.9017E+10 2321408
SYS@ADMB2> @chk_temp_by_user.sql
check tempoary space used by session
no rows selected
no rows selected
SYS@ADMB2> @chk_temp_usage.sql
percent used
------------
0
TABLESPACE MB_TOTAL MB_USED MB_FREE
-------------------- ---------- ---------- ----------
TEMP 18137 0 18137
Funny.
What does file_id 201 stand for ?
No session is hogging the temp tablespace.
The next intuition is why the tempfile is antoextensive.
Trying to turn id off, and shank it down.
SYS@ADMB2> alter database tempfile '/admadm/ADMB2/temp01.dbf' autoextend off;
Database altered.
SYS@ADMB2> alter database tempfile '/admadm/ADMB2/temp01.dbf' resize 2g;
Database altered.
At last, run the sql again. wow, 15429 rows selected, and right close 7pm, and luckily this is not a production database.
From this case, it seems to even when there is free space in temporary tablespace, Oracle database always acquires space from file system, although there is no more disk space in the file system, and can't look from existing tablespace. Such behaviour is "silly" and dangerous.
