CODEWITHSUNDEEP
sqloracle-database
Dinu Nicolae
Dinu Nicolae

Reputation: 1281

How to solve ORA-54019: Virtual column expression cannot be changed because it is a partitioning column?

I have a table that is partitioned by a virtual column and I want to change the behaviour of that virtual column, but apparently this is not possible to do the way I tried it.

ALTER TABLE MY_TABLE
MODIFY VIRTUAL_COLUMN 
GENERATED ALWAYS AS (TO_DATE(SUBSTR(SOME_COLUMN, 4, 8), 'yyyy-mm-dd'))

How can I change that virtual column?

Upvotes: 1

Views: 1126

Answers (1)

Chris Saxon
Chris Saxon

Reputation: 9775

If the virtual column forms part of the partitioning key, you can't change its definition.

If you want to change this so that you can also change the partition scheme, you'll need to:

  • Add a new virtual column with the expression you want
  • Change the partitioning scheme to use the new virtual column

If you're on 18c or higher, you can do this with alter table:

create table t (
  c1 date,
  v1 number as (
    to_number ( to_char ( c1, 'yyyymmdd' ) )
  )
) partition by range ( v1 ) (
  partition p0 values less than ( 20200101 )
);

alter table t
  modify v1 number as (
    to_number ( to_char ( c1, 'yyyymm' ) )
  );

ORA-54019: Virtual column expression cannot be changed because it is a partitioning column
  
alter table t
  add v2 number as (
    to_number ( to_char ( c1, 'yyyymm' ) )
  );
  
alter table t
  modify partition by range ( v2 ) (
    partition p0 values less than ( 202001 )
  ) online;

select column_name 
from   user_part_key_columns
where  name = 'T';

COLUMN_NAME   
V2

For older releases, you'll want to use DBMS_redefinition to minimize any downtime.

Upvotes: 1

Related Questions