Temp Tablespace unable to extend

Printable View

10-03-2006, 03:18 AM
AdityaPB

Temp Tablespace unable to extend

Hi,

we have problem with temp tablespace,we are getting the following error

ORA-01114: IO error writing block to file 1025 (block # 1292425)
ORA-27063: skgfospo: number of bytes read/written is incorrect

temp tablespace size 10 gb.

we have verified the v$sort_usage to find the any other parallel transaction is running,but we don't find it.we have bounced the database, but again we face the simlar issue.

It is simple join opertaion query.

Now records in the table

Table1 = 100000
table2 = 200000

After consulted my DBA, he recreated the temp tablespace of same size once again. Same query executed sucessfully after recreating the temp tablespace

What could be problem on this ?

Regards,
Adhavan
10-03-2006, 04:09 AM
davey23uk

the datafile in the temp tablespace was corrupted
10-04-2006, 01:35 AM
AdityaPB

Thanks a lot, Dave .
10-04-2006, 01:58 AM
yxez

oh..thats what i wanted to say too...but its hard to find proof :rolleyes:
10-04-2006, 04:47 AM
davey23uk

eh? couldnt write to the file, recreated the file and it work

what more proof could you want!!!!!
10-04-2006, 01:16 PM
ixion

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

Davey...
Seems to me that you can't say 100% that it was a datafile corruption. Just that some form of I/O error happened. There probably was no need to recreate the temp tablespace.

Just my .02
10-05-2006, 12:59 AM
oradba88

Its my observation that the TEMP space is not allocated on the disk at the time of tablespace creation. The space is used only when actually required.
So if you created a 5GB tempfile, you have to make sure that amount of free space is always available on the disk & not used up by other files.
The only solution is to recreate the TEMP tablespace
10-05-2006, 01:19 AM
yxez

Thanks boys..for the enlightenment :D

I'm just curious...is the above statement rubbish? :confused: