move分区表lob对象

创建分区表和索引 create table t (a number(5), b varchar2(10), c number(5), d clob ) partition by range(a) ( partition p1 values less than (50) tablespace users lob (d) store as (tablespace users), partition p2 values less than (100) tablespace users lob (d) store as (tablespace users), partition p3 values less than (maxvalue) tablespace users lob (d) store as (tablespace users)); insert into t values (11,'a',111,'clob_a'); insert into t values (51,'b',222,'clob_b'); insert into t values (101,'c',333,'clob_c'); commit; create index idx_t_01 on t(a) local; create index idx_t_02 on t(c); 查询表的信息 select partition_name, lob_partition_name, tablespace_name from dba_lob_partitions where table_name = 'T'; PARTITION_NAME       LOB_PARTITION_NAME             TABLESPACE_NAME -------------------- ------------------------------ ------------------------------ P1                   SYS_LOB_P2823                  USERS P2                   SYS_LOB_P2824                  USERS P3                   SYS_LOB_P2825                  USERS select table_name,column_name,lob_name,lob_index_name from dba_part_lobs where table_name='T'; TABLE_NAME           COLUMN_NAME                              LOB_NAME                                 LOB_INDEX_NAME -------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- T                    d                                      SYS_LOB0000134827C00004$$                SYS_IL0000134827C00004$$ SQL>  select INDEX_NAME,STATUS,PARTITION_NAME,tablespace_name from dba_ind_partitions where index_name='SYS_IL0000134827C00004$$'; INDEX_NAME                               STATUS                   PARTITION_NAME       TABLESPACE_NAME ---------------------------------------- ------------------------ -------------------- ------------------------------ SYS_IL0000134827C00004$$                 USABLE                   SYS_IL_P2826         USERS SYS_IL0000134827C00004$$                 USABLE                   SYS_IL_P2827         USERS SYS_IL0000134827C00004$$                 USABLE                   SYS_IL_P2828         USERS MOVE分区和lob对象 alter table t move partition p1 tablespace tbs_ming; alter table t move partition P1 lob(d) store as (tablespace tbs_ming); move后查看lob索引和普通索引 SQL>   select INDEX_NAME,STATUS,PARTITION_NAME,tablespace_name from dba_ind_partitions where index_name='SYS_IL0000134827C00004$$'; SYS_IL0000134827C00004$$                 USABLE                   SYS_IL_P2827         USERS SYS_IL0000134827C00004$$                 USABLE                   SYS_IL_P2828         USERS SYS_IL0000134827C00004$$                 USABLE                   SYS_IL_P2830         TBS_MING SQL> select partition_name, lob_partition_name, tablespace_name from dba_lob_partitions where table_name = 'T'; P1                   SYS_LOB_P2829                  TBS_MING P2                   SYS_LOB_P2824                  USERS P3                   SYS_LOB_P2825                  USERS SQL>  select INDEX_NAME,STATUS,PARTITION_NAME,tablespace_name from dba_ind_partitions where index_name in (select index_name from dba_indexes where table_name='T'); INDEX_NAME                     STATUS                   PARTITION_NAME       TABLESPACE_NAME ------------------------------ ------------------------ -------------------- ------------------------------------------------------------------------------------------ SYS_IL0000134827C00004$$       USABLE                   SYS_IL_P2828         USERS SYS_IL0000134827C00004$$       USABLE                   SYS_IL_P2830         TBS_MING IDX_T_01                       USABLE                   P1                   USERS IDX_T_01                       UNUSABLE                 P2                   USERS IDX_T_01                       USABLE                   P3                   USERS SYS_IL0000134827C00004$$       USABLE                   SYS_IL_P2837         TBS_MING 6 rows selected. SQL> select index_name,status from dba_indexes where table_name='T'; INDEX_NAME                     STATUS ------------------------------ ------------------------ SYS_IL0000134827C00004$$       N/A IDX_T_01                       N/A IDX_T_02                       UNUSABLE 如果增加新的分区，还会使用用户或表默认使用的表空间，需修改分区表的表空间attribute SQL> select def_tablespace_name from DBA_part_tables where table_name='T'; DEF_TABLESPACE_NAME ------------------------------------------------------------------------------------------ SYSTEM SQL> alter table t modify default attributes tablespace tbs_ming; Table altered. SQL> select def_tablespace_name from DBA_part_tables where table_name='T'; DEF_TABLESPACE_NAME ------------------------------------------------------------------------------------------ TBS_MING 总结 1.move partition不会move lob对象，lob对象需要单独move 2.move lob时，lobindex会自动维护，不会失效，但是其他列上的全局索引和分区索引会失效,不管这个索引有没有基于lob列。