ORA-54033: column to be modified is used in a virtual column expression
Error:
While executing the following command will give us the below error:
alter table customers modify country_id varchar2(30);
ORA-54033: column to be modified is used in a virtual column expression
Cause:
On checking found it has extended stats. we need to delete the Extended stats
Solution
Following example show the deletion and creation method of extended stats which cause the ORA-54033 error.
Example:
-- Create table
Create table test.customers (customer_id number, cust_state_province varchar2(10),country_id varchar2(10));
1. Check the extended stats or Column groups:
SELECT EXTENSION_NAME, EXTENSION FROM USER_STAT_EXTENSIONS WHERE TABLE_NAME='CUSTOMERS';
EXTENSION_NAME EXTENSION ------------------------------ ------------------------------------- SYS_SRE#W#RA34Y#WEGVBN#ETYIOO_ ("CUST_STATE_PROVINCE","COUNTRY_ID")
2. Drop the extended stats.
BEGIN
DBMS_STATS.DROP_EXTENDED_STATS( 'test', 'customers',
'(cust_state_province, country_id)' );
END;
/
3. Alter the column country_id of table customers
alter table customers modify country_id varchar2(30);
4. Create extended Stats or Column groups on Table Customers.
--Create Column Groups Manually
BEGIN
DBMS_STATS.GATHER_TABLE_STATS( 'test','customers',
METHOD_OPT => 'FOR ALL COLUMNS SIZE SKEWONLY ' ||
'FOR COLUMNS SIZE SKEWONLY (cust_state_province,country_id)' );
END;
/