Update operations on partition fields in a partitioned table

By default, oracle Partition Table for partition fields is not allowed to update the operation, if there is a forward update on the partition field, the error -- ORA-14402: updating the partition keyword column will change the partition. However, you can enable the row movement attribute of a table to update partition fields.

Example: Create a partition table test_part for the experiment.

Create table TEST_PART
(
A1 NUMBERnot null,
A2 DATE not null,
A3 VARCHAR2 (6) not null,
A4 DATE not null,
A5 NUMBER not null,
)
Partition by range (A1)
(
Partition P1 values less than (1000 ),
Partition P2 values less than (2000 ),
Partition P3 values less than (3000 ),
Partition P4 values less than (4000 ),
Partition P5 values less than (5000 ),
Partition P6 values less than (MAXVALUE)
);

Insert the following data
SQL> select * from test_part;

A1 A2 A3 A4 A5
------------------------------------------------
123 2006-06-30 123456 2006-06-30 123
456 asdfgh 2006-06-30 456
1 2006-06-30 234123 2006-06-30 1
2 2006-06-30 234234 2006-06-30 2
1234 2006-06-30 456789 2006-06-30 1234
1111 2006-06-30 ewrqwe 2006-06-30 1111
2222 2006-06-30 fdafda 2006-06-30 2222
3333 2006-06-30 342342 2006-06-30 3333
5678 2006-06-30 qwerty 2006-06-30 5678

9 rows selected

Partitions P1 and P2 have the following data:
SQL> select rowid, t. * from test_part partition (p1) t;

ROWID A1 A2 A3 A4 A5
------------------------------------------------------------------
AAAGLoAAGAAAtsEAAB 456 2006-06-30 asdfgh 2006-06-30 456
AAAGLoAAGAAAtsEAAC 1 2006-06-30 234123 2006-06-30 1
AAAGLoAAGAAAtsEAAD 2 2006-06-30 234234 2006-06-30 2
AAAGLoAAGAAAtsEAAE123 2006-06-30 123456 2006-06-30 123

SQL> select rowid, t. * from test_part partition (p2) t;

ROWID A1 A2 A3 A4 A5
------------------------------------------------------------------
AAAGLwAAGAAA + 8 MAAC 1234 2006-06-30 456789 2006-06-30 1234
AAAGLwAAGAAA + 8 MAAD 1111 2006-06-30 ewrqwe 2006-06-30 1111

An error is prompted during direct update.
SQL> update test_part set a1 = 1123 where a1 = 123;

Update test_part set a1 = 1123 where a1 = 123

ORA-14402: updating partition keyword columns will cause partition changes

Open the row movement attribute
SQL> alter table test_part enable row movement;

Table altered

Execute the update operation again
SQL> update test_part set a1 = 1123 where a1 = 123;

1 row updated

The execution is successful and migrated to the P2 partition, And the rowid also changes.
SQL> select rowid, t. * from test_part partition (p2) t;

ROWID A1 A2 A3 A4 A5
------------------------------------------------------------------
AAAGLwAAGAAA + 8 MAAC 1234 2006-06-30 456789 2006-06-30 1234
AAAGLwAAGAAA + 8 MAAD 1111 2006-06-30 ewrqwe 2006-06-30 1111
AAAGLwAAGAAA + 8 PAAB1123 2006-06-30 123456 2006-06-30 123

SQL>

Enable row movement allows data segment compression and update partition field data (cross-partition)

However, it is also restrictive: The rowid of a normal table (heap-organized) changes after the row is migrated, and it is still valid for the index-organized rowid, however, the actual physical structure is incorrect.

(Reference: http://www.itpub.net/283642,1.html)