ALTER TABLE … MOVE ONLINE

As you know, using the ALTER TABLE … MOVE command to relocate a table causes the related indexes of that table to become UNUSABLE.

Starting from version 12.2, using the ONLINE keyword in this command prevents the indexes from becoming invalid, and they will remain in a VALID state.

Example:

create index ind on tbl(name);
———
alter table tbl move online;
——-
select index_name,status from dba_indexes where index_name=’IND’;

INDEX_NAME STATUS
IND         VALID

As shown in the example above, the status of the index IND remains VALID even after moving the table TBL.

Next, let’s compare the execution time between the new ONLINE form of the command and the traditional one:

SQL> alter table tbl move online;
Table altered.
Elapsed: 00:16:01.52
———-
SQL> alter table tbl move;
Table altered.
Elapsed: 00:01:03.70

As you can see, moving a table online takes significantly more time.