13 Replies Latest reply: Dec 19, 2013 3:14 AM by SKP RSS

    Add Partition to the existing table partitions.

    carmac

      HI all,

       

      I have a table with 10 data partitions and 10 index partitions now i want to add one data partition and one index partition to the existing table.

       

      How to add partitions to the existing table partitions.

       

      Thanks,

        • 1. Re: Add Partition to the existing table partitions.
          Karthick_Arp

          Provide the DDL for both your table and index. Also mention your DB Version.

          • 2. Re: Add Partition to the existing table partitions.
            Purvesh K

            It depends on where you are adding the Partition. You need to split the partitions to add any.

             

            See the demo:

             

            drop table test_table;

             

             

            create table test_table (col date) partition by range (col)
            (
                partition p_1 values less than (to_date('17-DEC-2013', 'DD-MON-YYYY')),
                partition p_def values less than (maxvalue)
            );

             

             

            select partition_name, high_value, partition_position
              from user_tab_partitions
            where table_name = 'TEST_TABLE';

             

            PARTITION_NAME HIGH_VALUE                                                                           PARTITION_POSITION    

            -------------- ------------------------------------------------------------------------------------ ----------------------

            P_1            TO_DATE(' 2013-12-17 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')  1

            P_DEF          MAXVALUE                                                                             2


            alter table test_table split partition p_def  at (to_date('19-DEC-2013', 'DD-MON-YYYY')) into (partition p_2, partition p_def);

             

             

            select partition_name, high_value, partition_position
              from user_tab_partitions
            where table_name = 'TEST_TABLE';

             

            PARTITION_NAME  HIGH_VALUE                                                                          PARTITION_POSITION    

            --------------- ----------------------------------------------------------------------------------- ----------------------

            P_1             TO_DATE(' 2013-12-17 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') 1

            P_2             TO_DATE(' 2013-12-19 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') 2

            P_DEF           MAXVALUE                                                                            3

            • 3. Re: Add Partition to the existing table partitions.
              carmac

              P_13            TO_DATE(' 2013-12-31 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') 

              P_DEF          MAXVALUE   


              i have partitions upto 2013 but there is no MAXVALUE partition.Now i want to add p_14 partition.

              • 4. Re: Add Partition to the existing table partitions.
                SKP

                Then You need to add partition P_14

                Just modified the steps what  PurveshK suggested.

                 

                create table test_table1 (col date) partition by range (col)

                (

                    partition p_12 values less than (to_date('31-DEC-2012', 'DD-MON-YYYY')),

                    partition p_13 values less than (to_date('31-DEC-2013', 'DD-MON-YYYY'))

                );

                 

                 

                 

                 

                select partition_name, high_value, partition_position

                  from user_tab_partitions

                where table_name = 'TEST_TABLE1';

                 

                 

                 

                PARTITION_NAME   HIGH_VALUE                                                                                                                    PARTITION_POSITION  

                 

                P_12             TO_DATE(' 2012-12-31 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')    1

                P_13             TO_DATE(' 2013-12-31 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')    2

                 

                 

                 

                 

                 

                alter table test_table1 ADD  partition p_14 values less than (to_date('31-DEC-2014', 'DD-MON-YYYY'));

                 

                 

                 

                 

                select partition_name, high_value, partition_position

                  from user_tab_partitions

                where table_name = 'TEST_TABLE1';

                 

                 

                 

                 

                PARTITION_NAME   HIGH_VALUE                                                                                                                     PARTITION_POSITION 

                P_12             TO_DATE(' 2012-12-31 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')    1

                P_13             TO_DATE(' 2013-12-31 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')    2

                P_14             TO_DATE(' 2014-12-31 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')    3

                • 5. Re: Add Partition to the existing table partitions.
                  carmac

                  how to add index partition to the existing table.

                  • 6. Re: Add Partition to the existing table partitions.
                    Purvesh K

                    If the Index is Local, it will be rebuilt automatically. You need not work on it.

                     

                    Read Managing Partitioned tables and Indexes

                    • 7. Re: Add Partition to the existing table partitions.
                      SKP

                      Check if the index (local index) , then the index will be created automatically for you.

                      • 8. Re: Add Partition to the existing table partitions.
                        carmac

                        Sorry!!!  this is a local index but i need to add indexs manually with my indexnames and table space name.

                        • 9. Re: Add Partition to the existing table partitions.
                          Purvesh K

                          If it is not local, there is no question about adding partitions to it. You could just rebuild the indexes after you load the data, if necessary.

                           

                          Sorry, You need to provide more information about your table and the Indexes (Global and Local).

                          • 10. Re: Add Partition to the existing table partitions.
                            SKP

                            Global Index? :

                             

                            User the earlier table i posted and follow the steps to add a partition in the global index.

                             

                            CREATE INDEX test_table1_idx ON test_table1 (col)

                            GLOBAL PARTITION BY RANGE (col)

                            (

                            PARTITION I_12 VALUES LESS THAN  (to_date('31-DEC-2012', 'DD-MON-YYYY')) ,

                            PARTITION I_13 VALUES LESS THAN  (MAXVALUE)

                              );

                             

                            select *  from user_ind_partitions  where index_name='TEST_TABLE1_IDX';

                             

                             

                            ALTER INDEX TEST_TABLE1_IDX 

                            SPLIT PARTITION I_13 AT (to_date('31-DEC-2013', 'DD-MON-YYYY')) INTO

                            (PARTITION I_13 , PARTITION I_14 );

                             

                             

                            select *  from user_ind_partitions  where index_name='TEST_TABLE1_IDX';

                            • 11. Re: Add Partition to the existing table partitions.
                              carmac

                              Sorry!!!  this is a local index but i need to add indexs manually with my indexnames and tablespace name.

                              • 12. Re: Add Partition to the existing table partitions.
                                Purvesh K

                                Did you bother to read the link I provided in an earlier post? It has stated the scenarios and the necessary actions. Please do not expect people to spoon feed you.

                                • 13. Re: Add Partition to the existing table partitions.
                                  SKP

                                  Put Your effort too:

                                  create table test_table2 (col date) partition by range (col)

                                  (

                                      partition p_12 values less than (to_date('31-DEC-2012', 'DD-MON-YYYY')),

                                      partition p_13 values less than (to_date('31-DEC-2013', 'DD-MON-YYYY'))

                                  );

                                   

                                   

                                  select partition_name, high_value, partition_position

                                  from user_tab_partitions

                                  where table_name = 'TEST_TABLE2'; --check the table partition

                                   

                                   

                                  CREATE INDEX TEST_TABLE2_IDX ON TEST_TABLE2 (COL) LOCAL

                                  (PARTITION IDX_12 TABLESPACE users,

                                    PARTITION IDX_13 TABLESPACE users);

                                   

                                  select *  from user_ind_partitions  where index_name='TEST_TABLE2_IDX';--check the index partition

                                   

                                   

                                  alter table test_table2 ADD  partition p_14 values less than (to_date('31-DEC-2014', 'DD-MON-YYYY'));

                                   

                                  select partition_name, high_value, partition_position

                                  from user_tab_partitions

                                  where table_name = 'TEST_TABLE2';--Aggin check the new table partition

                                   

                                   

                                  select *  from user_ind_partitions  where index_name='TEST_TABLE2_IDX';-- Now check the index partition because of new table partition

                                   

                                   

                                  ALTER INDEX TEST_TABLE2_IDX RENAME PARTITION P_14 to IDX_14  ;  --Rename the index

                                   

                                   

                                  ALTER INDEX TEST_TABLE2_IDX rebuild PARTITION IDX_14  TABLESPACE YOUR_TS; --Move the index to new tablespce if required

                                   

                                   

                                  Now the New partition  P_14 added to your table

                                  New Index partition IDX_14  added using tablespace YOUR_TS