suggestions for 10g partitioning when 11g is soon to come

I would like my 11g table to look like this...

CREATE TABLE mytable  (
    id number NOT NULL enable,
    transaction_dtm timestamp default systimestamp not null enable,
    part_key as (mod(extract(month from transaction_dtm), 6)) )
partition by range (part_key)
  (Partition acl_aud_mes_part_1 values less than (1),
  partition ACL_AUD_MES_PART_2 values less than (2),
  Partition acl_aud_mes_part_3 values less than (3),
  Partition acl_aud_mes_part_4 values less than (4),
  Partition acl_aud_mes_part_5 values less than (5),
  Partition acl_aud_mes_part_6 values less than (6)
  )
 ;

but we are on 10g beyond the first production deployment of this table.

Any suggestions for a table deployment to 10g followed by an alter in 11g that will bring it around to look like this?

I can't partition on a virtual column in 10g. I'm guessing I would create a real column in 10g and partition on that, but how would I alter the table once 11g is available to switch it to be a virtual column? would I have to rename the table, create the table I want, insert all the data into the new table, drop the old table?
gswitzAsked:
Who is Participating?
 
Mark GeerlingsDatabase AdministratorCommented:
Based on my experience with partitioned tables in Oracle10 and 11 and on my understanding of Oracle's documentation, you are correct.  That is: you could create a real column in Oracle10 to use as the partitioning column (that you may need a "before insert ... for each row" trigger to populate for you) then after you upgrade to Oracle11, you would have to: "rename the [original] table, create the table I want, insert all the data into the new table, drop the old table".  You would also have to copy any/all constraints. indexes, default values and triggers (other than the one to populate your now virtual column) from the original table to the new table.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.