Hello,
I have a situation in Oracle Database 12c EE (12.1.0.1) where I am unable to rename a column that contains extended statistics. We are able to rename the column if the extended stats are dropped. My question is why is this step required? This is the first time this has ever happened to us when trying to rename a column.
The column itself is NOT created as virtual. I don't believe we are gathering statistics any differently to 11g.
The following Oracle error was produced when trying to rename the column:
ALTER TABLE Vehicle_Related_Legal_Entity
RENAME COLUMN Status_Cd TO Vehicle_Related_Status_Cd
*
ORA-54032: column to be renamed is used in a virtual column expression
****
The following post is where I found the information regarding the extended statistics:
https://community.oracle.com/thread/3534511?start=0&tstart=0
I followed these steps and was able to rename the column, but there is no information as to how and why this is so.
****
SQL> select table_name, column_name, data_default, VIRTUAL_COLUMN, HIDDEN_COLUMN
2 from user_tab_cols where table_name='VEHICLE_RELATED_LEGAL_ENTITY';
TABLE_NAME
--------------------------------------------------------------------------------------------------------------------------------
COLUMN_NAME
--------------------------------------------------------------------------------------------------------------------------------
DATA_DEFAULT VIR HID
-------------------------------------------------------------------------------- --- ---
VEHICLE_RELATED_LEGAL_ENTITY
SYS_STSYMB_Z$MGI6OP8HM#NUFN$#T
SYS_OP_COMBINED_HASH("VEHICLE_ID","NATURE_OF_RELATION_CD","STATUS_CD") YES YES
SQL> exec DBMS_STATS.DROP_EXTENDED_STATS (ownname=>null, tabname=>'VEHICLE_RELATED_LEGAL_ENTITY', extension=>'("VEHICLE_ID","NATURE_OF_RELATION_CD","STATUS_CD")');
PL/SQL procedure successfully completed.
SQL> select table_name, column_name, data_default, VIRTUAL_COLUMN, HIDDEN_COLUMN
2 from user_tab_cols where table_name='VEHICLE_RELATED_LEGAL_ENTITY';
TABLE_NAME
--------------------------------------------------------------------------------------------------------------------------------
COLUMN_NAME
--------------------------------------------------------------------------------------------------------------------------------
DATA_DEFAULT VIR HID
-------------------------------------------------------------------------------- --- ---
no rows selected
Could someone please tell me how or why the column or statistics got to this state? Is there perhaps something we should be considering to prevent this scenario? It obviously causes havoc when trying to run automated upgrade scripts on schemas...
Thanks in advance.
Ross