12cR2 new feature: online table move

articles: 

I'm sure all DBAs know the ALTER TABLE MOVE command - and its problems. See here:

C:\Users\john>sqlplus scott/tiger@x122

SQL*Plus: Release 12.1.0.2.0 Production on Wed Oct 19 13:44:32 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Last Successful login time: Wed Oct 19 2016 13:30:14 +01:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.0.3 - 64bit Production

x122> alter table emp move tablespace example;

Table altered.

x122> delete from emp where rownum=1;
delete from emp where rownum=1
*
ERROR at line 1:
ORA-01502: index 'SCOTT.PK_EMP' or partition of such index is in unusable state


x122> select index_name,status from user_indexes;

INDEX_NAME                     STATUS
------------------------------ --------
PK_DEPT                        VALID
PK_EMP                         UNUSABLE

x122> alter index pk_emp rebuild;

Index altered.

x122>
Not only is the table locked while the move is in progress, but also the move broke all the indexes. That is massive downtime. But this is release 12.2. Take a look at this syntax:
x122>
x122> alter table emp move tablespace users online update indexes;

Table altered.

x122> select index_name,status from user_indexes;

INDEX_NAME                     STATUS
------------------------------ --------
PK_DEPT                        VALID
PK_EMP                         VALID

x122>
The objects remain usable throughout and after the entire operation. You can move any LOBs, too.
How cool is that?
--
John Watson
Oracle Certified Master DBA
http://skillbuilders.com