CODEWITHSUNDEEP
databaseoracle-databaseoracle11g
user3562926
user3562926

Reputation: 77

Alter table in Oracle 10G - virtual column

I am new with Oracle DB. When I execute in Oracle database version: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production the following code:

ALTER TABLE TEMP_WORKING_TABLE_1 
ADD (CD1 AS ( CAST(MYDATE - 1 AS TIMESTAMP(6)) ) VIRTUAL NOT NULL);

I get successful output: table TEMP_WORKING_TABLE_1 altered.

But when I execute the same code in version: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production I get an error:

ALTER TABLE TEMP_WORKING_TABLE_1 
ADD (CD1 AS ( CAST(MYDATE - 1 AS TIMESTAMP(6)) ) VIRTUAL NOT NULL)
Error SQL: ORA-00902: invalid datatype
00902. 00000 -  "invalid datatype"

Any ideas why I am getting this output? Any workaround for this code?

EDIT:

The code I have is the following:

create table TEMP_WORKING_TABLE_1 compress as select * from myTable partition(P20141023);

ALTER TABLE TEMP_WORKING_TABLE_1 
ADD (MYDATE AS ( CAST(CREATION_DATE - 1 AS TIMESTAMP(6)) ) VIRTUAL NOT NULL);
create table TEMP_WORKING_TABLE_2 compress as 
select COLUMN1,COLUMN2,MYDATE AS CREATION_DATE,COLUMN3
from TEMP_WORKING_TABLE_1;
;
ALTER TABLE myTable EXCHANGE PARTITION P20141022 WITH TABLE TEMP_WORKING_TABLE_2 INCLUDING INDEXES WITHOUT VALIDATION UPDATE GLOBAL INDEXES;

I am trying to exchange partitions. I was using virtual column in Oracle 11G.

Upvotes: 1

Views: 4188

Answers (1)

Jon Heller
Jon Heller

Reputation: 36808

Virtual columns were a new feature of 11gR1, they do not exist in 10g.

Depending on what you are trying to accomplish with a virtual column, some potential workarounds are creating a view, a materialized view, or a function based index.

Upvotes: 2

Related Questions