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.

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 ......

user8604530

ERROR at line 1:
ORA-14060: data type or length of a table partitioning column may not be changed

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=

Unknown

>
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.

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 ....

Unknown

>
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.

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

Unknown

>
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.

sb92075

PDNFTT

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.