2 Replies Latest reply: Apr 18, 2014 1:54 PM by Solomon Yakobson RSS

    Moving data between range partitions

    Sri G

      Hello All,

       

      I have a range partitioned table like this.

      how do i update partition value of q2_2014 to a higher number say 25.

      ie i want all the num < 25 to reside in partition q2_2014.

      any help is appreciated.

       

      {

      CREATE TABLE claim

        ( num       NUMBER

        )

      PARTITION BY RANGE (num)

      ( PARTITION q1_2014 VALUES LESS THAN (10)

      , PARTITION q2_2014 VALUES LESS THAN (20)

      , PARTITION q3_2014 VALUES LESS THAN (30)

      , PARTITION q4_2014 VALUES LESS THAN (40)

      );

      insert into claim values (4);

      insert into claim values (11);

      insert into claim values (25);

      }

        • 1. Re: Moving data between range partitions
          rp0428

          Sri G wrote:

           

          Hello All,

           

          I have a range partitioned table like this.

          how do i update partition value of q2_2014 to a higher number say 25.

          ie i want all the num < 25 to reside in partition q2_2014.

          any help is appreciated.

           

          {

          CREATE TABLE claim

            ( num       NUMBER

            )

          PARTITION BY RANGE (num)

          ( PARTITION q1_2014 VALUES LESS THAN (10)

          , PARTITION q2_2014 VALUES LESS THAN (20)

          , PARTITION q3_2014 VALUES LESS THAN (30)

          , PARTITION q4_2014 VALUES LESS THAN (40)

          );

          insert into claim values (4);

          insert into claim values (11);

          insert into claim values (25);

          }

          You don't and you can't. Data for '25' is located in the 'q3' partition.

           

          You need to SPLIT the q3 partition and create a < 25 and a < 30 partition.

           

          Then MERGE the current < 20 and the new < 25 partitions.

           

          See the VLDB and Partitioning doc for examples of SPLIT and MERGE

          http://docs.oracle.com/cd/B28359_01/server.111/b32024/part_admin.htm

          • 2. Re: Moving data between range partitions
            Solomon Yakobson

            SQL> ALTER TABLE claim
              2    SPLIT PARTITION q3_2014
              3      AT(25)
              4      INTO(
              5           PARTITION q2_2014_extension,
              6           PARTITION q3_2014
              7          )
              8  /

            Table altered.

            SQL> ALTER TABLE claim
              2    MERGE PARTITIONS q2_2014,
              3                     q2_2014_extension
              4      INTO PARTITION q2_2014_extension
              5  /

            Table altered.

            SQL> ALTER TABLE claim
              2    RENAME PARTITION q2_2014_extension
              3    TO q2_2014
              4  /

            Table altered.

             

            SY.