Table Reorganizing by Alter Table Move in Oracle

Alter table move is the another option through which you can reorganize the tables in oracle.

Syntax:-

SQL> ALTER TABLE EMP MOVE;

Table altered


Advantages :-

1. When you reorganize the table oracle reset the HWM which removes the fragmentation of a table and increases the performance of a SQL's.

2. High Water Mark is a Marker where oracle stops writing in the block and put a mark where it ends or We can say its a barrier between used and unused blocks.


Drawbacks:-

1. Table is not available for DML during the move operation.
2. Additional space is needed.
3. Indexes need to be rebuilt manually because Row-Ids are changed.

• You can see in this output how row-ids will change after moving the table.
• All the indexes are created on this row-ids this is the reason indexes will not work. 

>> Before Moving Table <<

SQL> Select rowid from EMP;

ROWID
------------------
AAAVlnAAGAAEcPlAAO
AAAVlnAAGAAEcPlAAP
AAAVlnAAGAAEcPlAAQ
AAAVlnAAGAAEcPlAAR
AAAVlnAAGAAEcPlAAS
AAAVlnAAGAAEcP0AAa


>> After moving Table <<


SQL> Select rowid from EMP;

ROWID
------------------
AAAWITAAGAACzdNAAa
AAAWITAAGAACzdNAAb
AAAWITAAGAACzdNAAc
AAAWITAAGAACzdNAAd
AAAWITAAGAACzdOAAA
AAAWITAAGAACzdOAAB


Check the index status Now

SQL> select status,index_name from dba_indexes where table_name='EMP';


STATUS   INDEX_NAME
-------- ---------------------------------
UNUSABLE      EMP_INDX
UNUSABLE      EMP_INDX_001

>> Indexes Should be rebuild now

SQL> Alter index emp_indx rebuild online;

SQL> Alter index emp_indx_001 rebuild online;


>> Now Gather Fresh Stats of a table to update optimizer.

SQL> Begin DBMS_STATS.GATHER_TABLE_STATS (
         ownname =>'HMIS',
         tabname =>'EMP',
         degree => 2,
         cascade => TRUE,
         METHOD_OPT => 'FOR COLUMNS SIZE AUTO',
         estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE);
         END;
         /

Thanks For Reading....