Table creation statement
Declare
V_ SQL varchar2 (32767 );
Begin
V_ SQL: = 'create table test (';
For I in 1 .. 500 loop
V_ SQL: = v_ SQL | 'name' | I | 'varchar2 (2000 ),';
End loop;
V_ SQL: = substr (v_ SQL, 1, length (v_ SQL)-1 );
V_ SQL: = v_ SQL | ');';
Dbms_output.put_line (v_ SQL );
End;
Copy, paste, and create a table
_ Dex @ DAVID> desc test
Name Null? Type
-------------------------------------------------------------------------------------------------
NAME1 VARCHAR2 (2000)
NAME2 VARCHAR2 (2000)
NAME3 VARCHAR2 (2000)
.....
NAME500 VARCHAR2 (2000)
_ Dex @ DAVID> insert into test (name500) values (lpad (1,300, 'D '));
1 row created.
_ Dex @ DAVID> select
2 length (t. name500 ),
3 dbms_rowid.rowid_relative_fno (t. rowid) as "FNO #",
4 dbms_rowid.rowid_block_number (t. rowid) as "BLK #",
5 dbms_rowid.rowid_row_number (t. rowid) as "ROW #"
6 from dex. test t
7/
LENGTH (T. NAME500) FNO # BLK # ROW #
-----------------------------------------------
300 4 925 1
1 4 925 3
300 4 925 5
View with bbed
First, we can see that row # is 1 and the length is 300.
BBED> set DBAs 4,925
DBA 0x0100039d (16778141 4,925)
BBED> p kdbr
Sb2 kdbr [0] @ 142 7504
Sb2 kdbr [1] @ 144 7250
Sb2 kdbr [2] @ 146 6991
Sb2 kdbr [3] @ 148 6737
Sb2 kdbr [4] @ 150 6177
Sb2 kdbr [5] @ 152 5923
BBED> p * kdbr [1]
Rowdata [1327]
-------------
Ub1 rowdata [1327] @ 7374 0x28
BBED> dump/v offset 7374 count 128
File:/u01/apps/Oracle/oradata/david/users01.dbf (4)
Block: 925 Offsets: 7374 to 7501 Dba: 0x0100039d
-------------------------------------------------------
2801f501 00039d00 ffffff l (. Large .............
Ffffffff ffffffffffffff ffffffffff l ................
Ffffffff ffffffffffffff ffffffffff l ................
Ffffffff ffffffffffffff ffffffffff l ................
Ffffffff ffffffffffffff ffffffffff l ................
Ffffffff ffffffffffffff ffffffffff l ................
Ffffffff ffffffffffffff ffffffffff l ................
Ffffffff ffffffffffffff ffffffffff l ................
<16 bytes per line>
These are row piece headers.
2801f501 00039d00
1-byte flag = 28
Hexadecimal 28 = binary 00101000 = -- H-F --- = head of rowpiece + first data piece
1 byte lb (itl slot) = 01
1-byte cc column count = f5 = 245 (because the last name500 column is not empty, it must be represented by ff for null, that is, 500 columns of data need to be stored)
4-byte dba
01 00039d = use dbms_utility.DATA_BLOCK_ADDRESS_FILE and dbms_utility.DATA_BLOCK_ADDRESS_BLOCK to query files and block numbers.
Details:
Select dbms_utility.DATA_BLOCK_ADDRESS_FILE (to_number ('0100039d', 'xxxxxxxxxx') file #,
Dbms_utility.DATA_BLOCK_ADDRESS_BLOCK (to_number ('0100039d', 'xxxxxxxxxx') block #
From dual;
_ Sys @ DAVID>/
FILE # BLOCK #
--------------------
4 925
That is, datafile 4 block 925 is in this block.
2-byte row #
00 00
Kdbr [0]
The rest is the ff that represents null.
BBED> p * kdbr [1]
Rowdata [1327]
-------------
Ub1 rowdata [1327] @ 7374 0x28
BBED> p * kdbr [0]
Rowdata [1581]
-------------
Ub1 rowdata [1581] @ 7628 0x04
7628-7374 = 254 bytes (from offset7374 to 7628, excluding 7628)
Where
9-byte row header
Therefore, the space for storing row data is 254-9 = 245 bytes.
Because 245 columns have null values and the last column is not empty, all null values are represented by ff (one byte), consuming a total of 245 bytes.
Let's take a look at its last data piece.
BBED> p * kdbr [0]
Rowdata [1581]
-------------
Ub1 rowdata [1581] @ 7628 0x04
BBED> dump/v offset 7628 count 1000
File:/u01/apps/oracle/oradata/david/users01.dbf (4)
Block: 925 Offsets: 7628 to 8191 Dba: 0x0100039d
-------------------------------------------------------
0401 ffff ffffffff l ................
Ffffffff ffffffffffffff ffffffffff l ................
Ffffffff ffffffffffffff ffffffffff l ................
Ffffffff ffffffffffffff ffffffffff l ................
Ffffffff ffffffffffffff ffffffffff l ................
Ffffffff ffffffffffffff ffffffffff l ................
Ffffffff ffffffffffffff ffffffffff l ................
Ffffffff ffffffffffffff ffffffffff l ................
Ffffffff ffffffffffffff ffffffffff l ................
Ffffffff ffffffffffffff ffffffffff l ................
Ffffffff ffffffffffffff ffffffffff l ................
Ffffffff ffffffffffffff ffffffffff l ................
Ffffffff ffffffffffffff ffffffffff l ................
Ffffffff ffffffffffffff ffffffffff l ................
Ffffffff ffffffffffffff ffffffffff l ................
Ffffffff ffffffffffffff ffffffffff l ................
Fffe2c01 64646464 64646464 64646464 l. t,. dddddddddddddd
64646464 64646464 64646464 64646464 l dddddddddddddddddd
64646464 64646464 64646464 64646464 l dddddddddddddddddd
64646464 64646464 64646464 64646464 l dddddddddddddddddd
64646464 64646464 64646464 64646464 l dddddddddddddddddd
64646464 64646464 64646464 64646464 l dddddddddddddddddd
64646464 64646464 64646464 64646464 l dddddddddddddddddd
64646464 64646464 64646464 64646464 l dddddddddddddddddd
64646464 64646464 64646464 64646464 l dddddddddddddddddd
64646464 64646464 64646464 64646464 l dddddddddddddddddd
64646464 64646464 64646464 64646464 l dddddddddddddddddd
64646464 64646464 64646464 64646464 l dddddddddddddddddd
64646464 64646464 64646464 64646464 l dddddddddddddddddd
64646464 64646464 64646464 64646464 l dddddddddddddddddd
64646464 64646464 64646464 64646464 l dddddddddddddddddd
64646464 64646464 64646464 64646464 l dddddddddddddddddd
64646464 64646464 64646464 64646464 l dddddddddddddddddd
64646464 64646464 64646464 64646464 l dddddddddddddddddd
64646464 64646464 64646464 64646431 l ddddddddddddddd1
01060aa4 l... Enabled
These are row piece headers.
0401ff
Where
1-byte flag = 04
Hexadecimal 4 = binary 00000100 = ----- L -- = last data piece
1 byte lb (itl slot) = 01
1-byte cc column count = ff = 255 (because the last name500 column is not empty, ff is required for null)
In summary, when the number of columns exceeds 253, the row will be split into multiple data piece Storage (similar to the row chain ), each time, we still use the column count of 1 byte to indicate the number of columns.
In addition, the data type of the last column is varchar2 (2000)
Here, we use 3 bytes to store its length.
Fe2c01
How to calculate it?
The last two bytes are used for representation.
Details:
The last 1 byte indicates the number of 256. For example, if the value is 01, the value is 256*1 = 256.
Plus the number of the first byte 2c = 44
256 + 44 = 300
It is estimated that fe is a computing identifier. Because no relevant information is found, it can only be confirmed by experiments.
Similarly,
253 = fefd00
255 = feff00
256 = fe0001
257 = fe0101
258 = fe0201
259 = fe0301
510 = fefe01
512 = fe0002
513 = fe0102
2000 = fed007
After continuous lab tests
When the length is greater than 250
It will use three bytes similar
Fe fb00 indicates the length.
The length is 250 and below.
Indicates the length in one byte.
Fe is the length bit, and the last two digits are the length values. Because it is little endian, the last two bytes need to be reversed.
510 = fefe01
512 = fe0002
513 = fe0102
Actually, it is in hexadecimal format.
510 = 01fe
512 = 0200
513 = 0201
Thank you for your answers.