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選項