Enabling Row Movement in Oracle 11g R2 and Oracle 19c

The process for "enabling row movement" in a partitioned table in Oracle 11g Release 2 is the same in Oracle 19c.
Enabling Row Movement in a Partitioned Table In both Oracle 11g R2 and Oracle 19c, you enable row movement using the following SQL command:

ALTER TABLE partitioned_table ENABLE ROW MOVEMENT;

Why Enable Row Movement?

• When row movement is enabled, Oracle allows rows to be moved between partitions automatically.
  • This is particularly useful when performing:
    • Partition Exchange Operations: Swapping partitions with a table.
    • Update Operations on Partition Key Columns: If you update a column that determines partition placement, the row can move to the correct partition instead of failing.
    • Partition Maintenance (Merge, Split, Drop, Truncate): Operations that require redistributing rows across partitions.

Checking If Row Movement is Enabled You can verify whether row movement is enabled for a partitioned table using:

SELECT TABLE_NAME, ROW_MOVEMENT 
FROM USER_TABLES 
WHERE TABLE_NAME = 'PARTITIONED_TABLE';

Disabling Row Movement: If necessary, you can disable row movement with:

ALTER TABLE partitioned_table DISABLE ROW MOVEMENT;

Key Differences Between Oracle 11g R2 and 19c While the command and process remain the same, Oracle 19c introduces improvements in partitioning features, such as:

• Automatic List Partitioning
• Hybrid Partitioned Tables
• Online Partition Operations
• Enhanced Interval Partitioning

However, the fundamental behavior of enabling row movement remains unchanged.
Automatic List Partitioning in Oracle 19c
Automatic List Partitioning in Oracle 19c is a feature that simplifies the management of list-partitioned tables, especially when dealing with evolving lists of values. It automates the creation of new partitions when new, previously unseen values are inserted into the partitioning column. Here's a breakdown of how it works and its key benefits:
Core Functionality:

• Automated Partition Creation:
  • When a new value that doesn't correspond to an existing partition is inserted into a list-partitioned table, Oracle automatically creates a new partition to accommodate that value.
  • This eliminates the need for manual intervention to add new partitions, which is crucial in scenarios where the list of values is dynamic.
• Default Partition (Optional):
  • You can still define a default partition to handle values that don't explicitly match any defined partition.
  • Automatic list partitioning will create new partitions before the default partition is used.
• Simplification of Data Management:
  • It greatly simplifies the management of list-partitioned tables, particularly in applications where the set of possible values for the partitioning column is subject to change.
  • This is very useful for things like country codes, or status codes that are updated over time.

Benefits:

• Reduced Administrative Overhead:
  • Eliminates the manual effort required to create new partitions.
  • Reduces the risk of errors associated with manual partition management.
• Improved Application Availability:
  • Ensures that new data can be inserted without interruption, even when new values are encountered.
  • Prevents application failures due to missing partitions.
• Enhanced Flexibility:
  • Provides greater flexibility in handling dynamic lists of values.
  • Adapts to changing data requirements without requiring application or database modifications.
• Better data organization:
  • Like all partitioning, it improves query performance when queries are targeted at a specific partition.

In essence: Automatic List Partitioning is a significant enhancement that makes list partitioning more adaptable and easier to manage in Oracle 19c. It streamlines data management and improves application availability by automating the creation of new partitions for evolving lists of values.

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 diagram, you can see the syntax and examples of the CREATE TABLE command.

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.