Explore how Oracle CLOB fields are stored

The Clob field stores text, and if the store is less than 4,000 bytes, the Clob field is stored with the record, and if the store is more than 4,000 bytes, it is not stored with the record. It is worth noting that the 4,000 bytes here are not equivalent to VARCHAR2 (4000), which is equivalent to what, see 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 (' 111 ', 100, ' 1 '));--236
INSERT into Test_clob values (2,lpad (' 222 ', 500, ' 1 '));--1036
INSERT into Test_clob values (3,lpad (' 333 ', 1000, ' 1 '));--2036
INSERT into Test_clob values (4,lpad (' 444 ', 1982, ' 1 '));--4000
INSERT into Test_clob values (5,lpad (' 555 ', 1983, ' 1 '));--4002
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 to see:

Col 0: [2] C1 --record id=1 Why is 1 please see my previous post: http://blog.csdn.net/stevendbaguo/article/details/8010105
Col 1: [236]
At 0c, a. 1a------ D8 -XX
The C8 of the xx, XX, XX, and the XX to the
 ...........................................................................
 ...........................................................................
 
Col 0: [2] C1--Record id=2
Col 1: [1036]
At 0c, a. 1a-------- F8
The E8 of the xx-xx-xx-----XX
 ...........................................................................
 ...........................................................................


Col 0: [2] C1--Record id=3
Col 1: [2036]
At 0c (1a )---------XX
The d0 of the xx-xx-xx-xx----
 ...........................................................................
 ...........................................................................
  
Col 0: [2] C1 --record id=4
Col 1: [4000]
At 0c, a. 1a, 0f, 8c , XX, and xx xx
At 0f 7c , the xx-xx-xx-----
 ...........................................................................
 ........................................................................... 


Col 0: [2] C1 --record id=5
Col 1: [+]
At 0c, a. 1a (XX)---------XX
0f 7e xx, XX, and the 4d 98 AC

record Lpad (' 111 ', 1 '),--236
record   lpad (' 222 ', 500, ' 1 ')--1036
record   lpad (' 333 ', +, ' 1 ')--2036
record   lpad (' 444 ', 1982, ' 1 ')--4000
record   lpad (' 555 ', 1983, ' 1 ')--4002

    See the law, the first record we think the length is 100, there is the length of the block is 236, is 236=100*2+36, the other laws are like this. You can also see that the storage is more than 4,000 bytes, that is, the length of 1982 when this is the threshold, greater than this after the CLOB to save to another place.