12cR2 new feature: online table move
Submitted by John Watson on Wed, 2016-10-19 08:10
How cool is that?
--
John Watson
Oracle Certified Master DBA
http://skillbuilders.com
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
»
- John Watson's blog
- Log in to post comments