Database Support Blog

Database Support Blog>仮想列関連エラーORA-54032/ORA-54033の原因と対処方法

  • Oracle Database
2016.08.12

仮想列関連エラーORA-54032/ORA-54033の原因と対処方法

仮想列エラーORA-54032/ORA-54033の対処方法

Oracle Database 12c環境では自動で仮想列が作成されていることがあり、「列定義の変更を行おうとした際にORA-54032やORA-54033が発生した」というお問い合わせをいただくことが増えてきました。今回はその原因と対処方法を紹介します。

仮想列とは

仮想列とは、Oracle Database 11gR1から追加された機能で、テーブルの列に対して計算式を定義し、表示専用の列を作成する機能です。たとえば、COL1とCOL2を乗算したCOL3という列を作成することができます。

列の定義を変更しようとALTER TABLE文を実行した際、対象の列に仮想列が作成されていると「ORA-54032: 名前を変更する列は、仮想列式で使用されています」や「ORA-54033: 仮想列式で使用されている列のデータ型を変更しようとしました」といったエラーが発生します。

SQL> CREATE TABLE test_tab (COL1 NUMBER,COL2 NUMBER,COL3 AS (COL1*COL2));
 
表が作成されました。
 
SQL> INSERT INTO test_tab(COL1,COL2) VALUES(3,4);
  
1行が作成されました。
 
SQL> COMMIT;
  
コミットが完了しました。
  
SQL> SELECT * FROM test_tab;
  
      COL1       COL2       COL3
---------- ---------- ----------
         3          4         12
 
SQL> ALTER TABLE test_tab RENAME COLUMN col1 TO dummy;
ALTER TABLE t RENAME COLUMN col1 TO dummy
                             *
行1でエラーが発生しました。:
ORA-54032: 名前を変更する列は、仮想列式で使用されています

なぜ作成した覚えの無い仮想列のエラーが発生するのか?

前回の記事で、 拡張統計(列グループ統計) について記載しましたが、拡張統計が取得されると仮想列がSYS_XXXXという名前で作成されます。 更にこの列はDBA_TAB_COLS.HIDDEN_COLUMNがYESであり、12cR1からの新機能である 不可視の列 で追加されるため、DESCRIBEや明示的に列名を指定せずにSELECTした場合では表示されません。

SQL> DECLARE
  2      cg_name VARCHAR2(30);
  3  BEGIN
  4      cg_name := DBMS_STATS.CREATE_EXTENDED_STATS(null,'T','(C2,C3)');
  5  END;
  6  /
  
PL/SQLプロシージャが正常に完了しました。
 
SQL> SELECT owner,table_name,column_name,virtual_column FROM dba_tab_cols WHERE table_name='T';
 
OWNER      TABLE_NAME COLUMN_NAME                    VIR
---------- ---------- ------------------------------ ---
TAKASHI    T          C1                             NO
TAKASHI    T          C2                             NO
TAKASHI    T          C3                             NO
TAKASHI    T          SYS_STUOXVZ1C2WGW4DRVBD89VDEO_ YES
 
4行が選択されました。
  
SQL> SELECT * FROM dba_stat_extensions WHERE table_name = 'T';
 
OWNER      TABLE_NAME EXTENSION_NAME                 EXTENSION    CREATO DRO
---------- ---------- ------------------------------ ------------ ------ ---
TAKASHI    T          SYS_STUOXVZ1C2WGW4DRVBD89VDEO_ ("C2","C3")  USER   YES
  
SQL> SELECT owner,column_name, hidden_column FROM dba_tab_cols
  2  WHERE table_name = 'T';
 
OWNER      COLUMN_NAME                    HID
---------- ------------------------------ ---
TAKASHI    C1                             NO
TAKASHI    C2                             NO
TAKASHI    C3                             NO
TAKASHI    SYS_STUOXVZ1C2WGW4DRVBD89VDEO_ YES
  
4行が選択されました。
  
SQL> desc t
 名前                                      NULL?    型
 ----------------------------------------- -------- ------------
 C1                                                 NUMBER
 C2                                                 VARCHAR2(16)
 C3                                                 VARCHAR2(16)


Oracle Database 12cでは、 SQL計画ディレクティブ の機能により、実際の行数とCBOの見積もりが大きく異なり、かつ、拡張統計によってその問題が解決可能であると判断された場合は自動で拡張統計が取得されることがあります。

そのため、ユーザが気付かないうちに仮想列が作成されていることがあり、仮想列が作成されている列に対して変更を行おうとすると、ORA-54032/ORA-54033のような、仮想列に定義された列に対しての操作に関するエラーが発生します。

エラー発生時の対処方法

上記のような仮想列に関するエラーが発生した際は、拡張統計を DBMS_STATS.DROP_EXTENDED_STATS で削除し、改めてエラーが発生した処理を実行します。

SQL> BEGIN
  2   DBMS_STATS.DROP_EXTENDED_STATS(
  3       OWNNAME => 'TAKASHI'
  4      ,TABNAME => 'T'
  5      ,EXTENSION => '(C2,C3)');
  6  END;
  7  /
 
PL/SQLプロシージャが正常に完了しました。
 
SQL> SELECT * FROM dba_stat_extensions WHERE table_name = 'T';
 
レコードが選択されませんでした。
 
SQL> SELECT owner,table_name,column_name,virtual_column FROM dba_tab_cols WHERE table_name='T';
 
OWNER      TABLE_NAME COLUMN_NAME                    VIR
---------- ---------- ------------------------------ ---
TAKASHI    T          C1                             NO
TAKASHI    T          C2                             NO
TAKASHI    T          C3                             NO
 
3行が選択されました。


なお、元々はCBOの見積もりが実際の行数と大きくずれていたことで拡張統計が取得されています。改めて自動で拡張統計が取得される可能性もありますが、対象列に対する処理完了後、 DBMS_STATS.CREATE_EXTENDED_STATS/DBMS_STATS.GATHER_TABLE_STATSで明示的に拡張統計を再取得することをおすすめします。※拡張統計の取得方法は 以前の記事 を参照ください。

まとめ

今回は弊社サポートセンターへのお問い合わせの多いORA-54032/ORA-54033を取り上げましたが、、「ORA-54031: 削除または変更する列は、仮想列式で使用されています」のように列の削除などでもエラーが発生する可能性があります。

列に対する操作を行う際には、事前にDBA_TAB_COLSやDBA_STAT_EXTENSIONSを確認して、仮想列が作成されているか(拡張統計が取得されているか)確認をすることで、時間の限られたメンテナンス作業などをよりスムーズに進めることが出来るかもしれません。

筆者情報

大野 高志

サービス事業部 サポートセンター

2007年にアシスト入社後、Oracle Databaseのサポート業務に従事。現在はサポートの傍ら、未解決のトラブルを一つでも多く減らせるよう、サポートセンターに蓄積されているノウハウを社内外に伝える活動を行っている。

関連している記事

  • Oracle Database
2016.11.16

【Oracle Database】パフォーマンスダウンの原因追求に必要な情報取得サンプルスクリプト

パフォーマンスダウンやCPU/メモリ負荷高騰時には、セッション/プロセス単位で情報を確認する必要があります。原因追求に必要な情報を取得できるサンプルスクリプトを提供します。

  • Oracle Database
2016.10.27

【Oracle Database】うるう秒の対応

Oracle Databaseでのうるう秒(閏秒)の対応方法について解説します。2017年元旦にトラブルとならないよう、準備しましょう。

  • Oracle Database
2016.09.21

再現ケースを簡単に作成!SQLテスト・ケース・ビルダーの使用方法

再現ケースに必要な情報を一括で容易に取得できる「SQLテスト・ケース・ビルダー」の使用方法を紹介します。

アシストサポートセンターのご紹介 Oracle Database研修

ページの先頭へ戻る