Oracle LOB storage in row

Do you know how Oracle stores LOB data with "ENABLE STORAGE IN ROW" and "COMPRESS"/"NOCOMPRESS" options?

According to the Oracle documentation:

The maximum amount of LOB data stored in the row is the maximum VARCHAR2 size (4000). This includes the control information as well as the LOB value. If you indicate that the LOB should be stored in the row, once the LOB value and control information is larger than approximately 4000, then the LOB value is automatically moved out of the row.

Create test tablespaces and table:

-- DROP TABLESPACE XTEST_DAT INCLUDING CONTENTS AND DATAFILES;
-- DROP TABLESPACE XTEST_LOB INCLUDING CONTENTS AND DATAFILES;

CREATE TABLESPACE XTEST_DAT DATAFILE '/oracle_tablespaces/xtest_dat.dat' SIZE 10M AUTOEXTEND ON;
CREATE TABLESPACE XTEST_LOB DATAFILE '/oracle_tablespaces/xtest_lob.dat' SIZE 10M AUTOEXTEND ON;

CREATE TABLE xtest_text
(
  text CLOB
)
TABLESPACE XTEST_DAT
LOB (text) STORE AS SECUREFILE (
TABLESPACE XTEST_LOB ENABLE STORAGE IN ROW CHUNK 8192
NOCACHE LOGGING NOCOMPRESS KEEP_DUPLICATES
);

SQL for retrieving tablespaces size:

SELECT TABLESPACE_NAME, SUM(BYTES) BYTES
FROM USER_SEGMENTS
WHERE TABLESPACE_NAME LIKE 'XTEST_%'
GROUP BY TABLESPACE_NAME;

Rows generator:

-- INSERT INTO xtest_text VALUES ('1');
-- COMMIT;

DECLARE
  string CLOB;
  multiplier NUMBER:=1;
BEGIN
  FOR i IN 1..multiplier
  LOOP
    string:= string || dbms_random.string('A', 1000);
  END LOOP;

  FOR i IN 1..1000
  LOOP
    INSERT INTO xtest_text VALUES (string);
  END LOOP;
  COMMIT;
END;

Follow these steps:

• run with multiplier = 1 to fill table with 1000 rows size of 1000 bytes
• check tablespaces size
• run with multiplier = 10 to fill table with 1000 rows size of 10000 bytes
• check tablespaces size
• run with multiplier = 5 to fill table with 1000 rows size of 5000 bytes
• check tablespaces size

Actual results:

With NOCOMPRESS option:

Tablespace name	Tablespace size	Tablespace size	Tablespace size	Tablespace size
	Inserted 1 row sizeof 1 byte	+ 1000 rows size of 1K	+ 1000 rows size of 10K	+ 1000 rows size of 5K
XTEST_LOB	196608	196608	20185088	27525120
XTEST_DAT	65536	2097152	2097152	2097152

All records size of 1K (with size less than 4000 bytes) are stored in the DAT tablespace. All records size of 10K and 5K (with size more than 4000 bytes) are in the LOB tablespace.

With COMPRESS option:

Tablespace name	Tablespace size	Tablespace size	Tablespace size	Tablespace size
	Inserted 1 row sizeof 1 byte	+ 1000 rows size of 1K	+ 1000 rows size of 10K	+ 1000 rows size of 5K
XTEST_LOB	196608	196608	10747904	10747904
XTEST_DAT	65536	1048576	2097152	9437184

All records size of 1K (with size less than 4000 bytes) are stored in the DAT tablespace. All records size of 10K (with compressed size more than 4000 bytes) are in the LOB tablespace and partially in DAT. All records size of 5K (with compressed size less than 4000 bytes) are stored in the DAT tablespace.