Updating partition key column would cause a partition change

Posted by / 13-Dec-2019 09:09

Updating partition key column would cause a partition change

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 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. PARTITION_NAME LAST_ANALYZED NUM_ROWS BLOCKS SAMPLE_SIZE HIGH_VALUE PORTAL_SERVICE_1 12/8/2016 41 1 PORTAL_SERVICE_2 6/8/2016 44 2 PORTAL_SERVICE_3 10/8/2016 29602 2014 29602 3 PORTAL_SERVICE_OTHERS 24/5/2016 0 110 DEFAULT i get error: Error report - SQL Error: ORA-14402: updating partition key column would cause a partition change 14402.00000 - "updating partition key column would cause a partition change" i am not allowed to use Enable Row Movement."Updating partition key column would cause a partition change" is an Oracle error message. This is something you will have to deal when working with Oracle partitioned table while updating existing records.Before diving in with this problem, we will need to understand ROW Movement in Oracle DBMS.

updating partition key column would cause a partition change-34updating partition key column would cause a partition change-73updating partition key column would cause a partition change-43

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

One thought on “updating partition key column would cause a partition change”