Irrelevant thoughts of an oracle DBA

21 September 2008

ORA-08103 revisisted

Filed under: Uncategorized — Freek D'Hooge @ 0:39

In an earlier post I had already written about sessions receiving an ORA-08103 error after selecting from a table that had been truncated. At that time, I wrote the following conclusion:

“At this point I have no real explanation as for why the error occurs at this number of records fetched.
I suspect oracle of storing the locations of the blocks it has to scan during a full table in a kind of array with a limited length. After this array has been processed, Oracle would use the dataobj# value (and other keys) to get the next batch of block locations. At that moment the “object no longer exists” error would be thrown as the referenced dataobj# value no longer exists (it has been increased by 1).
But at this moment this is just pure speculation.”

Some weeks ago, I read an answer on the Oracle-L list from Tanel Poder to an ORA-08103 question, in which he stated that oracle would read 10 pointers to extents from the segment header at a time.

A quick test does seem to confirm this:

The TEST tablespace is a LMT with an uniform extent size of 16K

drop table test;

Table dropped.

fdh@GUNNAR> create table test (veld1 char(2000)) tablespace test pctfree 0;

Table created.

fdh@GUNNAR> insert into test select 'x' from all_objects where rownum <= 200;

200 rows created.

fdh@GUNNAR> commit;

Commit complete.

fdh@GUNNAR> select bytes, blocks, extents from user_segments where segment_name = 'TEST';

---------- ---------- ----------
425984         52         26

fdh@GUNNAR> set serveroutput on
fdh@GUNNAR> -- set the arraysize to 1 to prevent oracle from "prefetching" records
fdh@GUNNAR> set arraysize 1
fdh@GUNNAR> var x refcursor
3      open :x for select veld1 from test;
5  END;
6  /

PL/SQL procedure successfully completed.

3      l_veld1   char(2000);
7      fetch :x into l_veld1;
8      dbms_output.put_line('veld1: ' || trim(l_veld1));
10  END;
11  /
veld1: x

PL/SQL procedure successfully completed.


3      l_veld1   char(2000);
4      l_cnt     number(3,0)   := 0;
8      BEGIN
10          loop
12              fetch :x into l_veld1;
13              exit when :x%NOTFOUND;
14              l_cnt := l_cnt + 1;
16          end loop;
19          when others then
20              dbms_output.put_line(SQLERRM);
22      END;
24      dbms_output.put_line('fetched rows: ' || l_cnt);
26  END;
27  /
ORA-08103: object no longer exists
fetched rows: 75

PL/SQL procedure successfully completed.


My block size is 8K, so I could store 4 records per db block. Before the truncate I read 1 record and after the truncate I could read 75 records before getting the error. 76 records / 4 records per block / 2 blocks per extent = 9.5 extents. Remember that the first block is used for the segment header, so pointer 1 would only point to an extent with 1 data block ( meaning that my 10 pointers point in total to 19 blocks * 4 = 76 records).

This answers the question on when you would receive the ORA-08103 error after a truncate, but also means that your dbfmrc is limited by your extent size.
In my example the 10 pointers would reference 20 blocks (not taking in account the segment header), so a dbfmrc of more then 20 would not be possible for this table.

New question: Why is a multiblock read not crossing extent bounderies?


  1. how to disable pop up blocker in android

    ORA-08103 revisisted | Irrelevant thoughts of an oracle DBA

    Trackback by wake up sid 720p download — 14 October 2017 @ 14:23 | Reply

  2. lost girl season 5 episode 1 full online

    ORA-08103 revisisted | Irrelevant thoughts of an oracle DBA

    Trackback by brick games free download — 14 October 2017 @ 14:35 | Reply

  3. down on me hd video download

    ORA-08103 revisisted | Irrelevant thoughts of an oracle DBA

    Trackback by ssc je syllabus 2017 pdf download — 19 October 2017 @ 0:33 | Reply

  4. Хэллоу!
    Куплю инвестиционный проект элитного отеля из бревен от 30 номеров для курорта . Приглашают на конференцию для индустрии гостепреимства International Hospitality Conference, их сайт говорят будут докладчики из Европы, Украины, которые расскажут про успешный опыт реализации таких проектов в Европе и Украине.
    Посоветуйте стоит ли идти, цена за вход 2200 грн (75 долларов) в стоимость входит обед, кофи брейки, ужин, синхронный перевод? Мероприятие состоится 17 февраля 2018 г. в Киеве в шикарном InterСontinental Hotel.

    Comment by bukinKr — 5 February 2018 @ 7:55 | Reply

  5. My up to date suss out d evolve:

    Comment by corahz18 — 21 April 2018 @ 10:36 | Reply

  6. n the summer and is keen on Premier Leaguebunde-ling bayernBayern Munich 1 Schalke 1: Robert Lewandowski's early opener quickly cancelled out in Bavarialahm the manPhilipp Lahm makes 500th Bayern appearance 鈥?German is one best full-backs of his generationDele counterBayern Munich interested in Dele Alli – but German champs will have to pay more than 锟?5m for ace Getty Images3Tillman has been tipped to reach the very top at Bayern MunichAgent Christian Rossner said: There was a tangible offer from Barcelona.

    Comment by ct14.aspx — 6 June 2020 @ 13:05 | Reply

  7. PA:Press Association3Sergio Aguero showed his worth in the 5-1 win over Huddersfield“The club can say what it wants but in June they will have to meet with me.

    Comment by mail28.aspx — 6 June 2020 @ 13:05 | Reply

  8. славный ресурс

    Comment by Joshuaasype — 29 August 2020 @ 1:06 | Reply

  9. доброкачественный ресурс

    Comment by JosephGig — 1 September 2020 @ 3:13 | Reply

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Blog at

%d bloggers like this: