A workaround for ORA-54033: column to be modified is used in a virtual column expression

Applicable for Oracle 12.1.0.2.

My customer reports that an attempt to execute a simple DDL statement failed:

ALTER TABLE mytable
MODIFY (COL2 DATE );

The error thrown was: ORA-54033: column to be modified is used in a virtual column expression.

Quite correctly, a look at the table revealed a virtual column:

set lines 200
col owner format a15
col table_name format a30
col column_name format a30
col data_default format a60

select owner,table_name,column_name, data_Default
from dba_tab_cols
where table_name='MYTABLE'
and hidden_column='YES';

Output:

OWNER           TABLE_NAME      COLUMN_NAME                    DATA_DEFAULT                                                                    
--------------- --------------- ------------------------------ -----------------------------------------------
SCOTT           MYTABLE         SYS_STSC13O20ML6_5OD25YOF16STK SYS_OP_COMBINED_HASH("COL1","COL2","COL3","COL4")             
1 row selected.

You can also query the DBA_STAT_EXTENSION to get similar information:

set lines 200
col extension_name format a30
col extension format a50

SELECT EXTENSION_NAME, EXTENSION,creator,droppable
FROM DBA_STAT_EXTENSIONS
WHERE TABLE_NAME='MYTABLE';

Output:

EXTENSION_NAME                 EXTENSION                      CREATOR DROPPABLE
------------------------------ ------------------------------ ------- ---------
SYS_STSC13O20ML6_5OD25YOF16STK ("COL1","COL2","COL3","COL4")  SYSTEM  YES      
1 row selected.

So in order to modify the existing column, the extended statistics will have to be dropped and recreated:

set lines 200
set serveroutput on
set timing on

BEGIN
  dbms_stats.drop_extended_stats('SCOTT', 'MYTABLE', '(COL1,COL2,COL3,COL4)');
END;
/

ALTER TABLE mytable
MODIFY (COL2 DATE );

select dbms_stats.create_extended_stats('SCOTT', 'MYTABLE', '(COL1,COL2,COL3,COL4)') 
from dual;