How Oracle finds things in a data file - From the simplest of the Queries to the instance Startup

So, how does it do it?

An Oracle server process is nothing more then a large C program. On the other hand, the Oracle RDBMS is a multitude of  processes, memory areas and files working and communicating in conjunction to serve a purpose. 
As we all know, two of the main purposes of a Database System are to store and to retrieve data. When it comes to these two simple things there is a lot more than meets the the eye. If you are like me, you probably want to know the whys and hows of everything, you will most likely find this post of some use. Well, at least it can be used to either refresh your memory on the subject or to amuse someone at the pub. :)
For this article, we will be concentrating on a tiny spectacle of the "retrieving data" function of an Oracle database. We will be exposing the foremost part of the process, which is, how Oracle knows where to look at in a data file when it wants to find data for a particular object.

This may sound very simple as we all know that the DBA_SEGMENTS view exposes which data file a specific segment is and at which block it starts. However, as we dive deeper into this article, you will find answers for the following question and many more:

How did Oracle find the DBA_SEGMENTS in first place?

So, let's start with a very simple example, a query against a table called CUSTOMERS.

SQL> set linesize 200
SQL> col name for a30
SQL> select name
       from customers
      where customer_id=1;

NAME
------------------------------

Daniel Da Meda

Ok, the above query could not be simpler but it will serve as our starting point as we walk backwards to find our how Oracle locates things.

So, let's, put together a sequence of events taking place by the Oracle server process when it wants to locate the CUSTOMERS segment and its relative data.

Let's query the DBA_SEGMENTS view right? 

SQL> select header_file, header_block, blocks, extents from dba_segments where segment_name='CUSTOMERS';

HEADER_FILE HEADER_BLOCK     BLOCKS    EXTENTS

----------- ------------ ---------- ----------

         36         2050          8          1

The above shows that this specific segment has it's segment header stored on block 2050 of datafile 36. The segment header block will always have to be read either from disk or memory if already cached. Ok, there are special cases but let's simplify and assume that it will always have to read the segment header. By the way, is in the segment header that Oracle finds out how many blocks a specific segment has up to the High Water Mark, but this is for another article.

Further to it, we can drill down to the DBA_SEGMENTS view and display the information regarding the only extent that this table has 

SQL> select file_id, block_id, blocks from dba_segments where segment_name='CUSTOMERS';

   FILE_ID   BLOCK_ID     BLOCKS

---------- ---------- ----------

        36       2048          8

From the above output, we can see that this segment's data is stored on data file 36, and it makes use of blocks from 2048 to 2055.

This is easy stuff. Or, is it? How did Oracle find the DBA_SEGMENTS view or its underlying tables? It must be stored somewhere. Where?

To answer this question we will need to have a look at the DBA_SEGMENTS view definition.

Once we look into the DBA_SEGMENTS view text, we realize that it is based on the following  tables:

• sys.user$
• sys.obj$ 
• sys.ts$
• sys.sys_objects
• sys.seg$
• sys.file$

From the above views, there is one in particular which has location-related information - SYS.SEG$ 

The DBA_SEGMENTS gets location-related information from the SYS.SEG$ table which, by the way, is kept in the dictionary cache to avoid unnecessary recursive queries.

The SYS.SEG$ table is the source of location-related information for the oracle server process and will be constantly checked in order to know where to find things in the data file.

While table SYS.SEG$ acts like a GPS for the oracle server process, who tells the Oracle Server process where SYS.SEG$ is? 

As it turns out, SYS.SEG$ is a special type of segment and its location  is defined somewhere else.

We present you with the BOOTSTRAP$ object. 

On my 12c database, there are only a few objects defined in this object:

SQL> select count(*) from sys.bootstrap$;

  COUNT(*)

----------

        60

The bootstrap$ segment contains DDL entries. We can actually see the CREATE TABLE statement that defines the SYS.SEG$ table.

This is how the record for SYS.SEG$ table look like:

 SQL> desc sys.bootstrap$

 Name                                      Null?    Type

 ----------------------------------------- -------- ----------------------------

 LINE#                                     NOT NULL NUMBER

 OBJ#                                      NOT NULL NUMBER

 SQL_TEXT                                  NOT NULL VARCHAR2(4000)

SQL> select sql_text from sys.bootstrap$ where sql_text like '%SEG$%';

SQL_TEXT

--------------------------------------------------------------------------------

CREATE TABLE SEG$("FILE#" NUMBER NOT NULL,"BLOCK#" NUMBER NOT NULL,"TYPE#" NUMBE

R NOT NULL,"TS#" NUMBER NOT NULL,"BLOCKS" NUMBER NOT NULL,"EXTENTS" NUMBER NOT N

ULL,"INIEXTS" NUMBER NOT NULL,"MINEXTS" NUMBER NOT NULL,"MAXEXTS" NUMBER NOT NUL

L,"EXTSIZE" NUMBER NOT NULL,"EXTPCT" NUMBER NOT NULL,"USER#" NUMBER NOT NULL,"LI

STS" NUMBER,"GROUPS" NUMBER,"BITMAPRANGES" NUMBER NOT NULL,"CACHEHINT" NUMBER NO

T NULL,"SCANHINT" NUMBER NOT NULL,"HWMINCR" NUMBER NOT NULL,"SPARE1" NUMBER,"SPA

RE2" NUMBER) STORAGE (  OBJNO 14 TABNO 2) CLUSTER C_FILE#_BLOCK#(TS#,FILE#,BLOCK

#)

From the above output, we can see that its storage attributes are defined as a CLUSTER with OBJNO 14 and TABNO 2 on a cluster named C_FILE#_BLOCK#.

So we now look at this cluster definition. Luckily, cluster C_FILE#_BLOCK# is also defined in the BOOTSTRAP$ object. Let's perform another query this time searching for the cluster:

SQL> select sql_text from sys.bootstrap$ where sql_text like '%C_FILE#_BLOCK#%';

CREATE CLUSTER C_FILE#_BLOCK#("TS#" NUMBER,"SEGFILE#" NUMBER,"SEGBLOCK#" NUMBER)

 PCTFREE 10 PCTUSED 40 INITRANS 2 MAXTRANS 255 STORAGE (  INITIAL 24K NEXT 1024K

 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO 8 EXTENTS (FILE 1 BLOCK

192)) SIZE 225

 Tada! At least some progress. We finally got a data file number and a block number - (FILE 1 BLOCK 192).

So this tells Oracle where to find the SYS.SEG$ segment?

YES!!!

Wait a minute, isn't the BOOTSTRAP$ also a table? Who tells Oracle where to find it then?

The answer is simple. The location of the bootstrap$ object used to be located in the first block of data file number 1. However, you will have to take my word for it as I dumped the block multiple times and I cannot find this information in there anymore...

I have dumped all data file headers to a trace file as follows:

SQL> alter session set tracefile_identifier=file_headers;


Session altered.

SQL> alter session set events 'immediate trace name file_hdrs level 10';

Since I could not find that information in the header of any database file, I tried the controlfile. Also, to no avail:

SQL> alter session set tracefile_identifier=controlfile_header;

Session altered.

SQL> alter session set events 'immediate trace name controlf level 10';

So, I gave up. Perhaps the trace has been changed not to show that information?

UPDATE
=======

Thanks to Rodrigo Righetti from Enkitec, I know now where to find the address for the BOOTSTRAP$ object. Please check out his answer to this:

"The pointer to the bootstrap is represented by the "root dba" entry on the file header trace, which on 11.2 onwards should be:
root dba:0x00400208 

Now the trick part 

Each number in the address is represented with 4bits, in a total of 32 bits, like this:

0000 0000 0100 0000 0000 0010 0000 1000
0 0 4 0 0 2 0 8

Now to get the File ID, we use the first 10 bits: 0000000001 

Which in decimal is: 1

And to get the Block Number we get the remaining 22 bits: 0000000000001000001000

Convert that to decimal and we get: 520 

if you look on dba_segments:

select header_file, header_block from dba_segments where segment_name='BOOTSTRAP$';

1, 520

The other way is using DD and OD, if you really want to go deep, BBED, but lets leave that way 

Regards
Rodrigo"

So, I guess I was looking at the right place for the wrong thing. I will perform another dump of the data file headers and see if I can find the root dba entry:

SQL> alter session set tracefile_identifier=file_headers;

Session altered.

SQL> alter session set events 'immediate trace name file_hdrs level 10';

Found it!

It is located in the trace file. You just have to search for the string root dba.
Since my database is 12.1, I get the same data block address as Rodrigo:

root dba:0x00400208

As demonstrated by him, this equates to data file 1 block 520 

Now we know who tells Oracle where the bootstrap$ object is.

I hope you have enjoyed this small article about How Oracle finds stuff in a data file. Many thanks to Rodrigo Righetti who has come to my rescue helping us to put an end to this small article.

Please leave comments!

Daniel Da Meda