In Oracle Release 19 Document: Database SecureFiles and Large Objects Developer's Guide
(
https://docs.oracle.com/en/database/oracle/oracle-database/19/adlob/LOB-storage-with-applications.html#GUID-B82B3C24-1FAF-4661-96A0-28241FD2A052 )
there are following description about "Inline and Out-of-Line LOB Storage":
============================== Oracle Docu =========================
13.3 LOB Storage Parameters
For a discussion of SECUREFILE parameters:
13.3.1 Inline and Out-of-Line LOB Storage
If you update a LOB that is stored out-of-line and the resulting LOB is less than approximately 4000 bytes, it is still stored out-of-line.
====================================================================
My question is if it is still true for all Oracle 19c on this text:
"If you update a LOB that is stored out-of-line and the resulting LOB is less than approximately 4000 bytes, it is still stored out-of-line."
If yes, how to construct a test code to observe this behavior ?
Two scenarios here to look at
1) "enable storage in row"
With some block dumps, you'll see initially storage out of row for a large clob ("col 1: [38]" - 38 bytes being the locator) and then it flips back to inline when the clob is shrunk ("col 1: [140]")
SQL> create table t ( pk int, c clob )
2 lob (c) store as ( enable storage in row );
Table created.
SQL> insert into t
2 values (1, rpad('x',4000,'x'));
1 row created.
SQL> commit;
Commit complete.
SQL> col block_no new_value b
SQL> col file_no new_value f
SQL> select
2 dbms_rowid.rowid_block_number(rowid) block_no,
3 dbms_rowid.rowid_relative_fno(rowid) file_no
4 from t ;
BLOCK_NO FILE_NO
---------- ----------
192563 12
SQL> col trcfile new_value fname nopri
SQL> col trace_file new_value trc
SQL> select value trace_file, substr(value,1+instr(value,'\',-1)) trcfile
2 from v$diag_info
3 where name = 'Default Trace File';
TRACE_FILE
----------------------------------------------------------------------------------------------------------------------------------
C:\ORACLE\diag\rdbms\db21\db21\trace\db21_ora_51480.trc
SQL> alter system dump datafile &&f block &&b;
System altered.
SQL> select col
2 from external (
3 ( col varchar2(4000) )
4 type oracle_loader
5 default directory tracedir
6 access parameters
7 ( records delimited by newline
8 nobadfile
9 nologfile
10 nodiscardfile
11 )
12 location ( '&&fname' )
13 reject limit unlimited ) ext
14 where substr(col,1,3) in (
15 'blo',
16 'tab',
17 'tl:',
18 'col'
19 );
old 12: location ( '&&fname' )
new 12: location ( 'db21_ora_51480.trc' )
COL
----------------------------------------------------------------------------------------------------------------------------------
block_row_dump:
tab 0
tl: 45 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 2] c1 02
col 1: [38]
block_row_dump:
tab 0
tl: 45 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 2] c1 02
col 1: [38]
10 rows selected.
SQL> update t set c = 'i am now a small clob that will consume about 100 bytes';
1 row updated.
SQL> commit;
Commit complete.
SQL> col block_no new_value b
SQL> col file_no new_value f
SQL> select
2 dbms_rowid.rowid_block_number(rowid) block_no,
3 dbms_rowid.rowid_relative_fno(rowid) file_no
4 from t ;
BLOCK_NO FILE_NO
---------- ----------
192563 12
SQL> col trcfile new_value fname nopri
SQL> col trace_file new_value trc
SQL> select value trace_file, substr(value,1+instr(value,'\',-1)) trcfile
2 from v$diag_info
3 where name = 'Default Trace File';
TRACE_FILE
----------------------------------------------------------------------------------------------------------------------------------
C:\ORACLE\diag\rdbms\db21\db21\trace\db21_ora_53684.trc
SQL> alter system dump datafile &&f block &&b;
System altered.
SQL> select col
2 from external (
3 ( col varchar2(4000) )
4 type oracle_loader
5 default directory tracedir
6 access parameters
7 ( records delimited by newline
8 nobadfile
9 nologfile
10 nodiscardfile
11 )
12 location ( '&&fname' )
13 reject limit unlimited ) ext
14 where substr(col,1,3) in (
15 'blo',
16 'tab',
17 'tl:',
18 'col'
19 );
old 12: location ( '&&fname' )
new 12: location ( 'db21_ora_53684.trc' )
COL
----------------------------------------------------------------------------------------------------------------------------------
block_row_dump:
tab 0
tl: 147 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 2] c1 02
col 1: [140]
block_row_dump:
tab 0
tl: 147 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 2] c1 02
col 1: [140]
10 rows selected.
SQL>
2) "disable storage in row"
Simply change the demo script above to be "disable storage in row" and you'll see that it does not