请教一个ORACLE的index 分区的问题ORA-01502: index 'TACFM.PK_ALARM_ARCHIVE' or partition of such index is in unusable state我已将 PK_ALARM_ARCHIVE重建了:SQL>alter index PK_ALARM_ARCHIVE rebuild;结果第二天0点(昨天转今天时)仍然报错是为何?分区已存在:
partition P20100511
tablespace INS_DATA
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
),
partition P20100512
tablespace INS_DATA
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
),
partition P20100513
tablespace INS_DATA
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
),
partition P20100511
tablespace INS_DATA
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
),
partition P20100512
tablespace INS_DATA
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
),
partition P20100513
tablespace INS_DATA
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
),
把建表和建索引贴上来,还有0点有没有对表做ddl操作。
create table ARCHIVE
(
ID NUMBER(16) not null,
SOURCE_ID VARCHAR2(64) not null,
USER_LABEL VARCHAR2(128) not null,
ALARM_ID VARCHAR2(64),
PRIORITY NUMBER(3) not null,
SUMMARY VARCHAR2(256) not null,
DETAILS VARCHAR2(4000),
MEDIATION_TIME DATE not null
)
partition by (MEDIATION_TIME)
(
partition P20100313
tablespace INMS_DATA
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
),
........
partition P20100511
tablespace INMS_DATA
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
),
partition P20100512
tablespace INMS_DATA
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
),
partition P20100513
tablespace INMS_DATA
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
),
........
partition P20100522
tablespace INMS_DATA
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
)
)
;
alter table ARCHIVE
add constraint PK_ARCHIVE primary key (ID, SOURCE_ID, USER_LABEL, SUMMARY, MEDIATION_TIME)
using index
tablespace INMS_DATA
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
SELECT t.partition_name FROM user_tab_partitions t
WHERE t.table_name=upper('表名称');找到有那些分区,可以自己写个过程也可,
然后使用语法:
ALTER INDEX <索引名称> REBUILD PARTITION <分区名称>;
如果是全局索引是可以重新编译的。和你语法一致:
ALTER INDEX <索引名称> REBUILD;