In previous Releases, To move a table to a different tablespace or segment, we need to take downtime for this activity And after moving the table, we have to rebuild/recreate the indexes.

Now with Oracle 12.2, We can move the table online to a different segment or tablespace, without impacting the DML activities and without making the indexes unusable. It will take care of the Index maintenance activity.

SYNTAX – ALTER TABLE MOVE ONLINE;

Lets test this feature.

On session 1( Run the alter table move online command)

On session 2( Do some DML activity)

Lets see, whether DML is getting blocked or not.

We can see the that the alter table statement is blocked by the insert statement, Even though we fired the Insert statement after the alter table move command. It shows the DMLs are not getting impacted due to this alter table move command online. However, the time taken to complete the alter table move online may take more time.

After few seconds, the DML statement completed.

Now only that alter table move statement ins running.

I checked the index status:

Now we are seeing more 2 indexes DBA_IDX1_SYS_95234,DBA_IDX2_SYS_95234 . It seems these indexes were created for the interim purpose during the move online operation.

After 2 min, the alter statement completed.

Check the index status:

The table has been moved to the new tablespace. All indexes are also valid, so no need to rebuild or recreate any of the indexes. 🙂

Oracle 12.2 is making our job much easier.

Leave a Reply