-
Title
ORA-600 detected in : ORA--00603 followed by ORA-01114 and ORA-27063 -
Description
reorg fails with ora-600 followed by errors in the trace file indicating corrupt disc or writing error.
On an Solaris platform you may see an error message like:
ORA-01114: IO error writing block to file and specific file number from the system will show as in the brackets<<202 (block # 458633)ORA-27063>>: skgfospo: number of bytes read/written is incorrectSVR4 Error: 28: No space left on deviceAdditional information -
Cause
ORA-01114 and ORA-27063
Specifically, the high numbering of the file (in blue), refers to a temporary file.
-1Additional information: 122880
it means that a datafile cannot extend anymore because a disk (or better said, some kind of storage device) is full.
If the number is in the range between 1 - DB_FILES, where DB_FILES is an initialization parameter whose value can be retrieved either with:
select * from v$parameterwhere name = 'db_files';
or in SQL*Plus with show parameter db_files
it means that we are talking of an ordinary datafile.
In this case DB_FILES was set to 200, which means that 202 refers to a temporary file and tempfiles are numbered starting from DB_FILES + 1.
If the temporary file is set to AUTOEXTEND ON, then the root cause of the problem can be a huge query on a view requiring a big sort operation or the insertion of lots of rows into a temporary table that made the temporary tablespace grow larger than usual until all the space on the device was used up. -
Resolution
This triggered the customer that it had to be temp tablespace - PSAPTEMP (db_files = 1600 and the file number was 1614).
Since AUTOEXTEND was set to ON when it first happened it tried to overfill a filesystem.
So this issue was resolved by setting the AUTOEXTEND back to off, cleaning the filesystem and adding some extra datafiles to the temp tablespace PSAPTEMP.
If you fine out from v$parameter that the issue is with db_files such as datafile cannot extend anymore because a disk is full, then add more datafiles or increase the number of datafiles in the parameter file if you can bounce the database.