はじめに
SQL Developer を使用して、Oracleデータベースの特定のカラムを更新してコミットしたところ、以下のエラーが発生しました。
ORA-01732: このビューはデータ操作が無効です
エラーメッセージの通り、このエラーコードは更新不可なビューであることを示しています。
ビューであるということは、UPDATE / INSERT / DELETE が許可されません。
では、「このビューの参照元テーブルから更新すればいい」と考えて、テーブルのカラムを変更しましたが、まだ変更前の値が出力されました。
今回はこの問題の対処法をまとめます。
問題の洗い出し
まずは時系列を追って問題の洗い出しを行います。
- そもそもこのオブジェクトは何のビューなのか
- SQL Developerでビューの種類を確認できるのか
問題の特定
オブジェクトタイプをSQLで確認
まずは本当にビューなのか確認します。
SELECT object_type
FROM user_objects
WHERE object_name = 'ビュー名';
--- 結果 ---
OBJECT_TYPE
-------------------
MATERIALIZED VIEW
通常のビューではなく、マテリアライズドビュー（以降、マテビューと省略）であることが判明しました。
マテビューは「実体コピー」であり、元テーブルのデータをスナップショットとして保持しています。
マテビューの定義を確認
SELECT query
FROM user_mviews
WHERE mview_name = 'ビュー名';
--- 結果 ---
SELECT
A.ID,
B.STATUS
FROM
USER_TABLE A
JOIN STATUS_TABLE B ON A.ID = B.ID
カラム単位で参照元テーブルを逆引き
SELECT DISTINCT table_name
FROM user_tab_columns
WHERE column_name = '問題のカラム名';
--- 結果 ---
TABLE_NAME
-------------------
USER_TABLE
対処法
テーブルを特定したので、再度テーブルのカラムを更新しましたが、マテビューにはまだ反映されていませんでした。
マテリアライズドビューは変更前のスナップショットを保持しており、REFRESH（再構築）されない限り、表示は変わりません
マテビューのリフレッシュ設定を確認
SELECT
refresh_mode,
refresh_method,
last_refresh_date
FROM user_mviews
WHERE mview_name = 'マテビュー名';
--- 結果 ---
REFRESH_MODE REFRESH_METHOD LAST_REFRESH_DATE
------------ -------------- -----------------
DEMAND COMPLETE YYYY-MM-DD hh:mm:ss
リフレッシュモードがDEMAND表示のため手動でリフレッシュする必要があります。
| 項目 | 値 | 意味 |
|---|---|---|
| REFRESH_MODE | DEMAND | 手動でリフレッシュ |
| REFRESH_METHOD | COMPLETE | 全件再作成 |
| LAST_REFRESH_DATE | 日時 | 最後にリフレッシュした日時 |
今回は本番環境ではないので、強制的に手動リフレッシュしました。
強制的にマテビューをリフレッシュ（開発環境のみ）
-- 以下のいずれかの方法でリフレッシュ
BEGIN
DBMS_MVIEW.REFRESH('MV_BUNNER_PRODUCT', 'C');
END;
-- EXECを使用
EXEC DBMS_MVIEW.REFRESH('マテビュー名');
EXEC DBMS_MVIEW.REFRESH('マテビュー名', 'C');
-- CALLを使用
CALL DBMS_MVIEW.REFRESH('マテビュー名', 'C');
本番環境での実行は、影響範囲を確認してから行うこと
まとめ
今回は初めてマテビューを更新する場面に遭遇しました。
- ビューへのUPDATEエラーが発生したら、まずオブジェクトタイプを確認する
- マテリアライズドビューは元テーブルを更新しても自動で反映されない場合がある
- リフレッシュ設定
(DEMAND / COMMIT)を確認し、必要に応じて手動リフレッシュを行う