Oracle Alter command to rename existing Column errorring

Question

alter table tablename rename column zl_divn_nbr to div_loc_nbr;

Error while executing the above statement. Please help.

SQL Error: ORA-54032: column to be renamed is used in a virtual column expression
54032. 0000 -  "column to be renamed is used in a virtual column expression"
*Cause:    Attempted to rename a column that was used in a virtual column
           expression.
*Action:   Drop the virtual column first or change the virtual column
           expression to eliminate dependency on the column to be renamed

Selfabasement

Answer 1

Run the following SQL query in your database using the table name mentioned in the error message. For example, in the error message shown in this article, the table name is 'tablename'. Note that whilst the table name appears in lower case in the error message, it may be upper case in your DB. This query is case sensitive so if you receive no results, check whether the table name is upper case inside your database.

SELECT COLUMN_NAME, DATA_DEFAULT, HIDDEN_COLUMN FROM USER_TAB_COLS WHERE TABLE_NAME = 'tablename';

Before proceeding, make sure the Bitbucket Server process is not running. If Extended Statistics has been enabled, contact your database administrator to have them drop the Extended Statistics metadata from the table, and proceed with your upgrade. If you wish to enable Extended Statistics again after the upgrade you may do so, however be aware that you may need to repeat this process again for subsequent upgrades otherwise you risk running into this issue again.

Removing columns created by Extended Statistics requires using an in-build stored procedure,

DBMS_STATS.DROP_EXTENDED_STATS().

Usage of this stored procedure is covered further in ORA-54033 and the Hidden Virtual Column Mystery, and looks similar to the following:

EXEC DBMS_STATS.DROP_EXTENDED_STATS(ownname=>'<YOUR_DB_USERNAME>', tabname=>'tablename', extension=>'("PR_ROLE", "USER_ID", "PR_APPROVED")')

References Database Upgrade Eror: column to be rename

Thanks.

Effieeffigy

Answer 2

Probably, you have such a table :

CREATE TABLE tablename(
  id               NUMBER,
  zl_divn_nbr      NUMBER,
  zl_divn_percent  NUMBER GENERATED ALWAYS AS (ROUND(zl_divn_nbr/100,2)) VIRTUAL
);

where zl_divn_nbr column is used for a computation for virtual(zl_divn_percent) column.

To rename zl_divn_nbr, all referenced virtual columns to this column should be removed, and may be created later.

The syntax for defining a virtual column is this :

column_name [datatype] [GENERATED ALWAYS] AS (expression) [VIRTUAL]

Since version 11 R1, we have this property.

Student

Answer 3

ALTER TABLE rename column to

In the case of tables with virtual or 'group extension columns' the above statement returns an error before Oracle 12cR2. For Oracle 12cR2 or newer versions the above statement runs fine cause 'renaming column' command is decoupled from the group extension aspect.

Burkhart