Hi Tom,

I have a requirement to create a range partition for an existing table.

Lets say the table name is Orders.

The table has Order_Date column of timestamp datatype and the data is in UTC timezone.

[We get the data in UTC format in a file to load into this table and Business want to keep it as it is so we did not populate in EST timezone when we were loading the data for previous days]

Business want query this table in EST timezone in reports going forward. And we have to create partition for this table. so our proposal is to create a virtual column which should have EST time zone and the data should be in timestamp format(should be same as the actual column ) and then create range partition on this virtual column.

I am trying to create a partitoin table using CTAS... syntax as below

Create table order_part
(Order_id,
Order_date,
Order_desc,
Order_zip,
Order_date_est as (cast(from_tz(Order_date, 'UTC') at time zone 'America/New_York' as timestamp))
)
partition by range (order_date_est) interval (numtodsinterval(1,'day'))
( partition p_before_2000 values less than (to_date('01-jan-2000','dd-mon-yyyy')) tablespace ts1 pctfree 0 )
tablespace ts1 nologging pctfree 0
AS SELECT /*+ PARALLEL(8) */ * FROM orders;

But the above code giving me error "SQL Error: ORA-01773: may not specify column datatypes in this CREATE TABLE"

I know when we use CTAS syntax we should not specify the column datatypes. But not sure how to create the virtual column while creating the partition table.

I can create a partition table with all column names and datatypes and then run a DML to insert all the data as below,

insert into order_part as select * from orders;
But in this case we have 80 millions records in the original table so not sure how much time it will take to do a insert. So please suggest me what is the best way to accomplish my requirement.

Thanks in Advance.

SQL> create table order_part
  2  ( order_id int,
  3    Order_ts timestamp,
  4    Order_date_est timestamp as (cast(from_tz(Order_ts, 'UTC') at time zone 'America/New_York' as timestamp))
  5  );
  Order_date_est timestamp as (cast(from_tz(Order_ts, 'UTC') at time zone 'America/New_York' as timestamp))
                                                             *
ERROR at line 4:
ORA-54002: only pure functions can be specified in a virtual column expression