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';

BYTES     BLOCKS    EXTENTS
---------- ---------- ----------
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
fdh@GUNNAR>
fdh@GUNNAR> BEGIN
2
3      open :x for select veld1 from test;
4
5  END;
6  /

PL/SQL procedure successfully completed.

fdh@GUNNAR>
fdh@GUNNAR> DECLARE
2
3      l_veld1   char(2000);
4
5  BEGIN
6
7      fetch :x into l_veld1;
8      dbms_output.put_line('veld1: ' || trim(l_veld1));
9
10  END;
11  /
veld1: x

PL/SQL procedure successfully completed.

-- AT THIS MOMENT ANOTHER SESSION TRUNCATES THE TABLE

fdh@GUNNAR>
fdh@GUNNAR> DECLARE
2
3      l_veld1   char(2000);
4      l_cnt     number(3,0)   := 0;
5
6  BEGIN
7
8      BEGIN
9
10          loop
11
12              fetch :x into l_veld1;
13              exit when :x%NOTFOUND;
14              l_cnt := l_cnt + 1;
15
16          end loop;
17
18      EXCEPTION
19          when others then
20              dbms_output.put_line(SQLERRM);
21
22      END;
23
24      dbms_output.put_line('fetched rows: ' || l_cnt);
25
26  END;
27  /
ORA-08103: object no longer exists
fetched rows: 75

PL/SQL procedure successfully completed.

fdh@GUNNAR>

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?

Advertisements

3 Comments »

  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


RSS feed for comments on this post. TrackBack URI

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: