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

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.