Applies to:

Purpose

This FAQ documents answers some of the common questions and problems in Row Movements.

Questions and Answers

1. What is Row Movement?

The row movement lets you specify whether the database can move a table row. It is possible for a row to move, for example, during table compression or an update operation on partitioned data.
For more information, have a look at: row_movement_clause

2. How to enable row movement?

Use the option below with ALTER TABLE statement
{ ENABLE | DISABLE } ROW MOVEMENT

Default value is disable. For complete syntax, go to "ALTER TABLE" in Oracle Database SQL Language Reference (11.2)

3. Concepts / how it works internally?

When enable row movement was not set a rowid was assigned when you inserted a row and that never changed. After row movement is enabled a simple update could change your rowid.
When you "Enable Row Movement", you are just giving "Permission" to move a row.
You do not need to delete and insert, you just update the row, and that causes delete and insert, and in turn changes the ROWID.
So, delete and insert is the implicit operation which happens in the background.
Row movement is enabled at the table level, and is disabled by default.

4. Is it only for partitioned tables?

No, Row Movement is associated with partitioned as well as non-partitioned tables.
It is allowed for non-partitioned tables starting 9.2. It comes in to affect for non-partitioned tables when operations like table compression is performed.

5. Does it invalidate index?

No, it does not invalidate indexes

6. Does it create row chaining?

Row Movement will not cause Row Chaining. If the row doesn’t fit into a single data block, it must be chained. Row Chaining basically is the distribution of a single table row across multiple data blocks.
Row Movement is different as it updates the corresponding indexes—the ROWID actually changes. This has benefits on the long run, because the additional block read can be avoided in the TABLE ACCESS BY INDEX ROWID operation.

7. Does it have impact on performance?

Yes, it can cause performance problem as it can cause Oracle to move rows to discontinuous data blocks.
There could be some performance impact as it will necessarily consume processing resources on your machine while running.
The reason being it will:
        - read the table
        - delete/insert the rows at the bottom of the table to move them up
        - generate redo
        - generate undo

8. What are restrictions associated with "Enable Row Movement"?

You cannot specify this clause for a nonpartitioned index-organized table.
Tables need to be in an ASSM (Automatic Segment Space Management) tablespace for this to work.

9. Related Errors

ORA-14402: updating partition key column would cause a partition change
You get this error during UPDATE of a row if row movement is not enabled on the partitioned table, and the row with the new partitioned key value would need to be placed into a a different partition compared where the row before update is. See Document 236191.1 for more details.

ORA-10636: ROW MOVEMENT is not enabled
Document 1132163.1 What is the Meaning of SHRINK SPACE CHECK?

ORA-08189: cannot flashback the table because row movement is not enabled
Document 270060.1 Use Flashback Table Feature and Resolve errors
 Document 287698.1 OERR: ORA-8189 cannot flashback the table because row movement is not enabled

ORA-29887: cannot support row movement if domain index defined on table
Look at the documentation e.g. for 11.2


ORA-14661: row movement must be enabled
Look at the documentation e.g. for 11.2


ORA-14662: row movement cannot be enabled
Look at the documentation e.g. for 11.2

References