Temp Tablespace unable to extend
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: Temp Tablespace unable to extend

  1. #1
    Join Date
    Sep 2006
    Posts
    5

    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

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    the datafile in the temp tablespace was corrupted

  3. #3
    Join Date
    Sep 2006
    Posts
    5
    Thanks a lot, Dave .

  4. #4
    Join Date
    May 2005
    Location
    Boracay
    Posts
    681
    oh..thats what i wanted to say too...but its hard to find proof
    Behind The Success And Failure Of A Man Is A Woman

  5. #5
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    eh? couldnt write to the file, recreated the file and it work

    what more proof could you want!!!!!

  6. #6
    Join Date
    Jun 2006
    Posts
    259
    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

  7. #7
    Join Date
    Feb 2006
    Posts
    7
    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

  8. #8
    Join Date
    May 2005
    Location
    Boracay
    Posts
    681
    Thanks boys..for the enlightenment

    I'm just curious...is the above statement rubbish?
    Behind The Success And Failure Of A Man Is A Woman

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width