Ora 14402 updating partition key column

Rated 4.83/5 based on 588 customer reviews

—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 16.2.0.0 to 16.2.8.0 [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.

ora 14402 updating partition key column-20

ora 14402 updating partition key column-40

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.

Leave a Reply