Storage of null values in Oracle Database

Here we mainly verify the storage of null values of the number and varchar2 data types.

First, we will introduce the structure of row piece.

Reference A structure chart of the official document

Generally, a rowpiece excluding (cluster table and chain row) includes row header and column data.

Other cases will be released in future studies.

BBED> dump/v
File:/u01/apps/Oracle/oradata/david/users01.dbf (4)
Block: 531 Offsets: 8173to 8191 Dba: 0x01000213
-------------------------------------------------------
3c020302 c1020353 59530444 55414c01 l <...... SYS. DUAL.
06fbd4 l. commandid
<16 bytes per line>

For example, the first three bytes of A rowpiece, 3c0203, indicate

1-byte flag
1-byte lb (itl slot)
1-byte columncount

In this example
3c = flags = 00111100 = -- HDFL -- = header + delete + first + last
02 = lb itl slot 0x02
03 = column count


The column value information includes:
Column Length, column value
For example, 02c102
2 bytes
C102 indicates the specific value

Dump information can be translated using UTL_RAW.CAST_TO_xxxx (thanks to itpubIori809)

Verify now

First, the environment is 11.1.0.6 linux x64

1. Dex @ FAKE>DescTs
2.  NameNull? Type
3.  ---------------------------------------------------------------------------------------------------------------------
4. ID NUMBER
5.  NAMEVARCHAR2 (20)
6.  COUNTNUMBER
9. Dex @ FAKE>SelectT .*,
10. 2 dbms_rowid.rowid_relative_fno (t. rowid)As "FNO #",
11. 3 dbms_rowid.rowid_block_number (t. rowid)As "BLK #",
12. 4 dbms_rowid.rowid_row_number (t. rowid)As "ROW #"
13. 5FromDex. ts t
14. 6;
17. IDNAMECOUNTFNO # BLK # ROW #
18. ----------------------------------------------------------------------
19. 1 dd 4 31 0
20. 2 2 4 31 1
21. 2 3 4 31 2
22. 4 4 31 3

Table creation statement: dex @ FAKE> create table ts (id number, name varchar2 (20), count number );
Because the long column type is not used in the table, the column order in the table is the same as that in the column declaration in the table.
BBED> set dba 4, 31
DBA 0x000001f (16777247)
BBED> map
File:/u01/apps/oracle/oradata/fake/users01.dbf (4)
Block: 31 Dba: 0x000001f
------------------------------------------------------------
KTB Data Block (Table/Cluster)


Struct kcbh, 20 bytes @ 0


Struct ktbbh, 72 bytes @ 20


Struct kdbh, 14 bytes @ 100


Struct kdbt [1], 4 bytes @ 114


Sb2 kdbr [3] @ 118


Ub1 freespace [8036] @ 124


Ub1 rowdata [28] @ 8160


Ub4 tailchk @ 8188

1 2 Next Page