Lesson 5	Enabling Row Movement
Objective	Describe how to enable Row Movement in a Partitioned Table

Enabling Row Movement

Once your table has been partitioned, you may come across a problem in the maintenance of the data. Let us say that the partitioning is based on the value of the PUBLISHED_DATE of a CD or phonograph record. For example, when you insert a row with a date, the row is inserted into the first partition. Later, you discover that the date is incorrect. You attempt to update the date, but you get an error message:

ORA-14402: 
updating partition key column would cause a partition change

Oracle has stopped the update because it would cause the row to belong to a different partition. Oracle has implemented a new feature, called row movement^[1], which allows you to make this kind of update. When you complete the update, the row moves to the appropriate partition automatically.
The new feature can be implemented when you first create the table (the CREATE TABLE command) or you can implement it with an ALTER TABLE command.
In the following MouseOver, you can see the syntax and examples of the CREATE TABLE command.

Include optional storage clauses here such as TABLESPACE, PCTFREE, and PCTUSED.
Define the partitions here.
To allow row movement, add this clause to the end of the statement. The default is no row movement.

CREATE TABLE tablename
(column1 datatype,
column2 ...,
physical storage clauses
PARTITION BY RANGE (column list)
(PARTITION partname1 VALUES LESS THAN (values list),
PARTITION partname2 ...))
ENABLE ROW MOVEMENT

Include optional storage clauses here such as TABLESPACE, PCTFREE, and PCTUSED

CREATE TABLE WHOLESALE_ORDER
(ORDER_ID NUMBER,
CUST_ID NUMBER,
LAST_PART_ORDERED NUMBER,
ORDER_DATE DATE)
TABLESPACE USERS PCTFREE 20
PARTITION BY RANGE (LAST_PART_ORDERED)
(PARTITION LOW_PART  VALUES LESS THAN (1000),
(PARTITION MED_PART  VALUES LESS THAN (50000),
(PARTITION HIGH_PART  VALUES LESS THAN (MAXVALUE))
ENABLE ROW MOVEMENT

Enable Row Movement
To change a partitioned table to allow row movement, use this command: ALTER TABLE tablename ENABLE ROW MOVEMENT; To change it back to not allowing row movement, use this command: ALTER TABLE tablename DISABLE ROW MOVEMENT; The next lesson covers renaming, moving, and coalescing partitions.

[1] Row movement: The automatic shifting of a row from one partition to another when the value of the partitioning column in the row changes so that the row belongs in a different partition.