Database Support Blog

  • 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®、Java、MySQL及びNetSuiteは、Oracle、その子会社及び関連会社の米国及びその他の国における登録商標です。
 ・Amazon Web Services、AWS、Powered by AWS ロゴ、[およびかかる資料で使用されるその他の AWS 商標] は、Amazon.com, Inc. またはその関連会社の商標です。
  文中の社名、商品名等は各社の商標または登録商標である場合があります。

関連している記事

  • Oracle Cloud
  • Oracle Database
2024.02.02

OCIにおけるOracle Database 11g R2、12g R1、12g R2の新規プロビジョニング終了とその影響

Oracle Databaseのバージョン11g R2、12g.R1、12g.R2は既にすべてのメーカーサポートが終了しています。OCIのBase Database Serviceでも2024年1月中旬ころから11g R2、12g R1、12g R2での新規プロビジョニングができなくなりました。

  • Oracle Database
  • その他
2023.12.21

【Oracle Database】サポートセンターでの生成AI(Glean)活用

アシストでは全社員にAIアシスタントGleanを導入しました。サポートセンターで2ヶ月間使ってみて感じた効果やメリットをお伝えします。

  • Oracle Database
  • Oracle Cloud
2023.12.15

ライセンスの観点から考えるOracle Cloudのススメ

オラクル社が提供しているクラウドサービス「Oracle Cloud」は、Oracle Databaseライセンス観点でも様々な効果があることはご存じでしょうか? ここでは「ライセンス」に焦点をあて、Oracle Cloudがおススメできるポイントを説明します。

ページの先頭へ戻る