ORA-01114: IO error writing block to file name block %s

Problem Description: got this error when trying to read from a table/view: ORA-01114: IO error writing block to file name block %s

******From metalink: 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. *****


In my case, this has happened b'cos we have created a temporary datafile in a mountpoint and did not extend it completely and now when it is trying to extend it, it can't ... ...this might be the cause of the error. As you know, when you create a tempfile, it doesn't immediately occupy the OS space unless, otherwise all the segments are extended. It just says database altered even though there is no space in that mountpoint.

We can see the the usage in enterprise manager. I've seen that and i've observed that i've allocated about 1GB to that tempfile..but there was only 0.9GB left....and it was trying to extend beyond that size. so, it was giving that error.

SOlUTION:

drop and recreate your temp tablespace.

procedure: create a new temp tablespace, make this as the default temp tablespace and drop the old one (in 9i , drop tablespace temp including contents and datafiles).

Any comments ??

Tablespaces

Here are some scripts, tips regarding the tablespaces. This script basically gives you all the tablespace info in a database.

****************** BEGIN -- Tablespace info script ******************

set linesize 150;

select ddf.TABLESPACE_NAME Tablespace,
ddf.BYTES/1024/1024 MBytes_allocated,
(ddf.BYTES-DFS.BYTES)/1024/1024 MBytes_used,
round(((ddf.BYTES-dfs.BYTES)/ddf.BYTES)*100,2) Percent_used,
dfs.BYTES/1024/1024 MBytes_free,
round((1-((ddf.BYTES-dfs.BYTES)/ddf.BYTES))*100,2) Percent_free
from (select TABLESPACE_NAME,
sum(BYTES) bytes
from dba_data_files
group by TABLESPACE_NAME) ddf,
(select TABLESPACE_NAME,
sum(BYTES) bytes
from dba_free_space
group by TABLESPACE_NAME) dfs
where ddf.TABLESPACE_NAME=dfs.TABLESPACE_NAME
order by ((ddf.BYTES-dfs.BYTES)/ddf.BYTES) desc;

****************** END-- Tablespace info script *****************

Oracle posts

This blog is to demonstrate my experiences with oracle. planning to post the new things i learn.