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?
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.
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..
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.
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
~
Reply With Quote
Bookmarks