OraFAQ Forum: SQL & PL/SQL » Row Movement disabled in Partitioned table

Home » SQL & PL/SQL » SQL & PL/SQL » Row Movement disabled in Partitioned table (Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Row Movement disabled in Partitioned table [message #671245]

Sun, 19 August 2018 23:11

saipradyumn
Messages: 419
Registered: October 2011
Location: Hyderabad

Senior Member

Hi All,

In Production environment ROW_MOVEMENT is disabled on one of the Partition table .
We trying to update the partition key column which causes partition change.

Table Name : CITY_OFFICES
Partition Type : RANGE
Interval :NUMTODSINTERVAL(1,'DAY')
Column Name : TIMESTAMP
Column Datatype : Date

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

In order to resolve that we are going for enabling the ROW MOVEMENT

ALTER TABLE CITY_OFFICES ENABLE ROW MOVEMENT;

I know that it will resolve my actual issue.
But is there any other impact if I Enable the row movement for that table
?

As that is production environment wants to check before enabling that ?

Please help me
Thanks
Sharavathi

[Updated on: Mon, 20 August 2018 00:03]

Report message to a moderator

Re: Row Movement disabled in Partitioned table [message #671247 is a reply to message #671245]

Mon, 20 August 2018 00:48

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

There is technical issue, the questions are:
- why ROW MOVEMENT was first disabled?
- if there is a functional reason for that, why it is allowed now to move a row between partitions?
Both questions are not Oracle's but functional/business ones.

Report message to a moderator

Re: Row Movement disabled in Partitioned table [message #671252 is a reply to message #671247]

Mon, 20 August 2018 05:23

saipradyumn
Messages: 419
Registered: October 2011
Location: Hyderabad

Senior Member

Hi Michel ,

As per the below document, it seems to be by default ROW MOVEMENT is not Enabled.
http://www.dba-oracle.com/t_callan_oracle_row_movement.htm

Now We have a situation to update the Partition Key column.

Instead of going for ROW MOVEMENT , we are trying to do the alternate from application/functional prospective.

Thanks for interest to resolve the issues

Thanks
Sharavathi

Report message to a moderator

Re: Row Movement disabled in Partitioned table [message #671254 is a reply to message #671252]

Mon, 20 August 2018 05:58

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

1/ Forget this site it is b..., NEVER EVER uses it, use Oracle genuine documentation instead.
2/ Row movement is disabled by default for backward (before version 8!) compatibility and prevent some bad applications from some errors.
3/ Do not rely on default options, ALWAYS investigate all of them when you create objects (and maybe always put all options in CREATE statement even of this is the default one to be sure you have investigated them and you deliberately put this value for all options)

Report message to a moderator

Re: Row Movement disabled in Partitioned table [message #671258 is a reply to message #671245]

Mon, 20 August 2018 06:19

John Watson
Messages: 8976
Registered: January 2010
Location: Global Village

Senior Member

I am not clear on Oracle's logic behind row movement, though it is all well documented.

The idea is that rowids should not change. So if a row has to be moved to a different block consequent on UPDATE, it is not in fact moved: it is migrated. This means that the associated index entries do not need to be changed, though subsequent SELECT may degrade. So this behaviour favours UPDATE and SELECT pays the price. A side effect is that operations that require rowids to change such as your update of the partition key column will fail. Other such operations are SHRINK SPACE and FLASHBACK TABLE. Enabling row movement means that all these operations are permitted so that rows will move and rowids will change, but it does not have any effect on the row migration behaviour.

It has always seemed to me (I may be wrong) that enabling row movement cannot have any bad effect. However, I do sometimes think it would be nice if it actually changed the migrate-on-update mechanism to move-on-update.

Report message to a moderator

Re: Row Movement disabled in Partitioned table [message #671259 is a reply to message #671258]

Mon, 20 August 2018 06:30

Roachcoach
Messages: 1576
Registered: May 2010
Location: UK

Senior Member

The rationale behind migrating is/was https://asktom.oracle.com/pls/apex/asktom.search?tag=row-migration

The Big Man

it would seriously damage the index structure.

In an index, you have the rowid as part of the key. An update to a row might have to update 6 indexes (cause the row moved) and update the indexes -- MOVING the rows in the index, perhaps splitting that up - but definitely cause tons of extra work.

An occasional extra IO on an indexed read was deemed preferrable to having to reach out and touch tons of index data.

Additionally -- with the exception of:

o index organized tables when you update a primary key
o partitioned tables with "enable row movement" specifically turned on and an update to the partition key

a rowid is immutable -- it is assigned to a row upon insert and will never change. Many tools count on this fact (replication used to but still can, indexes do, Oracle Forms does by default for row locking, and so on)

At least the last I heard.

Report message to a moderator

Previous Topic:	Prevent Rounding when using Format Masking
Next Topic:	Strange Issue in executing procedure

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Apr 28 17:36:30 CDT 2025