--本文学习create table lob_storage_clause

--创建lob列的表
SQL> create table t_test(a blob);
 
Table created

---查询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?                                           
 
--查看lob列的表之配置信息
SQL> select table_name,column_name,segment_name,index_name,tablespace_name from user_lobs where table_name='T_TEST';
 
TABLE_NAME                     COLUMN_NAME                                                                      SEGMENT_NAME                   INDEX_NAME                     TABLESPACE_NAME
------------------------------ -------------------------------------------------------------------------------- ------------------------------ ------------------------------ ------------------------------
T_TEST                         A                                                                                SYS_LOB0000067044C00001$$      SYS_IL0000067044C00001$$       TBS_HANG
 
SQL>

 
SQL> drop table t_test purge;
 
Table dropped
 
--显式指定lob存储在某个表空间
SQL> create table t_test(a blob) lob(a) store as (tablespace users);
 
Table created
 
SQL> select table_name,column_name,segment_name,index_name,tablespace_name from user_lobs where table_name='T_TEST';
 
TABLE_NAME                     COLUMN_NAME                                                                      SEGMENT_NAME                   INDEX_NAME                     TABLESPACE_NAME
------------------------------ -------------------------------------------------------------------------------- ------------------------------ ------------------------------ ------------------------------
T_TEST                         A                                                                                SYS_LOB0000067047C00001$$      SYS_IL0000067047C00001$$       USERS
 
SQL> select table_name,column_name,segment_name,index_name,tablespace_name,in_row from user_lobs where table_name='T_TEST';
 
TABLE_NAME                     COLUMN_NAME                                                                      SEGMENT_NAME                   INDEX_NAME                     TABLESPACE_NAME                IN_ROW
------------------------------ -------------------------------------------------------------------------------- ------------------------------ ------------------------------ ------------------------------ ------
T_TEST                         A                                                                                SYS_LOB0000067047C00001$$      SYS_IL0000067047C00001$$       USERS                          YES
 
SQL>

SQL> drop table t_test purge;
 
Table dropped
 
SQL> create table t_test(a blob) lob(a) store as (tablespace users disable storage in row);
 
Table created
 
 
---in_row表示lob是否和所依赖的记录存储在一起
SQL> select table_name,column_name,segment_name,index_name,tablespace_name,in_row from user_lobs where table_name='T_TEST';
 
TABLE_NAME                     COLUMN_NAME                                                                      SEGMENT_NAME                   INDEX_NAME                     TABLESPACE_NAME                IN_ROW
------------------------------ -------------------------------------------------------------------------------- ------------------------------ ------------------------------ ------------------------------ ------
T_TEST                         A                                                                                SYS_LOB0000067050C00001$$      SYS_IL0000067050C00001$$       USERS                          NO
 
---其它参数不再测试,皆同理,如下列出所有lob parameter,以备后用:
 
{ TABLESPACE tablespace
| { ENABLE | DISABLE } STORAGE IN ROW
| storage_clause
| CHUNK integer
| PCTVERSION integer
| RETENTION
| FREEPOOLS integer
| { CACHE
  | { NOCACHE | CACHE READS } [ logging_clause ]
  }
}
  [ TABLESPACE tablespace
  | { ENABLE | DISABLE } STORAGE IN ROW
  | storage_clause
  | CHUNK integer
  | PCTVERSION integer
  | RETENTION
  | FREEPOOLS integer
  | { CACHE
    | { NOCACHE | CACHE READS } [ logging_clause ]
    }
  ]...
 
 
 
---各lob参数注解
LOB_parameters

The LOB_parameters clause lets you specify various elements of LOB storage.
 
 ---默认情况下,如果lob列长度不足4000bytes-系统控制信息.你可以开启storage in row
ENABLE STORAGE IN ROW  If you enable storage in row, then the LOB value is stored in the row (inline) if its length is less than approximately 4000
bytes minus system control information. This is the default.
 
----开启storage in row的限制:对于iot表,一般不能指定此参数,除非配置了overflow段
Restriction on Enabling Storage in Row For an index-organized table, you cannot specify this parameter unless you have specified an OVERFLOW segment
in the index_org_table_clause.
 
 --而相反,disable storage in row,lob列的值则存储在基记录之外,不管lob的长度
DISABLE STORAGE IN ROW  If you disable storage in row, then the LOB value is stored outside of the row out of line regardless of the length of the LOB value.
 
 
 ---lob定位器始终存储的基记录中,不管lob值存储在哪儿.如果用alter table move重设storage in row,则不能再改变它的值.
The LOB locator is always stored inline regardless of where the LOB value is stored. You cannot change the value of STORAGE IN ROW once it is set except
by moving the table. See the move_table_clause in the ALTER TABLE documentation for more information.
 
 
 --chunk参数,用于操作lob可以分配的字节数.默认是一个oracle block.此参数分配策略为:四舍五入,即指定2050,则四舍五入为4096;此参数最大值为32k
CHUNK integer  Specify the number of bytes to be allocated for LOB manipulation. If integer is not a multiple of the database block size,
then the database rounds up in bytes to the next multiple. For example, if the database block size is 2048 and integer is 2050, then the database
allocates 4096 bytes (2 blocks). The maximum value is 32768 (32K), which is the largest Oracle Database block size allowed. The default CHUNK size
is one Oracle Database block.
 
The value of CHUNK must be less than or equal to the value of NEXT, either the default value or that specified in the storage_clause. If CHUNK exceeds
the value of NEXT, then the database returns an error.You cannot change the value of CHUNK once it is set.
 
 
 ---pctversion,用于指定维护lob各个版本的存储空间显比率.默认为10,即:如果花费或占用了整个lob存储空间的10%,则重用存储lob老版本的数据空间,有点类似pct_used
PCTVERSION integer  Specify the maximum percentage of overall LOB storage space used for maintaining old versions of the LOB. The default value is 10,
meaning that older versions of the LOB data are not overwritten until they consume 10% of the overall LOB storage space.
 
 
 -----无论数据库运行在手工或自动撤消模式,皆可指定pctversion.在手工撤消模式下,pctversion是默认的.retention是自动撤消模式的默认.
 -----不能同时指定pctversion和retention
You can specify the PCTVERSION parameter whether the database is running in manual or automatic undo mode. PCTVERSION is the default in manual undo
mode. RETENTION is the default in automatic undo mode. You cannot specify both PCTVERSION and RETENTION.
 
 
 ----retention用于维护lob列的旧版本数据,oracle用undo_retention来决定存储多大的已提交的undo数据,用地维护oracle
RETENTION  Use this clause to indicate that Oracle Database should retain old versions of this LOB column. Oracle Database uses the value of the UNDO_RETENTION
initialization parameter to determine the amount of committed undo data to retain in the database.
 
 ---只能数据库运行在自动撤消模式下,方可使用retention.
You can specify the RETENTION parameter only if the database is running in automatic undo mode. In this mode, RETENTION is the default value unless you specify
 PCTVERSION. You cannot specify both PCTVERSION and RETENTION.