create table進階學習系列(五)

--本文學習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.