Updating partition key column would cause a partition change

Oracle throws an error : ORA-14402 updating partition key column would cause a partition change I think that this is because emp_det is a partitioning key of a partitioned table.

Oracle documentation says that "UPDATE will fail if you change a value in the column that would move the row to a different partition or subpartition, unless you enable row movement" .

create table INVOICE( INVOICE_ID NUMBER , INVOICE_SK NUMBER , INVOICE_AMOUNT NUMBER , INVOICE_TEXT VARCHAR2(4000 Char) , B2B_FLAG NUMBER -- 0 or 1 , ACTIVE NUMBER(1) -- 0 or 1 ) PARTITION BY LIST (ACTIVE) SUBPARTITION BY LIST (B2B_FLAG) ( PARTITION p_active_1 values (1) ( SUBPARTITION sp_b2b_flag_11 VALUES (1) , SUBPARTITION sp_b2b_flag_10 VALUES (0) ) , PARTITION p_active_0 values (0) ( SUBPARTITION sp_b2b_flag_01 VALUES (1) , SUBPARTITION sp_b2b_flag_00 VALUES (0) ) ) For perfomance reasons the table should get a "Composite List-List" partitioning, see

can anybody shed some light if this can be used in the scenario: I came across this question as the top Google result when searching for ORA-14402.

– Data is partitioned to distinguish active data from those in archived state. are compressed and located on low cost storage tiers. are identified as read-only so that subsequent backups are not required after the initial database backup.

create table emp (id number, name varchar2(30), retired char(1)) row archival partition by list(ora_archive_state) (partition p_active values(0) tablespace active_tbs, partition p_archived values (default) tablespace archived_tbs compress); select id, name, retired, ora_archive_state from emp partition (p_active); ID NAME RETIRED ORA_ARCHIVE_STA ---------- ---------- ------- --------------- 1 EMP1 N 0 2 EMP2 N 0 3 EMP3 N 0 4 EMP4 N 0 5 EMP5 N 0 select id, name, retired, ora_archive_state from emp partition (p_archived); ID NAME RETIRED ORA_ARCHIVE_STA ---------- ---------- ------- --------------- 6 EMP6 Y 1 7 EMP7 Y 1 8 EMP8 Y 1 9 EMP9 Y 1 10 EMP10 Y 1 connect target sys/[email protected] configure exclude for tablespace pdb1:archived_tbs; Tablespace PDB1: ARCHIVED_TBS will be excluded from future whole database backups new RMAN configuration parameters are successfully stored I hope information in this post was useful.

This clause either enables or disables the migration of a row to a new partition if it's key is updated. A partitioned table should prevent moving data from one partition to another unless you are correcting data.

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.