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

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.