CloudJedi CloudJedi - 1 month ago 10
SQL Question

ORA 01114 - IO Error writing block to file

I am working on some compelex sql queries in Oracle 11g, which have aggregation functions like SUM and joins multiple tables and views. I am getting the IO Error and Tablespace insufficient space error when I try to query large span of data.

Error no.1 is
ORA-01114: IO Error writing block to file(block #)
ORA-01114: IO Error writing block to file 201(block #1343798)
ORA-27063: number of bytes read/written is incorrect

Error no.2 Sometimes
Database Running out of Temporary Space when loaddate > 12 months

Is this an Oracle specific error that my DBA has to solve or something is wrong with my queries? How would I fine tune the performance of the queries to avoid insufficient tablespace prompt? I am writing a dummy sample of what my queries look like

SELECT Sum(s.stock + s.accept + s.new) AS result,
Floor(( s.sales / s.stock ) * 100) AS sales_per,,
f.load_date,,
u.user_id,
Sum(s.falsepos + s.realvio) AS closed_ale,
Sum(f.nbrecords) AS nb_records
FROM stocks s,
facts f,
zones z,
users u
WHERE s.sid = f.fid
AND z.zoneid = f.zoneid
AND u.userid = z.userid
AND f.load_date BETWEEN '20081010' AND '20121030'

Answer

Yes, you'll probably have to get your DBA to fix something.

According to this page,

ORA-01114 occurs when you attempt to write to a file and the device with the file is either:

1) offline OR

2) Has run out of space, possibly because it is a temporary file which was not allocated at creation time.

To resolve ORA-01114, you should either:

1) restore access to the device OR

2) take out files which are not needed in order to gain more space

And offers this debugging advice (I've inserted your block numbers):

You can pinpoint the tablespace and segment for an ORA-01444 error by plugging-in the file_id and block_id into this query on dba_extents:

select 
   owner,
   tablespace_name, 
   segment_type, 
   segment_name 
from 
   dba_extents
where 
   file_id = 201
and 
   block_id = 1343798;
Comments