oracle添加索引指定表空间,分区表add partition的时候如何指定local索引的存储表空间...

oracle版本是11.2.3.

应用环境是这样：

数据表空间sing_dt01----sing_dt02

create tablespace sing_dt01 datafile '/home/oracle/oradata/**z/sing_dt01_01.dbf' size 4096m autoextend on next 100m;

create tablespace sing_dt02 datafile '/home/oracle/oradata/**z/sing_dt02_01.dbf' size 4096m autoextend on next 100m;

create tablespace sing_dt03 datafile '/home/oracle/oradata/**z/sing_dt03_01.dbf' size 4096m autoextend on next 100m;

create tablespace sing_dt04 datafile '/home/oracle/oradata/**z/sing_dt04_01.dbf' size 4096m autoextend on next 100m;

create tablespace sing_dt05 datafile '/home/oracle/oradata/**z/sing_dt05_01.dbf' size 4096m autoextend on next 100m;

create tablespace sing_dt06 datafile '/home/oracle/oradata/**z/sing_dt06_01.dbf' size 4096m autoextend on next 100m;

create tablespace sing_dt07 datafile '/home/oracle/oradata/**z/sing_dt07_01.dbf' size 4096m autoextend on next 100m;

索引表空间sing_idx01----sing_idx07

create tablespace sing_idx01 datafile '/home/oracle/oradata/**z/sing_idx01_01.dbf' size 1024m autoextend on next 100m;

create tablespace sing_idx02 datafile '/home/oracle/oradata/**z/sing_idx02_01.dbf' size 1024m autoextend on next 100m;

create tablespace sing_idx03 datafile '/home/oracle/oradata/**z/sing_idx03_01.dbf' size 1024m autoextend on next 100m;

create tablespace sing_idx04 datafile '/home/oracle/oradata/**z/sing_idx04_01.dbf' size 1024m autoextend on next 100m;

create tablespace sing_idx05 datafile '/home/oracle/oradata/**z/sing_idx05_01.dbf' size 1024m autoextend on next 100m;

create tablespace sing_idx06 datafile '/home/oracle/oradata/**z/sing_idx06_01.dbf' size 1024m autoextend on next 100m;

create tablespace sing_idx07 datafile '/home/oracle/oradata/**z/sing_idx07_01.dbf' size 1024m autoextend on next 100m;

创建分区表

create table FLR_SING_ASAC_AMT

(

SING_ASAC_AMT_ID    INTEGER not null,

XXXX             CHAR(11) not null,

XXXX        CHAR(20) not null,

XXXX           CHAR(14),

XXXX             CHAR(2) not null,

XXXX         INTEGER not null,

XXXX       VARCHAR2(32),

XXXX  CHAR(11),

XXXX           NUMBER(20,4),

XXXX             CHAR(4),

XXXX        CHAR(3),

XXXX        CHAR(8),

CLR_DATE  CHAR(8) not null,

XXXX      CHAR(14)

)

partition by range(clr_date)

(

partition p1 values less than ('20120702') tablespace sing_dt01,

partition p2 values less than ('20120703') tablespace sing_dt02,

partition p3 values less than ('20120704') tablespace sing_dt03,

partition p4 values less than ('20120705') tablespace sing_dt04,

partition p5 values less than ('20120706') tablespace sing_dt05,

partition p6 values less than ('20120707') tablespace sing_dt06,

partition p7 values less than ('20120708') tablespace sing_dt07

);

创建对应的索引--local

create unique index PK_FLR_SING_ASAC_AMT on FLR_SING_ASAC_AMT(clr_date,SING_ASAC_AMT_ID) local

(

partition p1 tablespace sing_idx01,

partition p2 tablespace sing_idx02,

partition p3 tablespace sing_idx03,

partition p4 tablespace sing_idx04,

partition p5 tablespace sing_idx05,

partition p6 tablespace sing_idx06,

partition p7 tablespace sing_idx07

);

当20120708的时候需要将分区P1和对应的索引分区drop掉 然后，将新的partition重新使用表空间sing_dt01。

alter table FLR_SING_ASAC_AMT add partition p8 values less than ('20120709') tablespace sing_dt01;

问题是这个时候local索引的存储位置是表空间sing_dt01，能不能再add partition的时候就指定对应local索引分区的tablespace。