I am altering 4 table structures, by changing the datatype from NUMBER(4) to NUMBER on the database. The table is the child table of a reference partition constraint and the field is part of the FK constraint which makes up the reference partition constraint.
When attempting to ALTER TABLE by modifying the column or attempting to drop the constraint, I get
>> ALTER TABLE assuper.mds_claim_line_errors MODIFY(aerr_code NUMBER)
ORA-14061: data type or length of an index partitioning column may not be changed
The only solution I am able to find, is to recreate a new table structure with the right field datatype and then rebuilding the reference partition and moving the data across using datapump. These are important tables and they are meta data tables where the row count is ranging from aprox. 400 000 000 to 4 000 000 000 rows each.
My questions are:
1. With regarding to the refence constraint child table data structure change, is there a better way of doing this change?
2. Would using datapump be the safest and fastest way to move the data across
You don't need to move any data to achieve this ... but you do need to jump through a few hoops :-)
The basic principle is:
- create standalone tables for your current partitions
- exchange the partitions out into the standalone tables
- extend the data types on the standalone tables
- create a new version of your reference partitioned table with extended datatypes
- exchange the standalone tables into the new table
- rename old/new
eg
SQL>
SQL> create table orders (
2 order_id number(5) not null,
3 order_date date,
4 constraint order_pk primary key (order_id))
5 partition by range (order_date)
6 (partition p_2018 values less than ('01-jan-2019'),
7 partition p_2019 values less than ('01-jan-2020')
8 );
Table created.
SQL>
SQL> create table order_items (
2 order_item_id number(5) not null,
3 order_id number(5) not null,
4 order_item varchar2(100),
5 constraint order_item_pk primary key (order_item_id),
6 constraint order_item_fk foreign key (order_id) references orders(order_id) on delete cascade)
7 partition by reference (order_item_fk);
Table created.
SQL>
SQL> insert into orders values (1,'01-dec-2018');
1 row created.
SQL> insert into order_items values (1,1,'item a');
1 row created.
SQL> insert into order_items values (2,1,'item b');
1 row created.
SQL> commit;
Commit complete.
SQL>
SQL> insert into orders values (2,'01-dec-2019');
1 row created.
SQL> insert into order_items values (3,2,'item a');
1 row created.
SQL> insert into order_items values (4,2,'item b');
1 row created.
SQL> commit;
Commit complete.
SQL>
SQL>
SQL> create table orders_2018 (
2 order_id number(5),
3 order_date date,
4 constraint order_2018_pk primary key (order_id));
Table created.
SQL>
SQL> create table order_items_2018 (
2 order_item_id number(5),
3 order_id number(5) not null,
4 order_item varchar2(100),
5 constraint order_item_2018_pk primary key (order_item_id),
6 constraint order_item_2018_fk foreign key (order_id) references orders_2018 (order_id) on delete cascade);
Table created.
SQL>
SQL> create table orders_2019 (
2 order_id number(5),
3 order_date date,
4 constraint order_2019_pk primary key (order_id));
Table created.
SQL>
SQL> create table order_items_2019 (
2 order_item_id number(5),
3 order_id number(5) not null,
4 order_item varchar2(100),
5 constraint order_item_2019_pk primary key (order_item_id),
6 constraint order_item_2019_fk foreign key (order_id) references orders_2019 (order_id) on delete cascade);
Table created.
SQL>
SQL> alter table orders
2 exchange partition p_2018
3 with table orders_2018 cascade update global indexes;
Table altered.
SQL>
SQL> alter table orders
2 exchange partition p_2019
3 with table orders_2019 cascade update global indexes;
Table altered.
SQL>
SQL> alter table orders_2019 modify order_id number(10);
Table altered.
SQL> alter table orders_2018 modify order_id number(10);
Table altered.
SQL>
SQL> alter table order_items_2018 modify order_id number(10);
Table altered.
SQL> alter table order_items_2019 modify order_id number(10);
Table altered.
SQL>
SQL> alter table order_items_2018 modify order_item_id number(10);
Table altered.
SQL> alter table order_items_2019 modify order_item_id number(10);
Table altered.
SQL>
SQL>
SQL> create table new_orders (
2 order_id number(10) not null,
3 order_date date,
4 constraint new_order_pk primary key (order_id))
5 partition by range (order_date)
6 (partition p_2018 values less than ('01-jan-2019'),
7 partition p_2019 values less than ('01-jan-2020')
8 );
Table created.
SQL>
SQL> create table new_order_items (
2 order_item_id number(10) not null,
3 order_id number(10) not null,
4 order_item varchar2(100),
5 constraint new_order_item_pk primary key (order_item_id),
6 constraint new_order_item_fk foreign key (order_id) references new_orders(order_id) on delete cascade)
7 partition by reference (new_order_item_fk);
Table created.
SQL>
SQL> alter table new_orders
2 exchange partition p_2018
3 with table orders_2018 cascade update global indexes;
Table altered.
SQL>
SQL> alter table new_orders
2 exchange partition p_2019
3 with table orders_2019 cascade update global indexes;
Table altered.
SQL>
SQL>
SQL> rename orders to old_Orders;
Table renamed.
SQL> rename order_items to old_order_items;
Table renamed.
SQL>
SQL> rename new_orders to orders;
Table renamed.
SQL> rename new_order_items to order_items;
Table renamed.
SQL>
SQL> desc orders
Name Null? Type
----------------------------------------------------------------------- -------- ------------------------------------------------
ORDER_ID NOT NULL NUMBER(10)
ORDER_DATE DATE
SQL> desc order_items
Name Null? Type
----------------------------------------------------------------------- -------- ------------------------------------------------
ORDER_ITEM_ID NOT NULL NUMBER(10)
ORDER_ID NOT NULL NUMBER(10)
ORDER_ITEM VARCHAR2(100)
SQL>
SQL> select * from orders;
ORDER_ID ORDER_DAT
---------- ---------
1 01-DEC-18
2 01-DEC-19
2 rows selected.
SQL> select * from order_items;
ORDER_ITEM_ID ORDER_ID ORDER_ITEM
------------- ---------- ----------------------------------------------------------------------------------------------------
1 1 item a
2 1 item b
3 2 item a
4 2 item b
4 rows selected.
SQL>