4

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'
Hawk
  • 5,060
  • 12
  • 49
  • 74
StarJedi
  • 1,410
  • 3
  • 21
  • 34
  • 1
    There's a good chance this is filesystem/disk related (the IO error at least) – Joe Jan 07 '14 at 00:27

2 Answers2

3

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;
Todd
  • 30,472
  • 11
  • 81
  • 89
  • Sadly this didn't work for me. Btw there's a typo that you carried on from the original website, where it says ORA-01444 it should say ORA-01114 – Ignasi Jan 16 '18 at 09:45
1

This command may save you guys.

alter tablespace temp shrink space keep 40m;

Perhaps your system reserve space for temp too much. So we just free this space, we may get back available resources to operate properly.

Kazugi
  • 11
  • 1
  • Good! But for a really full explanation people might want to check this https://stackoverflow.com/a/1878732/3856754 – Ignasi Jan 16 '18 at 09:43