October 27, 2009

ORA-27069 and ORA-01114

When creating a new object on a database (e.g. an index, a table) you get a set of ORA-27069 errors, each of them accompanied by ORA-01114. The description for these errors is:

ORA-27069 skgfdisp: attempt to do I/O beyond the range of the file
Cause: This is an internal error. The range of blocks being read or written is
outside the range of the file, additional information indicates the starting block
number, number of blocks in I/O, and the last valid block in the file.
Action: Check for a trace file and contact Oracle Support Services.

ORA-01114: IO error writing block to file string (block # string)
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.

How to work it out:

This can happen for a multitude or reasons: damaged storage, lack of storage space, etc. On Windows, namely Windows Server 2003 running a 32-bit file system, this can happen when one of the files in your tablespace reaches or crosses 4GB, 8GB, 12GB etc, so this may be the first thing to check.

You can use Oracle Enterprise Manager to check this: http://download.oracle.com/docs/html/B14399_01/rev_precon_db.htm#CACDJBBF

If, in fact, the problem is related to having a file with the characteristics above, you have plenty of options. For example, if you have an 8GB file in the USERS tablespace, you may have trouble adding data to that tablespace (which is the default for user tables, indexes, etc). To solve it, you can:
- set the file's maximum size below the size limit that started causing problems (e.g. 8GB)
- add more files to the tablespace where that file resides, so the tablespace can grow without creating very large files
- move indexes to a specific table space (ALTER INDEX REBUILD TABLESPACE ;)
- furthermore, you can create specific tablespaces for specific objects and move them there, which is way better than having every single object cramping up your default tablespace

All in all, the best way to solve this problem is to correctly manage your storage, tablespaces and files. Oracle ASM can help you with this. If you're running older versions, start here: http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/physical.htm