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.
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.