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.
Search for updating partition key column would cause a partition change:




Points can also be converted to gift cards which can be used in major establishments such as Walmart, Amazon, and i Tunes. Vindale pays out through Pay Pal with a Trustpilot score of 8 .
Interracial porn features men and women of different races having hardcore sex.
Ruidoso weather is mild and great for enjoying the outdoors and the natural wonders that abound in Ruidoso, Lincoln County and the Lincoln National Forest.