[Oracle] 再作成したテーブルにアクセスするとORA-01502エラーが発生する。 | Archive Redo Blog

Archive Redo Blog

DBエンジニアのあれこれ備忘録

テーブルを再作成するのにALTER TABLE ~ MOVE TABLESPACE ~という文を使いますが、このようにしてテーブルを再作成した後、そのテーブルにアクセスしようとするとORA-01502エラーが発生することがあります。

例えば、

alter table emp move tablespace users;

としてscottのempテーブルを再作成した後、

select * from emp where empno = 7369;

とプライマリーキーを条件にempテーブルを検索すると、

ORA-01502: 索引'SCOTT.PK_EMP'またはそのパーティションが使用不可の状態です。

というエラーが出ます。

USER_INDEXESを検索してインデックスの状態を確認してみると確かに'UNUSABLE'となっています。

select index_name, status from user_indexes;

INDEX_NAME                                                   STATUS
------------------------------------------------------------ ----------------
PK_DEPT                                                      VALID
PK_EMP                                                       UNUSABLE

どうやらテーブルを再作成するとインデックスが使用不可になるようです。

これを使用可能('VALID')に戻すにはインデックスを再作成すればいい、ということで、以下のようにインデックスを再作成し、

alter index pk_emp rebuild;

再度状態を確認してみると確かに'VALID'となります。

select index_name, status from user_indexes;

INDEX_NAME                                                   STATUS
------------------------------------------------------------ ----------------
PK_DEPT                                                      VALID
PK_EMP                                                       VALID

しかし、テーブルを再作成してインデックスが使用不可になるのはわかるとしても、なぜ、気を利かせて一緒に再作成してくれないのでしょう?

あるいは、インデックスが使用不可だったらそのインデックスは使わないというふうにオプティマイザが判断してくれないのでしょう?

テーブルを再作成したときは何もエラーが出ず、後でアクセスしたときにエラーが出て初めて気づかされるなんて間抜けすぎます...

せめて、同時にインデックスも再作成するオプションくらいあってもいいと思うのですが。

自己防衛手段として、テーブルを再作成した後でそのテーブルに作成されているインデックスを探し出して再作成するというようなプロシージャを作成してそれを使うようにしていますが、何とかしてほしいものです...