Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2014
    Posts
    2

    Unanswered: How to Partition existing Oracle 11g Table

    Hello All,

    I need help from all of you. I need to partition few existing Oracle tables by Quarterly date range. I am doing this in our Dev environment which is not being used right now so please let me know the steps to partition an existing table in Oracle.

    For Example - I am attempting to partition a Table T1 with existing data.

    Table T1 is as follows:

    COLUMN DATATYPE
    -----------------
    COLUMN1 NUMBER PK
    COLUMN2 NUMBER
    COLUMN3 NUMBER
    CHANGED_DT DATE

    I am using this approach:

    1- ALTER TABLE T1 RENAME TO T1_TEMP

    2- CREATE TABLE T1 (
    COLUMN1 NUMBER,
    COLUMN2 NUMBER,
    COLUMN3 NUMBER,
    CHANGED_DT DATE,
    CONSTRAINT "PK1_PK" PRIMARY KEY ("COLUMN1") )

    PARTITION BY RANGE (changed_dt)
    ( PARTITION "Q1_2013" VALUES LESS THAN (TO_DATE(' 2013-04-01 00:00:00', 'SYYYY-MM-DD HH24:MIS', 'NLS_CALENDAR=GREGORIAN')) TABLESPACE "USERS" ,
    PARTITION "Q2_2013" VALUES LESS THAN (TO_DATE(' 2013-07-01 00:00:00', 'SYYYY-MM-DD HH24:MIS', 'NLS_CALENDAR=GREGORIAN')) TABLESPACE "USERS" ,
    PARTITION "Q3_2013" VALUES LESS THAN (TO_DATE(' 2013-10-01 00:00:00', 'SYYYY-MM-DD HH24:MIS', 'NLS_CALENDAR=GREGORIAN')) TABLESPACE "USERS" ,
    PARTITION "Q4_2013" VALUES LESS THAN (TO_DATE(' 2014-01-01 00:00:00', 'SYYYY-MM-DD HH24:MIS', 'NLS_CALENDAR=GREGORIAN')) TABLESPACE "USERS" )

    ENABLE ROW MOVEMENT;

    3- INSERT INTO T1 SELECT * FROM T1_TEMP


    Please let me know if you think above approach is wrong approach. I also tried use this approach on one table and was able to create the new partitioned table with data in it. But If I do Select count(*) then number of rows matches with this non partitioned table but when I query the number of rows in each partition and then add them, then total number of rows are greater than Select Count(*) from same table. Please let me know if this is correct behavior.

    Thanks a lot in Advance !!!

    Regards,
    AG

  2. #2
    Join Date
    Jul 2014
    Posts
    2

    How to Partition existing Oracle 11g Table

    Hello All,

    I need help from all of you. I need to partition few existing Oracle tables by Quarterly date range. I am doing this in our Dev environment which is not being used right now so please let me know the steps to partition an existing table in Oracle.

    For Example - I am attempting to partition a Table T1 with existing data.

    Table T1 is as follows:

    COLUMN DATATYPE
    -----------------
    COLUMN1 NUMBER PK
    COLUMN2 NUMBER
    COLUMN3 NUMBER
    CHANGED_DT DATE

    I am using this approach:

    1- ALTER TABLE T1 RENAME TO T1_TEMP

    2- CREATE TABLE T1 (
    COLUMN1 NUMBER,
    COLUMN2 NUMBER,
    COLUMN3 NUMBER,
    CHANGED_DT DATE,
    CONSTRAINT "PK1_PK" PRIMARY KEY ("COLUMN1") )

    PARTITION BY RANGE (changed_dt)
    ( PARTITION "Q1_2013" VALUES LESS THAN (TO_DATE(' 2013-04-01 00:00:00', 'SYYYY-MM-DD HH24:MIS', 'NLS_CALENDAR=GREGORIAN')) TABLESPACE "USERS" ,
    PARTITION "Q2_2013" VALUES LESS THAN (TO_DATE(' 2013-07-01 00:00:00', 'SYYYY-MM-DD HH24:MIS', 'NLS_CALENDAR=GREGORIAN')) TABLESPACE "USERS" ,
    PARTITION "Q3_2013" VALUES LESS THAN (TO_DATE(' 2013-10-01 00:00:00', 'SYYYY-MM-DD HH24:MIS', 'NLS_CALENDAR=GREGORIAN')) TABLESPACE "USERS" ,
    PARTITION "Q4_2013" VALUES LESS THAN (TO_DATE(' 2014-01-01 00:00:00', 'SYYYY-MM-DD HH24:MIS', 'NLS_CALENDAR=GREGORIAN')) TABLESPACE "USERS" )

    ENABLE ROW MOVEMENT;

    3- INSERT INTO T1 SELECT * FROM T1_TEMP


    Please let me know if you think above approach is wrong approach. I also tried use this approach on one table and was able to create the new partitioned table with data in it. But If I do Select count(*) then number of rows matches with this non partitioned table but when I query the number of rows in each partition and then add them, then total number of rows are greater than Select Count(*) from same table. Please let me know if this is correct behavior.

    Thanks a lot in Advance !!!

    Regards,

  3. #3
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool

    Quote Originally Posted by amit.sonu38 View Post
    Hello All,

    I need help from all of you. I need to partition few existing Oracle tables . . .
    . . .
    Thanks a lot in Advance !!!

    Regards,
    Use DBMS_REDEFINITION() Package.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  4. #4
    Join Date
    May 2014
    Posts
    24
    Is there a shortcut for this? The code for me seems difficult.

  5. #5
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    do nothing & all will be OK
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •