Mohamed Houri’s Oracle Notes

June 24, 2017

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

Filed under: Oracle,virtual column — hourim @ 3:45 pm

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.

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

Tony's Oracle Tips

Tony Hasler's light hearted approach to learning about Oracle

Richard Foote's Oracle Blog

Focusing Specifically On Oracle Indexes, Database Administration and Some Great Music

Hatem Mahmoud Oracle's blog

Just another Oracle blog : Database topics and techniques

Mohamed Houri’s Oracle Notes

Qui se conçoit bien s’énonce clairement

Oracle Diagnostician

Performance troubleshooting as exact science

Raheel's Blog

Things I have learnt as Oracle DBA

Coskan's Approach to Oracle

What I learned about Oracle

So Many Oracle Manuals, So Little Time

“Books to the ceiling, Books to the sky, My pile of books is a mile high. How I love them! How I need them! I'll have a long beard by the time I read them”—Lobel, Arnold. Whiskers and Rhymes. William Morrow & Co, 1988.

EU Careers info

Your career in the European Union

Carlos Sierra's Tools and Tips

Tools and Tips for Oracle Performance and SQL Tuning

Oracle Scratchpad

Just another Oracle weblog

OraStory

Dominic Brooks on Oracle Performance, Tuning, Data Quality & Sensible Design ... (Now with added Sets Appeal)

%d bloggers like this: