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;
No comments:
Post a Comment