きり丸の技術日記

技術検証したり、資格等をここに残していきます。

トップ > DB > SQL*Loaderを使ったらアプリ実行時にORA-00001, ORA-01502が発生した

SQL*Loaderを使ったらアプリ実行時にORA-00001, ORA-01502が発生した

DB 技術

当たり前といえば当たり前の話ですが、知らなかったのでブログに残します。

環境

  • Oracle Database
    • 11g
      • Docker環境で使用していました

事象

Oracle DatabaseにはSQL*Loaderという標準でついていて、高速でCSV等の外部ファイルからDatabaseにImportできるCLIがあります。SQL*Loaderを使用した後に、アプリケーションで実行すると実行するAPIによっては次の2つのエラーが発生しました。

  • ORA-00001: 一意制約に反しています
  • ORA-01502: index 'XXXXXX' or partition of such index is in unusable state

原因

SQL*Loaderのダイレクト・パス・ロードで一意制約違反のデータをImportしていたから。

原因詳細

ダイレクト・パス・ロードとは、制約を無視してImportできる機能のことです。制約を無視できる分、高速で処理をすることができます。

原因としては、制約を無視してImportしたデータが一意制約違反を起こしていました。なお、ダイレクト・パス・ロードでの処理後に何もエラーが発生していませんでした。

その結果としてデータをInsertしようとすると、Insertするデータ自体は問題ないものの、既存のデータが一意制約違反を起こして「ORA-00001」を発生しました。

「ORA-01502」に関しては、一意制約違反を無視したデータを入れているせいで、本来有効（USABLE）なはずのIndexを有効化できずに無効（UNUSABLE）になっていました。無効なIndexを使って検索をしようとした結果、「ORA-01502」が発生していました。

整理すると、原因は同じだったものの、実行時の処理によって異なるエラーが発生していました。

  • SELECT
    • ORA-01502
  • UPDATE, INSERT等の更新
    • ORA-00001

対策

複数あると思います。個人的には最後がオススメです。

  1. 制約を有効な従来型パス・ロードでImportする
  2. ダイレクト・パス・ロードで一意制約違反のデータをImportしないようにする
  3. ダイレクト・パス・ロードでImport後に、無効になっているIndexが存在しないか確認する

無効になっているIndexは次のSQLで確認することができるので、ダイレクト・パス・ロードでのImport後に使用すると実行時ではなく処理時に確認できるのでオススメです。

SELECT index_name, status 
FROM all_indexes 
WHERE status = 'UNUSABLE'

終わりに

SQL*Loaderの操作時にエラーが発生していたらすぐに気づけましたが、APIの実行時にエラーになったので調査難易度が高かったです。

「ORA-01502」でエラー検索すると、「テーブルを再作成したことでIndexが無効になった」「パーティションを移動させたことでIndexが無効になった」等々の情報しかヒットしなかったので、真因にたどり着くのに時間がかかりました。

検証用としてOracleをDockerで運用していることもあって、「Dockerだと4時間経過するとテーブルやパーティションがリセットされる…？」等の違うところにアタリを付けていたので苦労しました。結果としてはDockerは何も悪くなかったです。

こうやって、障害はブログにお焚き上げして、未来の自分のために検索に引っかかるようにしたいです。

この記事がお役に立ちましたら、各種SNSでのシェアや、今後も情報発信しますのでフォローよろしくお願いします。

参考