Explore how oracle clob fields are stored

The clob field stores text. If less than 4000 bytes are stored, the clob field is stored together with the recording. If more than 4000 bytes are stored, it is not stored together with the record. It is worth noting that the 4000 bytes here are not equivalent to varchar2 (4000). What is the equivalent? Please refer to the following experiment. The database version is 10.2.0.1.0:

Drop table test_clob purge;
Create table test_clob
(
Id number,
Clob1 clob
);

Insert into test_clob values (1, Lpad ('20140901', 111, '1'); -- 100
Insert into test_clob values (2, Lpad ('20140901', 222, '1'); -- 500
Insert into test_clob values (3, Lpad ('20140901', 333, '1'); -- 1000
Insert into test_clob values (4, Lpad ('20140901', 444, '1'); -- 1982
Insert into test_clob values (5, Lpad ('20140901', 555, '1'); -- 1983
Commit;

Select rowid,
Dbms_rowid.rowid_object (rowid) object_id,
Dbms_rowid.rowid_relative_fno (rowid) file_id,
Dbms_rowid.rowid_block_number (rowid) block_id,
Dbms_rowid.rowid_row_number (rowid) num
From test_clob;
ROWID OBJECT_ID FILE_ID BLOCK_ID NUM
----------------------------------------------------------
AAAYQbAAFAADRpcAAA 99355 5 858716 0
AAAYQbAAFAADRpcAAB 99355 5 858716 1
AAAYQbAAFAADRpcAAC 99355 5 858716 2
AAAYQbAAFAADRpdAAA 99355 5 858717 0
AAAYQbAAFAADRpgAAA 99355 5 858720 0

Alter system dump datafile 5 block 858716;
Alter system dump datafile 5 block 858717;
Alter system dump datafile 5 block 858720;

View the dump file and you can see:

Col 0: [2] c1 02 -- Record id = 1 Why is 1 please see my previous post: http://blog.csdn.net/stevendbaguo/article/details/8010105
Col 1: [236]
00 54 00 01 02 0c 80 00 00 00 00 00 01 00 00 02 1a 85 00 d8 09 00 00
00 00 00 00 c8 00 00 00 00 00 01 00 31 00 31 00 31 00 31 00 31 00 31 00 31 00 31
........................................ ...................................
........................................ ...................................
 
Col 0: [2] c1 03-- Record id = 2
Col 1: [1036]
00 54 00 01 02 0c 80 00 00 00 00 00 01 00 00 02 1a 86 03 f8 09 00 00
00 00 00 03 e8 00 00 00 00 00 01 00 31 00 31 00 31 00 31 00 31 00 31 00 31 00 31
........................................ ...................................
........................................ ...................................


Col 0: [2] c1 04-- Record id = 3
Col 1: [2036]
00 54 00 01 02 0c 80 00 00 00 00 00 01 00 00 02 1a 87 07 e0 09 00 00
00 00 00 07 d0 00 00 00 00 01 00 31 00 31 00 31 00 31 00 31 00 31 00 31 00 31
........................................ ...................................
........................................ ...................................

Col 0: [2] c1 05-- Record id = 4
Col 1: [4000]
00 54 00 01 02 0c 80 00 00 00 00 00 01 00 00 02 1a 88 0f 8c 09 00 00
00 00 00 0f 7c 00 00 00 00 01 00 31 00 31 00 31 00 31 00 31 00 31 00 31 00 31
........................................ ...................................
........................................ ...................................


Col 0: [2] c1 06-- Record id = 5
Col 1: [40]
00 54 00 01 02 0c 80 00 00 00 00 00 01 00 00 02 1a 89 00 14 05 00 00
00 00 00 0f 7e 00 00 00 00 02 01 4d 98 ac

Record Lpad ('20140901', 111, '1'); -- 100
Record Lpad ('20140901', 222, '1') -- 500
Record Lpad ('20140901', 333, '1') -- 1000
Record Lpad ('20140901', 444, '1') -- 1982
Record Lpad ('20140901', 555, '1') -- 1983

We can see that the first record is 100 in length and the block length is 236, that is, 236 = 100*2 + 36. This is the case with other rules. At the same time, we can see that the storage exceeds 4000 bytes, that is, when the length is 1982, this is the critical value. After this is exceeded, clob will be stored elsewhere.