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;
/