[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


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

Posted on 2011-10-17
Medium Priority
Last Modified: 2012-05-12
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?
Question by:gswitz
1 Comment
LVL 35

Accepted Solution

Mark Geerlings earned 2000 total points
ID: 36981284
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.

Featured Post

Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Suggested Courses

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question