--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列的选项很多，仅仅是测试了下一些用法
各种选项与性能的关系还理解不足
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9240380/viewspace-761448/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/9240380/viewspace-761448/
386
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
