Sunday, August 28, 2011

ORA-01114: IO error writing block to file 202 (block # 4445)

Getting error while creating the and index

ERROR at line 1:
ORA-00603: ORACLE server session terminated by fatal error
ORA-01114: IO error writing block to file 202 (block # 4445)
ORA-27063: number of bytes read/written is incorrect
Additional information: 245760
Additional information: 253952
ORA-01114: IO error writing block to file 202 (block # 4445)
ORA-27063: number of bytes read/written is incorrect
Additional information: 245760
Additional information: 253952
ORA-01114: IO error writing block to file 202 (block # 4445)
ORA-27063: number of bytes read/written is incorrect
Additional information: 245760
Additional information: 253952
ORA-01114: IO error writing block to file 202 (block # 4445)
ORA-27063: number of bytes read/written is incorrect
Additional information: 245760
Additional information: 253952
Process ID: 15076
Session ID: 492 Serial number: 1471
The most probable reason for this error under my circumstances is that, create index command use tamp tablespace and one of the datafile of temp tablespace is not able to extend itself because the disk have no space left. 

In case your TEMP tablespace have two tempfile temp01.dbf and temp02.dbf, first file is on the disk with less space than the maxsize of the file and second file on the disk with more space then the maxsize.

Now you are creating an index and it is using temp01.dbf to as tempspace then once the file have filled the db disk then it will give above error, but it will not start using temp02.dbf.

Incase you query have started using temp02.dbf, because this file can grow and it have enough space on dbdisk and once this file is completely used then it will start using temp01.dbf until it have filled the db disk and hit the error.

No comments: