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
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?
To answer this question we will need to have a look at the DBA_SEGMENTS view definition.
I have dumped all data file headers to a trace file as follows:
SQL> alter session set tracefile_identifier=file_headers;
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:
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$';
The other way is using DD and OD, if you really want to go deep, BBED, but lets leave that way
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:
As demonstrated by him, this equates to data file 1 block 520
Now we know who tells Oracle where the bootstrap$ object is.