ORA-54032 : column to be renamed is used in a virtual column expression

This is a simple note re-explaining what this 12cR1 ORA-54032 error is as it kicked in again a couple of days ago at one of my customer sites and showing that it has definitely been solved in 12cR2.

Here’s a how to reproduce it at will:

12cR1

SQL> select banner from v$version where rownum=1;

BANNER
----------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production

SQL> create table t1(x number, y number, z number);

Table created.

SQL> alter table t1 rename column x to x_bis;

Table altered.

As you can see we renamed column x to x_bis without any issue. However if we create a column group extension out of (x_bis, y) columns we will not be able to rename neither x_bis nor y column as shown below:

SQL> SELECT
          dbms_stats.create_extended_stats
           (ownname   => user
           ,tabname   => 't1'
          ,extension => '(x_bis,y)'
           ) ext
    FROM dual;

-------------------------------
SYS_STU3L40_B_DWBMIA8PMJRGR$S0

SQL> alter table t1 rename column x_bis to x;
alter table t1 rename column x_bis to x
                             *
ERROR at line 1:
ORA-54032: column to be renamed is used in a virtual column expression

That’s exactly what happened to my client with the sole difference that the client has never explicitly created any virtual column hence its great stupefaction. I had to explain him that this is because Oracle has created, behind the scenes, an extended column group, which is nothing else than a virtual column. This creation is very probably a response to a SQL Plan Directive request.

And indeed, the column group extension I have manually created above is a virtual column created out of a combination of x_bis and y column as clearly shown by the default value of this virtual column:

SQL> select 
         column_name
        ,data_default
        ,data_length
     from
       user_tab_cols
     where
       table_name = 'T1'
     and hidden_column = 'YES';

COLUMN_NAME                    DATA_DEFAULT                      DATA_LENGTH
------------------------------ --------------------------------- -----------
SYS_STU3L40_B_DWBMIA8PMJRGR$S0 SYS_OP_COMBINED_HASH("X_BIS","Y")   22

We are unable to rename the x_bis column to x because x_bis is used in the definition of the above virtual column. But that was before the arrival of 12cR2.

12cR2

Execute the same experiment in 12cR2 and you will realize that things have changed as the followings demonstrate:

SQL> select banner from v$version where rownum=1;
BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> create table t1(x number, y number, z number);

Table T1 created.

SQL> alter table t1 rename column x to x_bis;

Table T1 altered.

  SELECT
      dbms_stats.create_extended_stats
       (ownname   => user
       ,tabname   => 't1'
       ,extension => '(x_bis,y)'
       ) ext
  FROM dual;

EXT
-------------------------------
SYS_STU3L40_B_DWBMIA8PMJRGR$S0

SQL> alter table t1 rename column x_bis to x;

Table T1 altered.

As of 12cR2 we are not anymore annoyed by the error when altering a table column used in a virtual column expression. This is because Oracle will take the alter column from the table level and extend it to the virtual column where it can be found as shown below:

SQL> select 
         column_name
        ,data_default
        ,data_length
     from
       user_tab_cols
     where
       table_name = 'T1'
     and hidden_column = 'YES';

COLUMN_NAME                    DATA_DEFAULT                   DATA_LENGTH
------------------------------ ------------------------------ -----------
SYS_STU3L40_B_DWBMIA8PMJRGR$S0 SYS_OP_COMBINED_HASH("X","Y")           22

Spot how, in the data_default value of the generated column group extension, the new modified name of the column has been changed as well.

SUMMARY

Again you know that an automatic extended column group extension, which might be created on behalf of you, generates a border corner where you are not anymore able to rename any of the columns that composed the extend virtual column group. Fortunately as of Oracle 12cR2 Oracle extends the column renaming directly to the extended virtual column and doesn’t raise the ORA-054023 anymore.