Tim Hall perfectly describes in his excellent post how new extended datatypes are stored on Oracle 12c.
I just found interesting parameter “_scalar_type_lob_storage_threshold“ – “threshold for VARCHAR2, NVARCHAR2, and RAW storage as BLOB” – This parameter is the max size in bytes, at which these data types will be stored “inline” as simple datatypes, without creation of the lob segments.
See little example:
Controlling store extended varchars as lob
SQL> @param_ _scalar_type_lob_storage_threshold; NAME VALUE DEFLT TYPE DESCRIPTION ---------------------------------------- ------------ ------------ ------------ ------------------------------------------------------------ _scalar_type_lob_storage_threshold 4000 TRUE number threshold for VARCHAR2, NVARCHAR2, and RAW storage as BLOB SQL> select * from user_lobs; no rows selected SQL> create table T_4000( 2 i int generated always as identity 3 ,v1000 varchar2(1000) 4 ,v4000 varchar2(4000) 5 ,v4500 varchar2(4500) 6 ); Table created. SQL> alter system set "_scalar_type_lob_storage_threshold"=5000; System altered. SQL> create table T_5000( 2 i int generated always as identity 3 ,v1000 varchar2(1000) 4 ,v4000 varchar2(4000) 5 ,v4500 varchar2(4500) 6 ); Table created. SQL> select table_name,column_name ,data_type,data_type_mod,data_length,char_col_decl_length,char_length,char_used 2 from user_tab_columns; TABLE_NAME COLUMN_NAM DATA_TYPE DAT DATA_LENGTH CHAR_COL_DECL_LENGTH CHAR_LENGTH C ---------- ---------- ---------- --- ----------- -------------------- ----------- - T_4000 V4500 VARCHAR2 4500 4500 4500 B T_4000 V4000 VARCHAR2 4000 4000 4000 B T_4000 V1000 VARCHAR2 1000 1000 1000 B T_4000 I NUMBER 22 0 T_5000 V4500 VARCHAR2 4500 4500 4500 B T_5000 V4000 VARCHAR2 4000 4000 4000 B T_5000 V1000 VARCHAR2 1000 1000 1000 B T_5000 I NUMBER 22 0 8 rows selected. SQL> select table_name,column_name,chunk,retention,cache,logging,encrypt,compression,deduplication,in_row,securefile 2 from user_lobs; TABLE_NAME COLUMN_NAM CHUNK RETENTION CACHE LOGGING ENCR COMPRE DEDUPLICATION IN_ SEC ---------- ---------- ---------- ---------- ---------- ------- ---- ------ --------------- --- --- T_4000 V4500 8192 YES YES NO NO NO YES YES
[collapse]
Note that there are no lobs for table t_5000!