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,,
Sum(s.falsepos + s.realvio) AS closed_ale,
Sum(f.nbrecords) AS nb_records
FROM stocks s,
WHERE s.sid = f.fid
AND z.zoneid = f.zoneid
AND u.userid = z.userid
AND f.load_date BETWEEN '20081010' AND '20121030'
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;