Temp TS and datafiles Issue
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Temp TS and datafiles Issue

  1. #1
    Join Date
    Oct 2000
    Posts
    449

    Temp TS and datafiles Issue

    Hello:

    I have a Temp TS and I have 4 datafiles in it and when a user runs a query it is erroring out with ora-01114. Complaining on certain block and file. Now to drill down to the right place, I want to make one datafile unavailable at a time, run the query. Is this a valid option? If so, how do I go about doing it?

    Thanks, ST2000

  2. #2
    Join Date
    May 2002
    Posts
    2,645
    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.

    Drop and recreate the temp tablespace, add another datafile, combination of both.

  3. #3
    Join Date
    Oct 2000
    Posts
    449
    Hello Stecal:

    Thanks for the reply.. By droppping the tablespace and related datafiles and recreating them with same or more space/datafiles, how can i be assured that this problem will not repeat.

    If it's a block corruption error(Incase), then this can hurt me again at a future date right? How to get rid of this problem permanently? Basically I have the temp TS online all the time and all datafiles are permanently in the available mode..

    Thanks, ST2000

  4. #4
    Join Date
    Oct 2000
    Posts
    449
    Yesterday I dropped the Temporary tablespace and its related datafiles and I created exactly the same way and ran my query. I have not added extra datafiles or resized or eliminated..

    Ran for 6 hrs and failed due to a filesystem error.. Looks like this formula worked for ORA-01114. Today I shall run again to make sure.

    Thanks, ST2000

  5. #5
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    Hi,

    You either have reached the limit of the mount point on OS level, i.e., no disk space is available or the file cannot autoextend.
    Oracle Certified Master
    Oracle Certified Professional 6i,8i,9i,10g,11g,12c
    email: ocp_9i@yahoo.com

  6. #6
    Join Date
    Oct 2000
    Posts
    449
    Yeah that's true. I have scripted for the output file to go to a larger filesystem this time.. Basically I wanted to share the info, so if anyone has gotten ora-1114 error, this formula works since the metalink throws various options that makes it more confusing (only for this error).. otherwise metalink is great site..
    Thanks, ST2000
    ~

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