使用在线重定义方式进行分区，这样可以保障业务的可用性，最后切换的时候会短暂锁表
1. 创建临时表 CW_MZSFMX_INTERIM
create table CWSF3.CW_MZSFMX_INTERIM
(
JLID NUMBER(10),
BMID NUMBER(4),
ZKID NUMBER(4),
BRBH VARCHAR2(28),
BRXM VARCHAR2(20),
DWDM VARCHAR2(5),
ZHID NUMBER(8),
XMID NUMBER(8),
FLM VARCHAR2(8),
TJM VARCHAR2(4),
MC VARCHAR2(100),
KZJB VARCHAR2(1),
YZLB VARCHAR2(2),
DJ NUMBER(8,2),
SL NUMBER(4),
ZFJE NUMBER(9,2),
JZJE NUMBER(9,2),
JMJE NUMBER(9,2),
ZLJE NUMBER(9,2),
ZFBL NUMBER(3,2),
YSID NUMBER(5),
JYID NUMBER(10),
SFYQ VARCHAR2(2),
ZTBZ VARCHAR2(1) default '1',
ZLHDID NUMBER(9) default 0,
JMID NUMBER(8),
SFLX VARCHAR2(1),
SFRYID NUMBER(8),
SFSJ DATE,
SPBZ VARCHAR2(1) default '0',
BZDM VARCHAR2(2),
BAFLM VARCHAR2(3)
)
partition by range(SFSJ)
(
partition CW_MZSFMX_p1 values less than(to_date('2011-01-01','yyyy-mm-dd')),
partition CW_MZSFMX_p2 values less than(to_date('2012-01-01','yyyy-mm-dd')),
partition CW_MZSFMX_p3 values less than(to_date('2013-01-01','yyyy-mm-dd')),
partition CW_MZSFMX_p4 values less than(to_date('2014-01-01','yyyy-mm-dd')),
partition CW_MZSFMX_p5 values less than(to_date('2015-01-01','yyyy-mm-dd')),
partition CW_MZSFMX_p6 values less than(to_date('2016-01-01','yyyy-mm-dd')),
partition CW_MZSFMX_p7 values less than(to_date('2017-01-01','yyyy-mm-dd')),
partition CW_MZSFMX_p8 values less than(to_date('2018-01-01','yyyy-mm-dd')),
partition CW_MZSFMX_p9 values less than(to_date('2019-01-01','yyyy-mm-dd')),
partition CW_MZSFMX_p10 values less than(to_date('2020-01-01','yyyy-mm-dd')),
partition CW_MZSFMX_p11 values less than(to_date('2021-01-01','yyyy-mm-dd')),
partition CW_MZSFMX_p12 values less than(to_date('2022-01-01','yyyy-mm-dd')),
partition CW_MZSFMX_p13 values less than(to_date('2023-01-01','yyyy-mm-dd')),
partition CW_MZSFMX_p14 values less than(to_date('2024-01-01','yyyy-mm-dd')),
partition CW_MZSFMX_p15 values less than(to_date('2025-01-01','yyyy-mm-dd'))
);
2.Verify that the table is a candidate for online redefinition. In this case you specify that the redefinition is to be done using primary keys or pseudo-primary keys.
exec dbms_redefinition.can_redef_table('CWSF3','CW_MZSFMX');
3.Start the redefinition process.
exec dbms_redefinition.start_redef_table('CWSF3','CW_MZSFMX','CW_MZSFMX_INTERIM'); ------ 该步操作会初始化中间表数据（中间表会被插入源表数据），后面源表插入的时候不会被同步过来
4.Copy dependent objects. (Automatically create any triggers, indexes, materialized view logs, grants, and constraints on CWSF3.CW_MZSFMX_INTERIM.)
DECLARE
num_errors PLS_INTEGER;
BEGIN
DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('CWSF3','CW_MZSFMX','CW_MZSFMX_INTERIM', num_errors=>num_errors);
END;
/
5.Optionally, synchronize the interim table CW_MZSFMX_INTERIM.
BEGIN
DBMS_REDEFINITION.SYNC_INTERIM_TABLE('CWSF3','CW_MZSFMX','CW_MZSFMX_INTERIM'); --------- 开始实时同步两个表格之间的数据（通过 mlog 物化视图的方式也来实现）
END;
/
6.Complete the redefinition.
exec dbms_redefinition.finish_redef_table('CWSF3','CW_MZSFMX','CW_MZSFMX_INTERIM'); ----- 完成在线重定义
-- 完成在线重定义后，约束状态为 enable 、 novalidate ，使用下面语句改成 enable ， validate
select 'alter table cwsf3.'||table_name||' enable validate constraint '||constraint_name||';' from dba_constraints where table_name='CW_MZSFMX';
-- 查看临时表空间使用率
select TEMPORARY_TABLESPACE from dba_users where username='CWSF3';
select * from dba_temp_free_space;
-- 终止在线重定义过程
exec dbms_redefinition.abort_redef_table('CWSF3','CW_MZSFMX','CW_MZSFMX_INTERIM');
select * from dba_redefinition_objects;
select * from dba_redefinition_errors;
分区完成后收集统计信息
exec DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'CWSF3', TABNAME => 'CW_MZSFMX',ESTIMATE_PERCENT => 1,METHOD_OPT => 'for all columns size repeat',DEGREE => 8,GRANULARITY => 'ALL',CASCADE => TRUE);
