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

Posted on 2011-10-17
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 34

    Accepted Solution

    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.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Suggested Solutions

    Title # Comments Views Activity
    Oracle date format issue 36 80
    Parse data from XMLtype data in Oracle SQL 5 57
    Mysql vs Oracle 10 105
    Space Delimited Sql File 4 59
    Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
    This post first appeared at Oracleinaction  ( Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
    This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
    Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

    761 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

    Need Help in Real-Time?

    Connect with top rated Experts

    8 Experts available now in Live!

    Get 1:1 Help Now