Ora 14402 updating partition key column
—LOCAL REPLICATE THE ERROR:——————– create table partition_test( key number(5)) partition by range(key) (partition p1 values less than (100), partition p2 values less than (200), partition p3 values less than (300), partition p4 values less than (maxvalue)); Table created.
Last updated on MARCH 06, 2019 Primavera P6 Enterprise Project Portfolio Management - Version 220.127.116.11 to 18.104.22.168 [Release 16.2]Information in this document applies to any platform.
ERROR: ORA-14402 Cause: An UPDATE statement attempted to change the value of a partition Key column causing migration of the row to another partition Action: Do not attempt to update a partition key column or make sure that The new partition key is within the range containing the old Partition key.; To reproduce ORA-14402 issue, we will need to create a partitioned table and then try to update the data from partition key column.
Below, we will create a partitioned Table, Insert some records and update the records that we just inserted to replicate the error ORA-14402.
Filled with real-world case studies and best practices, Oracle Database 11g Release 2 Performance Tuning Tips & Techniques details the latest monitoring, troubleshooting, and optimization methods.
Find out how to find and fix bottlenecks, configure storage devices, execute effective queries, and develop bug-free SQL and PL/SQL code.
PARTITION TABLE DDL: CREATE TABLE baniya.sales( prod_id NUMBER(6), cust_id NUMBER, time_id DATE, channel_id CHAR(1), promo_id NUMBER(6), quantity_sold NUMBER(3), amount_sold NUMBER(10, 2)) PARTITION BY RANGE(time_id)( PARTITION sales_m1_2016 VALUES LESS THAN(TO_DATE('01-APR-2016', 'dd-MON-yyyy')), PARTITION sales_m2_2016 VALUES LESS THAN(TO_DATE('01-MAY-2016', 'dd-MON-yyyy')), PARTITION sales_m3_2016 VALUES LESS THAN(TO_DATE('01-JUN-2016', 'dd-MON-yyyy'))); BEGIN INSERT INTO baniya.sales (prod_id,cust_id, time_id, channel_id, promo_id, quantity_sold, amount_sold) values(12388,4026373820,'02-MAY-2016',5,543,22,5000.00); INSERT INTO baniya.sales (prod_id,cust_id, time_id, channel_id, promo_id, quantity_sold, amount_sold) values(12388,4026373820,'02-APR-2016',5,543,22,5000.00); INSERT INTO baniya.sales (prod_id,cust_id, time_id, channel_id, promo_id, quantity_sold, amount_sold) values(12388,4026373820,'31-MAY-2016',5,543,22,5000.00); COMMIT; END; / Error starting at line : 32 in command - BEGIN UPDATE baniya.sales SET time_id ='29-MAY-2016' WHERE time_id = TO_DATE('02-APR-2016', 'DD-MON-YYYY'); COMMIT; END; Error report - ORA-14402: updating partition key column would cause a partition change ORA-06512: at line 2 14402.
00000 - "updating partition key column would cause a partition change" *Cause: An UPDATE statement attempted to change the value of a partition key column causing migration of the row to another partition *Action: Do not attempt to update a partition key column or make sure that the new partition key is within the range containing the old partition key.
If the partition key column needs frequent update, then you may re-think about another solid column from a table as a good candidate for partition key column.Will Oracle automatically recognize the records, for which the partitioning value has changed and move them to the appropriate partion or do I have to call some kind of maintenance function, in order to reorganize the partitions?create table T_TESTPART ( pk number(10), part_key number(10) ) partition by list (part_key) ( partition p01 values (1), partition p02 values (2), partition pdef values (default) ); alter table T_TESTPART add constraint pk_pk primary key (PK); I did not do any performance tests, but Oracle will probably delete the row from the first partition and insert it to the second partition. In my own databases, I usually only use partitioning on columns that do not change.When attempting to convert a project to a baseline in the Maintain Baseline dialog window the following error is observed: Executable: Application: Primavera P6 Professional R16.2Event Code: PRMDB-2963-FMessage: PRM DBAccess Error: Error executing query.Error: ORA-14402: updating partition key column would cause a partition change customer used orpm_table_to enable the table partitioning.