3 Replies Latest reply: Apr 4, 2014 12:53 PM by User503581-OC RSS

    Partition high_value.

    User503581-OC


      Hello Guys,

      I have a table partitioned by range with a high_value of the last day of each year 'YYYY1231'. As a result of this, December data is going to my next partition, 17partitions in all.

       

      I need to change or alter the high_value of my partitions and make it the first day of the year. So that my high_value would be something like

      '20040101' for all 2003 data and not '20031231' which is what I have above.

       

      Is it possible to run an alter partition change........ or any other way to change or alter the high_value for the partition.

        • 1. Re: Partition high_value.
          Krishna-Oracle

          Hi,

          I understand you are planning to change high value of particular partition. We can follow below method

           

          1)create an empty table just like the partitioned table down to the indexes.

           

           

          SQL> create table dummy as select * from <table_name> where 1=0

           

           

          2)swap the empty table with partition - so the partition becomes a table, the table an empty partition.

           

           

          SQL> alter table <table_name>

              exchange partition <partition name>

              with table dummy

              including indexes

              without validation

           

           

          3)drop the partition

           

           

          SQL> alter table <table_name> drop partition <partition_name>;

           

           

          4) add a new partition with a higher upper bound

           

           

          SQL> alter table <table_name> add partition <partition_name> values less than ('20040101');

           

           

          5) swap the empty partition with the full table

          SQL> alter table <table_name>

          exchange partition <partition_name>

          with table dummy

          including indexes

          without validation;

           

           

           

          Thanks,

          Krishna

          • 2. Re: Partition high_value.
            User503581-OC

            Hello,
            Here is my plan of attack tell me what you think.
            The name of my table is RGA_ACTIVITY3 the partitions are
            T1_PARTS through T15_parts. Per your advice, I will create a table called dummy as my table.

            1)create an empty table just like the partitioned table down to the indexes.

            CRAETE TABLE DUMMY AS SELECT * FROM RGA_ACTIVITY3 WHERE 1=0;

             

            2)swap the empty table with partition - so the partition becomes a table, the table an empty partition.

            ALTER TABLE TRA_ACTIVITY3 EXCHANGE PARTITION T1_PARTS WITH DUMMY INCLUDING INDEXES WITHOUT VALIDATION;

             

            3)drop the partition

            ALTER TABLE RGA_ACTIVITY DROP PARTITION T1_PARTS;

             

            4) add a new partition with a higher upper bound

            ALTER TABLE RGA_ACTIVITY3 ADD PARTITION Y1_PARTS VALUES LESS THAN ('02250101);

             

            5) swap the empty partition with the full table

            ALTER TABLE RGA_ACTIVITY EXCHANGE PARTITION T1_PARTS WITH DUMMY INCLUDING INDEXES WITHOUT VALIDATION;

             

            Can you please take a look at my step and see it am adding, droping and swaping the right partition with right table. T1_parts is my orignal partition Y1_parts I think is the new one I create is the swap in step 5 above correct???

            tahnks a lot

            • 3. Re: Partition high_value.
              User503581-OC

              Hello here is an error am getting with spitting the partitions

              ALTER TABLE DOLLAR.TRA_ACTIVITY3

              SPLIT PARTITION T2_PARTS AT (VALUES LESS THAN ('20050101')

              INTO (PARTITION T1_PARTS, PARTITION T2_PARTS),

              UPDATE GLOBAL INDEXES WITHOUT VALIDATION;

               

              Error starting at line 7 in command:

              ALTER TABLE DOLLAR.TRA_ACTIVITY3

              SPLIT PARTITION T2_PARTS AT (VALUES LESS THAN ('20050101')

              INTO (PARTITION T1_PARTS, PARTITION T2_PARTS),

              UPDATE GLOBAL INDEXES WITHOUT VALIDATION

              Error report:

              SQL Error: ORA-00936: missing expression

              00936. 00000 -  "missing expression"

              *Cause:   

              *Action: