ORA-01502: index '%s' or partition of such index is in unusabl

ORA-01502: index '%s' or partition of such index is in unusabl

위 에러가 발생할 시 partition table의 key가 unusabl 상태가 된 것을 확인할 수 있습니다.

partition table에 딸린 key가 unsabl 된 상태 인데요.
partition table에 생성 된 global key에 모두 영향을 끼쳐 그렇습니다.
partition table에서 truncate partition table과 drop partition table 같은 작업이 빈번한 경우 global key를 local key로 변경하는 것을 권장 합니다.

---

ORA-01502 재현

SQL> create table p1(x int, xx int, xxx int)
partition by range (x)
(
    partition p1_1_100 values less than (100),
    partition p1_101_200 values less than (200),
    partition p1_201_300 values less than (300),
    partition p1_301_400 values less than (400  2    3    4    5    6    7  ),
    partition p1_401_500 values less than (500),
    partition p1_maxvalue values less than (maxvalue)
);  8    9   10  

Table created.

SQL> insert into p1(x) select level from dual connect by level <=500;

500 rows created.

SQL> commit;

Commit complete.

SQL> alter table p1 add constraint pk_p1_x primary key (x);

Table altered.

SQL> alter table p1 truncate partition p1_1_100;

Table truncated.

SQL> insert into p1(x) select level from dual connect by level <=1;
insert into p1(x) select level from dual connect by level <=1
*
ERROR at line 1:
ORA-01502: index 'SYS.PK_P1_X' or partition of such index is in unusable state

ORA-01502 조치

SQL> drop table p1;

Table dropped.

SQL> create table p1(x int, xx int, xxx int)
partition by range (x)
(
    partition p1_1_100 values less than (100),
    partition p1_101_200 values less than (200),
    partition p1_201_300 values less than (300),
    partition p1_301_400 values less than (400),
    partition p1_401_500 values less than (500),
    partition p1_maxvalue values less than (maxvalue)
);  2    3    4    5    6    7    8    9   10  

Table created.

SQL> insert into p1(x) select level from dual connect by level <=500;

500 rows created.

SQL> commit;

Commit complete.

SQL> create unique index pk_p1_x on p1(x) local;

Index created.

SQL> alter table p1 add constraint pk_p1_x primary key (x);

Table altered.

SQL> alter table p1 truncate partition p1_1_100;

Table truncated.

SQL> insert into p1(x) select level from dual connect by level <=1;

1 row created.

---

---