10 Replies Latest reply on Oct 1, 2012 6:42 PM by Mark Malakanov (user11181920)

    Oracle 11gR2: partition key column, need to be modified

    user8604530
      Hi folks,

      Have a situation, need to increase a column width, which is also a partition key and table is pretty huge, can any one assist, what is the best possible way to achieve this task.

      Thanks in advance.
        • 1. Re: Oracle 11gR2: partition key column, need to be modified
          sb92075
          user8604530 wrote:
          Hi folks,

          Have a situation, need to increase a column width, which is also a partition key and table is pretty huge, can any one assist, what is the best possible way to achieve this task.

          Thanks in advance.
          ALTER TABLE FOOBAR MODIFY ......
          • 2. Re: Oracle 11gR2: partition key column, need to be modified
            user8604530
            ERROR at line 1:
            ORA-14060: data type or length of a table partitioning column may not be changed
            • 3. Re: Oracle 11gR2: partition key column, need to be modified
              sb92075
              user8604530 wrote:
              ERROR at line 1:
              ORA-14060: data type or length of a table partitioning column may not be changed
              http://www.oracle.com/pls/db112/search?word=dbms_redefinition&partno=
              • 4. Re: Oracle 11gR2: partition key column, need to be modified
                rp0428
                >
                Have a situation, need to increase a column width, which is also a partition key and table is pretty huge, can any one assist, what is the best possible way to achieve this task.
                >
                You will need to rebuild the table. You cannot modify the partition key of the existing table.

                You can rebuild the table online using the DBMS_REDEFINITION package or you can rebuild it manually and then populate it with data from an export or from the existing table. Using PARALLEL for the population will speed things up.

                If you rebuild it offline populate it before adding the other indexes.

                BEFORE you rebuild you should review with your team any other changes that might be pending for the table. Other columns to add, columns to drop, tablespaces to change, etc.

                Maybe you even want to change the partitioning scheme to make it easier to roll off old data?

                If this table has been around a while now is a good time to use different tablespaces and eliminate whatever fragmentation there might be in the existing ones.

                Take advantage of the opportunity to fix/address any other issues you might have had with the current table.
                • 5. Re: Oracle 11gR2: partition key column, need to be modified
                  wakwau
                  I have done this thing .... We should recreate table .... to make faster used alter table exchange partition clause.
                  so step is :
                  1. create temp table using CTAS.
                  2. exchange partition to temp table.
                  2. drop and recreate table using partition with new column setting
                  3. modify the structure of temp table exactly same with new production table.
                  4. exchange again from temp to prod
                  5. recreate all indexs.
                  6. drink some coffeee ....
                  • 6. Re: Oracle 11gR2: partition key column, need to be modified
                    rp0428
                    >
                    I have done this thing .... We should recreate table .... to make faster used alter table exchange partition clause.
                    so step is :
                    1. create temp table using CTAS.
                    2. exchange partition to temp table.
                    2. drop and recreate table using partition with new column setting
                    3. modify the structure of temp table exactly same with new production table.
                    4. exchange again from temp to prod
                    5. recreate all indexs.
                    6. drink some coffeee ....
                    >
                    Maybe you should have started with step 6 and then added a bagel or donut.

                    The above won't work for OP's use case. The table has to be rebuilt with the larger partition key and then the data added.

                    It doesn't make sense to try to swap every partition to a temp table to try to do this.
                    • 7. Re: Oracle 11gR2: partition key column, need to be modified
                      Mark Malakanov (user11181920)
                      The above won't work for OP's use case.
                      It works perfectly well.
                      The table has to be rebuilt with the larger partition key and then the data added.
                      no need for the latter.
                      It doesn't make sense to try to swap every partition to a temp table to try to do this.
                      It does make sense, but it should be an individual "temp" table for each partition;

                      Edited by: user11181920 on Oct 1, 2012 1:51 PM
                      • 8. Re: Oracle 11gR2: partition key column, need to be modified
                        rp0428
                        >
                        it should be an individual "temp" table for each partition;
                        >
                        You could build a temp table for each row too but that doesn't make sense either.

                        No need to build multiple temp tables, do multiple swaps, and do multiple redefines of the temp table columns when you can just build the one table that you need.
                        • 10. Re: Oracle 11gR2: partition key column, need to be modified
                          Mark Malakanov (user11181920)
                          No need to build multiple temp tables, do multiple swaps, and do multiple redefines of the temp table columns when you can just build the one table that you need.
                          OK. Could you show us a script for your approach.

                          Meantime I show mine.

                          Preparation
                          create table PART_TEST (part_key number(1), val varchar2(100))
                          partition by range(part_key)(
                            partition P0 values less than (1),
                            partition P1 values less than (2),
                            partition P2 values less than (3),
                            partition P3 values less than (4)
                          );
                          
                          insert into PART_TEST values (0,'zero');
                          insert into PART_TEST values (1,'one');
                          insert into PART_TEST values (2,'two');
                          insert into PART_TEST values (3,'three');
                          commit;
                          The modification
                          set timing on
                          create table PART_TEST_P0 as select * from PART_TEST where 1=0;
                          create table PART_TEST_P1 as select * from PART_TEST where 1=0;
                          create table PART_TEST_P2 as select * from PART_TEST where 1=0;
                          create table PART_TEST_P3 as select * from PART_TEST where 1=0;
                          
                          alter table PART_TEST exchange partition P0 with table PART_TEST_P0;
                          alter table PART_TEST exchange partition P1 with table PART_TEST_P1;
                          alter table PART_TEST exchange partition P2 with table PART_TEST_P2;
                          alter table PART_TEST exchange partition P3 with table PART_TEST_P3;
                          
                          alter table PART_TEST_P0 modify part_key number(6);
                          alter table PART_TEST_P1 modify part_key number(6);
                          alter table PART_TEST_P2 modify part_key number(6);
                          alter table PART_TEST_P3 modify part_key number(6);
                          
                          drop table PART_TEST;
                          create table PART_TEST (part_key number(6), val varchar2(100))
                          partition by range(part_key)(
                            partition P0 values less than (1),
                            partition P1 values less than (2),
                            partition P2 values less than (3),
                            partition P3 values less than (4)
                          );
                          
                          alter table PART_TEST exchange partition P0 with table PART_TEST_P0;
                          alter table PART_TEST exchange partition P1 with table PART_TEST_P1;
                          alter table PART_TEST exchange partition P2 with table PART_TEST_P2;
                          alter table PART_TEST exchange partition P3 with table PART_TEST_P3;
                          Whole modification of the column takes a fraction of second because there is no need to physically move data. Only metadata is changed.