oracle blob_clob列的表如何指定sement_name及index name

参考：

http://space.itpub.net/267265/viewspace-760948

 

---创建lob列的表
SQL> create table t_lob(a int,b clob,c blob);
 
Table created

---查看lob列表信息
SQL> select * from user_lobs;--主要看segment_name,index_name列，即lob类型会独立归属于lob segment和产生一个对应的index
 
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                                                                                SYS_LOB0000073737C00002$$      TBS_NEWLY                      SYS_IL0000073737C00002$$             8192                   900            NO         YES     NONE    NONE        NONE            YES    ENDIAN NEUTRAL  NO          NO         NO
T_LOB                          C                                                                                SYS_LOB0000073737C00003$$      TBS_NEWLY                      SYS_IL0000073737C00003$$             8192                   900            NO         YES     NONE    NONE        NONE            YES    NOT APPLICABLE  NO          NO         NO

--由下可知segment_name构成为：sys_lob00000+object_id+0000+column_id+$$
SQL> select * from user_objects where object_name='T_LOB';
 
OBJECT_NAME                                                                      SUBOBJECT_NAME                  OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE         CREATED     LAST_DDL_TIME TIMESTAMP           STATUS  TEMPORARY GENERATED SECONDARY  NAMESPACE EDITION_NAME
-------------------------------------------------------------------------------- ------------------------------ ---------- -------------- ------------------- ----------- ------------- ------------------- ------- --------- --------- --------- ---------- ------------------------------
T_LOB                                                                                                                73737          73737 TABLE               2013-05-17  2013-05-17 18 2013-05-17:18:20:16 VALID   N         N         N                  1

---lob列对应的索引不能直接rename
 
SQL> alter index SYS_IL0000073737C00002$$ rename to index_t_lob_b;
 
alter index SYS_IL0000073737C00002$$ rename to index_t_lob_b
 
ORA-22864: cannot ALTER or DROP LOB indexes
 
SQL>
SQL>
SQL>
SQL> drop table t_lob purge;
 
Table dropped

--如下可以指定lob列产生指定名称的index_name,便于管理,知道此lob index对应哪个表,但是segment_name还是没有变过来
SQL> create table t_lob(a int,b blob) lob(b) store as (index idx_t_lob);
 
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                                                                                SYS_LOB0000073742C00002$$      TBS_NEWLY                      IDX_T_LOB                            8192                   900            NO         YES     NONE    NONE        NONE            YES    NOT APPLICABLE  NO          NO         NO
 
 
---指定lob列的segment_name及index_name
SQL> create table t_lob(a int,b blob) lob(b) store as idx_t_lob ( index idx_t_lob);
 
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           

 

问题：未在11g官方手册create table查到index选项

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9240380/viewspace-761395/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/9240380/viewspace-761395/