oracle在线重定义分区表

oracle在线重定义分区表

DBMS_REDEFINITION.START_REDEF_TABLE

这个过程首先会创建一个快速刷新的物化视图作为临时表，然后将源表的数据加载到临时表中，并在源表上创建物化视图日志，以支持快速刷新同步数据。

DBMS_REDEFINITION.SYNC_INTERIM_TABLE

把源表中的数据同步到临时表。

DBMS_REDEFINITION.FINISH_REDEF_TABLE

这个过程的操作步骤比较多，也是做在线重定义时需要特别注意的，但其执行时间通常是非常短的：

（1）先调用一次DBMS_REDEFINITION.SYNC_INTERIM_TABLE，同步数据到临时表；
（2）锁定源表，锁定之后表数据不再允许发生变化；
（3）再调用一次DBMS_REDEFINITION.SYNC_INTERIM_TABLE，同步数据到临时表；
（4）交换源表和临时表的表名；
（5）删除物化视图和物化视图日志；
（6）释放表锁资源。

操作步骤：

1、查看原表

select count(*) from wen_2020;

2、检测源表是否可以在线重定义

使用rowid方式：
EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE('ecard','wen_2020', DBMS_REDEFINITION.CONS_USE_ROWID);
注：使用rowid方式，会产生名为M_ROW$$的unused列，可以在重定义后删除：
ALTER TABLE 表名 DROP UNUSED COLUMNS ;
（该操作会锁表，不要在业务时间段进行操作。
  测试时，该语句会长时间锁表，原因待后续确认）

使用key方式:
EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE('ecard','wen_2020', DBMS_REDEFINITION.CONS_USE_PK);
（默认方式）

3、建立临时表

--查询最小时间
select to_char(min(dt_start_time),'YYYY-MM-DD HH24:MI:SS') from wen_2020;

2015-09-18 19:32:00

--查询最大时间
select to_char(max(dt_start_time),'YYYY-MM-DD HH24:MI:SS') from wen_2020;

2019-04-29 11:53:00

--创建临时分区表（以时间为分区）
CREATE TABLE wen_2020_temp   
( vc_batch_no                  VARCHAR2(24) not null,
  dt_start_time                DATE not null,
  dt_finish_time               DATE,
  vc_status                    CHAR(2) not null,
  int_total_count              INTEGER not null,
  int_success_count            INTEGER,
  int_fail_count               INTEGER,
  vc_upload_local_file_name    VARCHAR2(200),
  vc_upload_remote_file_name   VARCHAR2(200),
  vc_download_local_file_name  VARCHAR2(200),
  vc_download_remote_file_name VARCHAR2(200),
  vc_error_code                VARCHAR2(6),
  vc_error_msg                 VARCHAR2(200),
  dt_move_time                 DATE not null,
  vc_customs_no                VARCHAR2(3)
) 
  PARTITION BY RANGE (dt_start_time)
 (PARTITION T_2015 VALUES LESS THAN (TO_DATE('2016-1-1', 'YYYY-MM-DD')),
  PARTITION T_2016 VALUES LESS THAN (TO_DATE('2017-1-1', 'YYYY-MM-DD')),
  PARTITION T_2017 VALUES LESS THAN (TO_DATE('2018-1-1', 'YYYY-MM-DD')),
  PARTITION T_2018 VALUES LESS THAN (TO_DATE('2019-1-1', 'YYYY-MM-DD')),
  PARTITION T_2019 VALUES LESS THAN (TO_DATE('2020-1-1', 'YYYY-MM-DD')));

4、开始表的在线重定义

使用rowid方式:
exec dbms_redefinition.start_redef_table('ecard','wen_2020','wen_2020_temp',null,dbms_redefinition.cons_use_rowid);

使用key方式:
exec dbms_redefinition.start_redef_table('ecard','wen_2020','wen_2020_temp',null,dbms_redefinition.cons_use_pk);

5、复制源表的属性到临时表

declare num_errors PLS_INTEGER; 
BEGIN 
   DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('ecard','wen_2020','wen_2020_temp',
   DBMS_REDEFINITION.CONS_ORIG_PARAMS, TRUE, TRUE, TRUE, TRUE, num_errors); 
END; 
/ 

6、同步差异数据到临时表

exec dbms_redefinition.sync_interim_table('ecard','wen_2020','wen_2020_temp');

7、完成在线重定义

EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE('ecard','wen_2020','wen_2020_temp');
（该操作会短暂锁表）

8、收集表的统计信息，检查索引名、并行度等，检查无效对象

SQL> exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>'ecard',tabname=>'wen_2020',estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE,cascade=>TRUE); 
SQL> select degree,index_name,status from dba_indexes where table_name='wen_2020' and owner='ECARD'; 

9、删除临时表

drop table wen_2020_temp purge;

----在线分区结束----

总结：
1、使用rowid方式时，产生的M_ROW$$列，删除时会产生长时间锁表；（具体原因待确定）
2、在线分区仅是该包的其中一个功能，后续继续研究。
3、参考：http://mini.eastday.com/mobile/170823041640062.html

谨记：心存敬畏，行有所止。