--user_lobs对应create table基于lob的诸多选项命令
SQL> desc user_lobs;
Name            Type           Nullable Default Comments                                                              
--------------- -------------- -------- ------- ----------------------------------------------------------------------
TABLE_NAME      VARCHAR2(30)   Y                Name of the table containing the LOB                                  
COLUMN_NAME     VARCHAR2(4000) Y                Name of the LOB column or attribute                                   
SEGMENT_NAME    VARCHAR2(30)   Y                Name of the LOB segment                                               
TABLESPACE_NAME VARCHAR2(30)   Y                Name of the tablespace containing the LOB segment                     
INDEX_NAME      VARCHAR2(30)   Y                Name of the LOB index                                                 
CHUNK           NUMBER         Y                Size of the LOB chunk as a unit of allocation/manipulation in bytes   
PCTVERSION      NUMBER         Y                Maximum percentage of the LOB space used for versioning               
RETENTION       NUMBER         Y                Maximum time duration for versioning of the LOB space                 
FREEPOOLS       NUMBER         Y                Number of freepools for this LOB segment                              
CACHE           VARCHAR2(10)   Y                Is the LOB accessed through the buffer cache?                         
LOGGING         VARCHAR2(7)    Y                Are changes to the LOB logged?                                        
ENCRYPT         VARCHAR2(4)    Y                Is this lob encrypted?                                                
COMPRESSION     VARCHAR2(6)    Y                What level of compression is used for this lob?                       
DEDUPLICATION   VARCHAR2(15)   Y                What kind of DEDUPLICATION is used for this lob?                      
IN_ROW          VARCHAR2(3)    Y                Are some of the LOBs stored with the base row?                        
FORMAT          VARCHAR2(15)   Y                Is the LOB storage format dependent on the endianness of the platform?
PARTITIONED     VARCHAR2(3)    Y                Is the LOB column in a partitioned table?                             
SECUREFILE      VARCHAR2(3)    Y                Is the LOB a SECUREFILE LOB?                                          
SEGMENT_CREATED VARCHAR2(3)    Y                Is the LOB segment created?                                           

---disable storage in row让lob与其行不存储同一个块中,防止产生行链接,当然哪果lob很少，可以配置为enable storage in row
SQL> create table t_lob_2(a int,b blob) lob(b) store as securefile(disable storage in row);
 
Table created

---查询in_row列即可
SQL> select * from user_lobs;
 
TABLE_NAME                     COLUMN_NAME                                                                      SEGMENT_NAME                   TABLESPACE_NAME                INDEX_NAME                          CHUNK PCTVERSION  RETENTION  FREEPOOLS CACHE      LOGGING ENCRYPT COMPRESSION DEDUPLICATION   IN_ROW FORMAT          PARTITIONED SECUREFILE SEGMENT_CREATED
------------------------------ -------------------------------------------------------------------------------- ------------------------------ ------------------------------ ------------------------------ ---------- ---------- ---------- ---------- ---------- ------- ------- ----------- --------------- ------ --------------- ----------- ---------- ---------------
T_LOB                          B                                                                                IDX_T_LOB                      TBS_NEWLY                      SYS_IL0000073748C00002$$             8192         10                       NO         YES     NONE    NONE        NONE            YES    NOT APPLICABLE  NO          NO         NO
T_LOB_2                        B                                                                                SYS_LOB0000073844C00002$$      TBS_NEWLY                      SYS_IL0000073844C00002$$             8192                   900            NO         YES     NO      NO          NO              NO     NOT APPLICABLE  NO          YES        NO

--cache选项是否缓存lob列,如经常访问lob且sga充足，可以配置为cache否则反之
SQL> create table t_lob_3(a int,b blob) lob(b) store as securefile(disable storage in row cache);
 
Table created
 
SQL> select * from user_lobs;
 
TABLE_NAME                     COLUMN_NAME                                                                      SEGMENT_NAME                   TABLESPACE_NAME                INDEX_NAME                          CHUNK PCTVERSION  RETENTION  FREEPOOLS CACHE      LOGGING ENCRYPT COMPRESSION DEDUPLICATION   IN_ROW FORMAT          PARTITIONED SECUREFILE SEGMENT_CREATED
------------------------------ -------------------------------------------------------------------------------- ------------------------------ ------------------------------ ------------------------------ ---------- ---------- ---------- ---------- ---------- ------- ------- ----------- --------------- ------ --------------- ----------- ---------- ---------------
T_LOB                          B                                                                                IDX_T_LOB                      TBS_NEWLY                      SYS_IL0000073748C00002$$             8192         10                       NO         YES     NONE    NONE        NONE            YES    NOT APPLICABLE  NO          NO         NO
T_LOB_2                        B                                                                                SYS_LOB0000073844C00002$$      TBS_NEWLY                      SYS_IL0000073844C00002$$             8192                   900            NO         YES     NO      NO          NO              NO     NOT APPLICABLE  NO          YES        NO
T_LOB_3                        B                                                                                SYS_LOB0000073847C00002$$      TBS_NEWLY                      SYS_IL0000073847C00002$$             8192                   900            YES        YES     NO      NO          NO              NO     NOT APPLICABLE  NO          YES        NO

---retention与行一致读或闪回操作有关
SQL> create table t_lob_3(a int,b blob) lob(b) store as securefile(disable storage in row cache retention max);
 
create table t_lob_3(a int,b blob) lob(b) store as securefile(disable storage in row cache retention max)
 
ORA-00955: name is already used by an existing object
 
SQL> create table t_lob_4(a int,b blob) lob(b) store as securefile(disable storage in row cache retention max);
 
Table created
 
SQL> select * from user_lobs;
 
TABLE_NAME                     COLUMN_NAME                                                                      SEGMENT_NAME                   TABLESPACE_NAME                INDEX_NAME                          CHUNK PCTVERSION  RETENTION  FREEPOOLS CACHE      LOGGING ENCRYPT COMPRESSION DEDUPLICATION   IN_ROW FORMAT          PARTITIONED SECUREFILE SEGMENT_CREATED
------------------------------ -------------------------------------------------------------------------------- ------------------------------ ------------------------------ ------------------------------ ---------- ---------- ---------- ---------- ---------- ------- ------- ----------- --------------- ------ --------------- ----------- ---------- ---------------
T_LOB                          B                                                                                IDX_T_LOB                      TBS_NEWLY                      SYS_IL0000073748C00002$$             8192         10                       NO         YES     NONE    NONE        NONE            YES    NOT APPLICABLE  NO          NO         NO
T_LOB_2                        B                                                                                SYS_LOB0000073844C00002$$      TBS_NEWLY                      SYS_IL0000073844C00002$$             8192                   900            NO         YES     NO      NO          NO              NO     NOT APPLICABLE  NO          YES        NO
T_LOB_3                        B                                                                                SYS_LOB0000073847C00002$$      TBS_NEWLY                      SYS_IL0000073847C00002$$             8192                   900            YES        YES     NO      NO          NO              NO     NOT APPLICABLE  NO          YES        NO
T_LOB_4                        B                                                                                SYS_LOB0000073850C00002$$      TBS_NEWLY                      SYS_IL0000073850C00002$$             8192          0                       YES        YES     NO      NO          NO              NO     NOT APPLICABLE  NO          YES        NO

---pctversion不能与retention共用
SQL> create table t_lob_5(a int,b blob) lob(b) store as securefile(disable storage in row cache retention min 10);
 
Table created
 
SQL> select * from user_lobs;
 
TABLE_NAME                     COLUMN_NAME                                                                      SEGMENT_NAME                   TABLESPACE_NAME                INDEX_NAME                          CHUNK PCTVERSION  RETENTION  FREEPOOLS CACHE      LOGGING ENCRYPT COMPRESSION DEDUPLICATION   IN_ROW FORMAT          PARTITIONED SECUREFILE SEGMENT_CREATED
------------------------------ -------------------------------------------------------------------------------- ------------------------------ ------------------------------ ------------------------------ ---------- ---------- ---------- ---------- ---------- ------- ------- ----------- --------------- ------ --------------- ----------- ---------- ---------------
T_LOB                          B                                                                                IDX_T_LOB                      TBS_NEWLY                      SYS_IL0000073748C00002$$             8192         10                       NO         YES     NONE    NONE        NONE            YES    NOT APPLICABLE  NO          NO         NO
T_LOB_2                        B                                                                                SYS_LOB0000073844C00002$$      TBS_NEWLY                      SYS_IL0000073844C00002$$             8192                   900            NO         YES     NO      NO          NO              NO     NOT APPLICABLE  NO          YES        NO
T_LOB_3                        B                                                                                SYS_LOB0000073847C00002$$      TBS_NEWLY                      SYS_IL0000073847C00002$$             8192                   900            YES        YES     NO      NO          NO              NO     NOT APPLICABLE  NO          YES        NO
T_LOB_4                        B                                                                                SYS_LOB0000073850C00002$$      TBS_NEWLY                      SYS_IL0000073850C00002$$             8192          0                       YES        YES     NO      NO          NO              NO     NOT APPLICABLE  NO          YES        NO
T_LOB_5                        B                                                                                SYS_LOB0000073853C00002$$      TBS_NEWLY                      SYS_IL0000073853C00002$$             8192          0                       YES        YES     NO      NO          NO              NO     NOT APPLICABLE  NO          YES        NO
 
SQL> create table t_lob_6(a int,b blob) lob(b) store as securefile(disable storage in row cache retention auto);
 
Table created
 
SQL> select * from user_lobs;
 
TABLE_NAME                     COLUMN_NAME                                                                      SEGMENT_NAME                   TABLESPACE_NAME                INDEX_NAME                          CHUNK PCTVERSION  RETENTION  FREEPOOLS CACHE      LOGGING ENCRYPT COMPRESSION DEDUPLICATION   IN_ROW FORMAT          PARTITIONED SECUREFILE SEGMENT_CREATED
------------------------------ -------------------------------------------------------------------------------- ------------------------------ ------------------------------ ------------------------------ ---------- ---------- ---------- ---------- ---------- ------- ------- ----------- --------------- ------ --------------- ----------- ---------- ---------------
T_LOB                          B                                                                                IDX_T_LOB                      TBS_NEWLY                      SYS_IL0000073748C00002$$             8192         10                       NO         YES     NONE    NONE        NONE            YES    NOT APPLICABLE  NO          NO         NO
T_LOB_2                        B                                                                                SYS_LOB0000073844C00002$$      TBS_NEWLY                      SYS_IL0000073844C00002$$             8192                   900            NO         YES     NO      NO          NO              NO     NOT APPLICABLE  NO          YES        NO
T_LOB_3                        B                                                                                SYS_LOB0000073847C00002$$      TBS_NEWLY                      SYS_IL0000073847C00002$$             8192                   900            YES        YES     NO      NO          NO              NO     NOT APPLICABLE  NO          YES        NO
T_LOB_4                        B                                                                                SYS_LOB0000073850C00002$$      TBS_NEWLY                      SYS_IL0000073850C00002$$             8192          0                       YES        YES     NO      NO          NO              NO     NOT APPLICABLE  NO          YES        NO
T_LOB_5                        B                                                                                SYS_LOB0000073853C00002$$      TBS_NEWLY                      SYS_IL0000073853C00002$$             8192          0                       YES        YES     NO      NO          NO              NO     NOT APPLICABLE  NO          YES        NO
T_LOB_6                        B                                                                                SYS_LOB0000073856C00002$$      TBS_NEWLY                      SYS_IL0000073856C00002$$             8192          0                       YES        YES     NO      NO          NO              NO     NOT APPLICABLE  NO          YES        NO
 
6 rows selected
 
SQL> create table t_lob_7(a int,b blob) lob(b) store as securefile(disable storage in row cache retention none);
 
Table created
 
SQL> select * from user_lobs;
 
TABLE_NAME                     COLUMN_NAME                                                                      SEGMENT_NAME                   TABLESPACE_NAME                INDEX_NAME                          CHUNK PCTVERSION  RETENTION  FREEPOOLS CACHE      LOGGING ENCRYPT COMPRESSION DEDUPLICATION   IN_ROW FORMAT          PARTITIONED SECUREFILE SEGMENT_CREATED
------------------------------ -------------------------------------------------------------------------------- ------------------------------ ------------------------------ ------------------------------ ---------- ---------- ---------- ---------- ---------- ------- ------- ----------- --------------- ------ --------------- ----------- ---------- ---------------
T_LOB                          B                                                                                IDX_T_LOB                      TBS_NEWLY                      SYS_IL0000073748C00002$$             8192         10                       NO         YES     NONE    NONE        NONE            YES    NOT APPLICABLE  NO          NO         NO
T_LOB_2                        B                                                                                SYS_LOB0000073844C00002$$      TBS_NEWLY                      SYS_IL0000073844C00002$$             8192                   900            NO         YES     NO      NO          NO              NO     NOT APPLICABLE  NO          YES        NO
T_LOB_3                        B                                                                                SYS_LOB0000073847C00002$$      TBS_NEWLY                      SYS_IL0000073847C00002$$             8192                   900            YES        YES     NO      NO          NO              NO     NOT APPLICABLE  NO          YES        NO
T_LOB_4                        B                                                                                SYS_LOB0000073850C00002$$      TBS_NEWLY                      SYS_IL0000073850C00002$$             8192          0                       YES        YES     NO      NO          NO              NO     NOT APPLICABLE  NO          YES        NO
T_LOB_5                        B                                                                                SYS_LOB0000073853C00002$$      TBS_NEWLY                      SYS_IL0000073853C00002$$             8192          0                       YES        YES     NO      NO          NO              NO     NOT APPLICABLE  NO          YES        NO
T_LOB_6                        B                                                                                SYS_LOB0000073856C00002$$      TBS_NEWLY                      SYS_IL0000073856C00002$$             8192          0                       YES        YES     NO      NO          NO              NO     NOT APPLICABLE  NO          YES        NO
T_LOB_7                        B                                                                                SYS_LOB0000073859C00002$$      TBS_NEWLY                      SYS_IL0000073859C00002$$             8192          0                       YES        YES     NO      NO          NO              NO     NOT APPLICABLE  NO          YES        NO
 
7 rows selected

--cache reads选项
SQL> create table t_lob_10(a int,b blob) lob(b) store as securefile(pctversion 100 disable storage in row cache reads);
 
Table created
 
SQL> select * from user_lobs;
 
TABLE_NAME                     COLUMN_NAME                                                                      SEGMENT_NAME                   TABLESPACE_NAME                INDEX_NAME                          CHUNK PCTVERSION  RETENTION  FREEPOOLS CACHE      LOGGING ENCRYPT COMPRESSION DEDUPLICATION   IN_ROW FORMAT          PARTITIONED SECUREFILE SEGMENT_CREATED
------------------------------ -------------------------------------------------------------------------------- ------------------------------ ------------------------------ ------------------------------ ---------- ---------- ---------- ---------- ---------- ------- ------- ----------- --------------- ------ --------------- ----------- ---------- ---------------
T_LOB                          B                                                                                IDX_T_LOB                      TBS_NEWLY                      SYS_IL0000073748C00002$$             8192         10                       NO         YES     NONE    NONE        NONE            YES    NOT APPLICABLE  NO          NO         NO
T_LOB_2                        B                                                                                SYS_LOB0000073844C00002$$      TBS_NEWLY                      SYS_IL0000073844C00002$$             8192                   900            NO         YES     NO      NO          NO              NO     NOT APPLICABLE  NO          YES        NO
T_LOB_3                        B                                                                                SYS_LOB0000073847C00002$$      TBS_NEWLY                      SYS_IL0000073847C00002$$             8192                   900            YES        YES     NO      NO          NO              NO     NOT APPLICABLE  NO          YES        NO
T_LOB_4                        B                                                                                SYS_LOB0000073850C00002$$      TBS_NEWLY                      SYS_IL0000073850C00002$$             8192          0                       YES        YES     NO      NO          NO              NO     NOT APPLICABLE  NO          YES        NO
T_LOB_5                        B                                                                                SYS_LOB0000073853C00002$$      TBS_NEWLY                      SYS_IL0000073853C00002$$             8192          0                       YES        YES     NO      NO          NO              NO     NOT APPLICABLE  NO          YES        NO
T_LOB_6                        B                                                                                SYS_LOB0000073856C00002$$      TBS_NEWLY                      SYS_IL0000073856C00002$$             8192          0                       YES        YES     NO      NO          NO              NO     NOT APPLICABLE  NO          YES        NO
T_LOB_7                        B                                                                                SYS_LOB0000073859C00002$$      TBS_NEWLY                      SYS_IL0000073859C00002$$             8192          0                       YES        YES     NO      NO          NO              NO     NOT APPLICABLE  NO          YES        NO
T_LOB_8                        B                                                                                SYS_LOB0000073862C00002$$      TBS_NEWLY                      SYS_IL0000073862C00002$$             8192          0                       YES        YES     NO      NO          NO              NO     NOT APPLICABLE  NO          YES        NO
T_LOB_9                        B                                                                                SYS_LOB0000073865C00002$$      TBS_NEWLY                      SYS_IL0000073865C00002$$             8192          0                       YES        YES     NO      NO          NO              NO     NOT APPLICABLE  NO          YES        NO
T_LOB_10                       B                                                                                SYS_LOB0000073868C00002$$      TBS_NEWLY                      SYS_IL0000073868C00002$$             8192          0                       CACHEREADS YES     NO      NO          NO              NO     NOT APPLICABLE  NO          YES        NO
 
10 rows selected

小结：create table基于lob列的选项很多，仅仅是测试了下一些用法
      各种选项与性能的关系还理解不足