Pages

Sunday 22 January 2012

Oracle enable row movement


When you add the clause "enable row movement" to a create table statement, you are granting permission to Oracle to change the ROWID's. It basically enables rows to move. If table is already created, you can enable row movement by using below command.

SQL> alter table table_name enable row movement;


Note that tables have to be in an ASSM (Automatic Segment Space Managment) tablespace for this to work so if they are not, you have to move them there first in order to enable row movement.

We use "enable row movement"  in below cases

1. We want to reclaim a space with the "alter table shrink space"  

SQL> alter table table_name shrink space;


2. When we want to use flashback table because we achieve that by:

a) deleting all rows modified or inserted since the time you want to flashback to
b) inserting all rows modified or deleted since the time you want to flashback to

so, if the emp_id record in EMP table is updated - we delete and insert it - it'll have a new rowid, it
"moved"

Example of flashback table is

SQL> FLASHBACK TABLE EMP TO TIMESTAMP
TO_TIMESTAMP('2012-23 08:10:00',
'YYYY-MM-DD HH24:MI:SS');



2 comments:

  1. What if I keep "enable row movement" always for a table in ASM ? will it have any downside? If yes then what it could be? Else will it take care of Fragmentation??

    ReplyDelete
  2. Santosh..I hope you are mentioning ASSM not ASM. Well if you enable row movement, there is no downside on database performance however while running it will consume resources on your machine. It will generate both redo and undo.

    It doesn't take care of fragmentation itself. It enables you to do so by allowing rows to move while doing re-org.

    Regards,
    Sangamesh

    ReplyDelete