Storage of over 253 internal data columns in Oracle

Table creation statement declarev_sqlvarchar2 (32767); beginv_ SQL:

Table creation statement declare v_ SQL varchar2 (32767); begin v_ SQL: =

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)