12C新特性：partition move online 分区表在线迁移

1、创建分区表

SQL> CREATE TABLE t_whs_p
  2  (ID number, WNAME varchar2(10))
  3  PARTITION BY RANGE (id)
  4  (PARTITION p1 VALUES LESS THAN (10),
  5  PARTITION p2 VALUES LESS THAN (20),
  6  PARTITION p3 VALUES LESS THAN (30),
  7  PARTITION p4 VALUES LESS THAN (40),
  8  PARTITION p5 VALUES LESS THAN (50)
  9  );

Table created.

2、插入数据

SQL> begin
  2  for i in 0 .. 49 loop
  3  insert into t_whs_p values(i,'whs');
  4  end loop ;
  5  commit;
  6  end;
  7  /

PL/SQL procedure successfully completed.

3、创建全局索引

SQL> create index i_whs_p on t_whs_p(id);

Index created.

查看索引状态

SQL> select index_name,status from user_indexes where index_name ='I_WHS_P';

INDEX_NAME
--------------------------------------------------------------------------------
STATUS
------------------------
I_WHS_P
VALID

4、分区表move online

SQL> ALTER TABLE t_whs_p move PARTITION p5 TABLESPACE users ONLINE;
ALTER TABLE t_whs_p move PARTITION p5 TABLESPACE users ONLINE
            *
ERROR at line 1:
ORA-14809: schema does not support ONLINE MOVE PARTITION

报错，sys用户不支持move online的操作。改用普通用户测试

[oracle@cat1 ~]$ sqlplus test/test@PDB1_WHS

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Dec 2 15:45:36 2020
Version 19.3.0.0.0

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

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> CREATE TABLE t_whs_p                   
  2  (ID number, WNAME varchar2(10))        
  3  PARTITION BY RANGE (id)                
  4  (PARTITION p1 VALUES LESS THAN (10),   
  5  PARTITION p2 VALUES LESS THAN (20),    
  6  PARTITION p3 VALUES LESS THAN (30),    
  7  PARTITION p4 VALUES LESS THAN (40),    
  8  PARTITION p5 VALUES LESS THAN (50)     
  9  );                                     

Table created.

SQL> begin                                
  2  for i in 0 .. 49 loop                
  3  insert into t_whs_p values(i,'whs'); 
  4  end loop ;                           
  5  commit;                              
  6  end;                                 
  7  /                                    

PL/SQL procedure successfully completed.

SQL> create index i_whs_p on t_whs_p(id);

Index created.

SQL> select index_name,status from user_indexes where index_name ='I_WHS_P';

INDEX_NAME
--------------------------------------------------------------------------------
STATUS
------------------------
I_WHS_P
VALID

SQL> ALTER TABLE t_whs_p move PARTITION p5 TABLESPACE users ONLINE;

Table altered.

SQL> select index_name,status from user_indexes where index_name ='I_WHS_P';

INDEX_NAME
--------------------------------------------------------------------------------
STATUS
------------------------
I_WHS_P
VALID         <---索引还是正常状态

move online测试成功。

5、不加online测试：

SQL> ALTER TABLE t_whs_p move PARTITION p5 TABLESPACE users;

Table altered.

SQL> select index_name,status from user_indexes where index_name ='I_WHS_P';

INDEX_NAME
--------------------------------------------------------------------------------
STATUS
------------------------
I_WHS_P
UNUSABLE     <---索引非正常状态