row movement in Oracle

Posted on 2010-01-08
Medium Priority
Last Modified: 2012-06-27
 Hello Experts !
   What is enable row movement in Oracle ?
    Where we can use this options in database ?
    What are the performance issues a DBA can face if it remains enabled on some frequently used  tables in database ?

Thnaks & regards
Question by:junaid_baig1
  • 3
  • 2
  • 2
  • +2
LVL 74

Accepted Solution

sdstuber earned 1000 total points
ID: 26210282
just as it sounds,  "row movement" means the row can be physically moved from one area to another as part of updates.  That is,  the data may be deleted from one data file and rewritten to another part of the datafile, or a new datafile, or even a new tablespace entirely in some cases.

Partitioning is probably the most common use of this feature.
For example,

I have data partitioned by year and each year-partition is in its own tablespace.

Now, I update a row that was for 2009 to be 2010.  It must go into a new partition, but the only way it can do that is if the table allows row movement because the update requires a physical rearrangement, not just twiddling some bytes.

Row movement is a performance drag when it happens but if it's necessary then it's necessary.

LVL 74

Expert Comment

ID: 26210298
if the data changes never require row movement then having it enabled won't incurr a performance penalty.
LVL 15

Expert Comment

by:Shaju Kumbalath
ID: 26210301
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 74

Expert Comment

ID: 26210337
if you enable row movement for the purposes of reorganization, as in the link above.  I suggest you enable, do the reorg and then disable.

But first, be sure the reorg is necessary.
LVL 48

Assisted Solution

schwertner earned 1000 total points
ID: 26210531
The positive sides of row movements are explained   by the previous postings and also here :

But there are also negative movements called row migration. This means that there is not enough space in the Oracle Data Block (by update operation) for the record and Oracle has placed the record in otherOracle block. But this slows down the processing and should be avoided.

Sometimes Oracle blocks get fragmented on the disk device. In this case some techniques (export/import or tablespace change) can help to improve the placement.
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 26210614
These questions sound a lot like homework/exam questions.
LVL 48

Expert Comment

ID: 26213110
@ slightwv:

Row Movement is not part even of Oracle University DBA Courses.
So (if you are right) this should be a very sophisticated course in a technological University

@ junaid_baig1: please clarify, because slightwv has the power to close the tread (I not :) )

Author Closing Comment

ID: 31674551

 Thanks !!
 we faced this Issues as new Year begins!

@schwertner:  Thanks!! I think slightwv couldn't get point..

Author Comment

ID: 26214214

 @slightwv:  i think you didn't get the point that i raised!  This is quite a common Issue that Development DBA team faced every now & than & They 've discussed the Issue with US  ! 'm DBA ADMIn !    

Thanks & regards

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Suggested Courses
Course of the Month17 days, 6 hours left to enroll

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question