Paweł Wiejkut SAP, ABAP, BW & Database Systems :)
All of you probably at least once in your life try to insert some records to a database table, but is anyone of you ever try to understand how Oracle actually saves the entries in the database? Last time I was making a small experiment and I want to share my results with you.
Step one, let’s build a small table and insert a few records:
|
1 |
CREATE TABLE tab1 (c1 VARCHAR(3), c2 number(3)) |
Now, let’s insert some data to table:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
INSERT INTO tab1 VALUES('AAA',111);
INSERT INTO tab1 VALUES('AAA',222);
INSERT INTO tab1 VALUES('AAA',333);
INSERT INTO tab1 VALUES('AAA',444);
INSERT INTO tab1 VALUES('BBB',111);
INSERT INTO tab1 VALUES('BBB',222);
INSERT INTO tab1 VALUES('BBB',333);
INSERT INTO tab1 VALUES('BBB',444);
INSERT INTO tab1 VALUES('CCC',111);
INSERT INTO tab1 VALUES('CCC',222);
INSERT INTO tab1 VALUES('CCC',333);
INSERT INTO tab1 VALUES('CCC',444);
|
After that my table content looks like below:
Let’s start some fun and check how my dump from Oracle DB looks like. First of all, I need to find the block where the table is stored:
|
1 |
SELECT segment_name,header_file, header_block+1 FROM dba_segments WHERE segment_name = 'TAB1'; |
Create a dump from the selected block:
|
1 |
alter system dump datafile 1 block 102161;
|
Now I have to run the console, ssh into the remote system and check last files in Oracle directory:
|
1
2
3
4
5
|
cd /u01/app/oracle/diag/rdbms/orcl12c/orcl12c/trace
find $1 -type f -exec stat --format '%Y :%y %n' "{}" \; | sort -nr | cut -d: -f2- | head
# cat orcl12c_ora_10640.trc
|
Section of my dump looks like below:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
|
block_row_dump:
tab 0, row 0, @0x1f95
tl: 11 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 3] 41 41 41
col 1: [ 3] c2 02 0c
tab 0, row 1, @0x1f8a
tl: 11 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 3] 41 41 41
col 1: [ 3] c2 03 17
tab 0, row 2, @0x1f7f
tl: 11 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 3] 41 41 41
col 1: [ 3] c2 04 22
tab 0, row 3, @0x1f74
tl: 11 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 3] 41 41 41
col 1: [ 3] c2 05 2d
tab 0, row 4, @0x1f69
tl: 11 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 3] 42 42 42
col 1: [ 3] c2 02 0c
tab 0, row 5, @0x1f5e
tl: 11 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 3] 42 42 42
col 1: [ 3] c2 03 17
tab 0, row 6, @0x1f53
tl: 11 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 3] 42 42 42
col 1: [ 3] c2 04 22
tab 0, row 7, @0x1f48
tl: 11 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 3] 42 42 42
col 1: [ 3] c2 05 2d
tab 0, row 8, @0x1f3d
tl: 11 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 3] 43 43 43
col 1: [ 3] c2 02 0c
tab 0, row 9, @0x1f32
tl: 11 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 3] 43 43 43
col 1: [ 3] c2 03 17
tab 0, row 10, @0x1f27
tl: 11 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 3] 43 43 43
col 1: [ 3] c2 04 22
tab 0, row 11, @0x1f1c
tl: 11 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 3] 43 43 43
col 1: [ 3] c2 05 2d
end_of_block_dump
|
If we look closer at block_row_dump, you will see some repeatability. Let’s check what is the meaning of lines with c2 at the start.
To achieve this, we can just execute SQL statement below:
|
1
2
3
|
var n number;
exec dbms_stats.convert_raw_value('c2020c',:n);
print
|
and a result of this statement is:
|
1 |
111
|
Value of raw ‚c2 03 17’ is : 222. You can check all these values and everything will look like our inserts on the beginning. To be more precise we are actually on „row” level of database storage:
That’s all for today, this is a basic knowledge related to database data save in tables. This knowledge is a key to understand how to works indexing or compression on the database.
