Sunday, 2 March 2014

Things to consider before moving table

 The following thing can be potential problem for big size table move .

1. No transaction should access those tables which you are going to move otherwise there will be ORA error on application side .So better to use dbms_redifnation in this case.( dbms_redifnation is available only EE edition) .

2. Time estimation can be go wrong .It may be due to different storage type used ( some times in some scenario the moving table reside on attached hard disk level file system and target reside on NAS or SAN ). I have seen that even 100GB of table takes time around 4 hours although target tablesapce and source tablespace were on different mount point but on same storage ( it was SAN ) .

3. Always use DBverify command to check block level corruption on target datafile before moving large tables .Otherwise it stuck in the middle of operation and some times it gets failed . can not use index rebuild on BITMAP index on partition table , if it is invalid state , you have to drop and rebuild .

5. You can not move those table which LONG Rows .

6. Always take expdp of those tables which you going to move, so in the middle of operation , this activity failed , you can rolled back .

7. Always enable row movement for the table which is going to be move.

8. Move the table.

9. Disable row movement for the table .

No comments:

Post a Comment