--操作的用户需要有以下的权限

GRANT CONNECT, RESOURCE TO CMIGDW;

GRANT EXECUTE ON DBMS_REDEFINITION TO CMIGDW;

GRANT ALTER ANY TABLE TO CMIGDW;

GRANT DROP ANY TABLE TO CMIGDW;

GRANT LOCK ANY TABLE TO CMIGDW;

GRANT CREATE ANY TABLE TO CMIGDW;

GRANT SELECT ANY TABLE TO CMIGDW;

GRANT CREATE ANY TRIGGER TO CMIGDW;

GRANT CREATE ANY INDEX TO CMIGDW;

--需要分区的表

select * from YW_LS_CHELIANGTJXCLS;

--表结构

create table YW_LS_CHELIANGTJXCLS

(

  ZHUJIANBH  NUMBER(19) not null,

  TUJINGXCBH NUMBER(19),

  CHELIANGBH NUMBER(19),

  JINRUSJ    NUMBER(10),

  SHICHUSJ   NUMBER(10),

  JINRUJD    VARCHAR2(30),

  JINRUWD    VARCHAR2(30),

  SHICHUJD   VARCHAR2(30),

  SHICHUWD   VARCHAR2(30),

  JINRUWZ    VARCHAR2(1000),

  SHICHUWZ   VARCHAR2(1000),

  SHUJUZT    NUMBER(1) default 1,

  JINRUSD    NUMBER(6),

  SHICHUSD   NUMBER(6)

)

--创建一个与YW_LS_CHELIANGTJXCLS表字段相同，但是分区的表

create table YW_LS_CHELIANGTJXCLS2

(

  ZHUJIANBH  NUMBER(19) not null,

  TUJINGXCBH NUMBER(19),

  CHELIANGBH NUMBER(19),

  JINRUSJ    NUMBER(10),

  SHICHUSJ   NUMBER(10),

  JINRUJD    VARCHAR2(30),

  JINRUWD    VARCHAR2(30),

  SHICHUJD   VARCHAR2(30),

  SHICHUWD   VARCHAR2(30),

  JINRUWZ    VARCHAR2(1000),

  SHICHUWZ   VARCHAR2(1000),

  SHUJUZT    NUMBER(1) default 1,

  JINRUSD    NUMBER(6),

  SHICHUSD   NUMBER(6)

)

partition by range (JINRUSJ)

(

  partition YW_LS_CHELIANGTJXCLS_202005 values less than (1590940800)，

    PARTITION YW_LS_CHELIANGTJXCLS_202006 values (maxvalue)

);

--开始执行在线重定义

SQL> EXEC dbms_redefinition.start_redef_table('JGPT_JX', 'YW_LS_CHELIANGTJXCLS', 'YW_LS_CHELIANGTJXCLS2');

PL/SQL procedure successfully completed

SQL> 

SQL> DECLARE

  error_count pls_integer := 0;

 BEGIN

   dbms_redefinition.copy_table_dependents('JGPT_JX', 'YW_LS_CHELIANGTJXCLS', 'YW_LS_CHELIANGTJXCLS2',

                                           0, true, false, true, false,

                                           error_count);

   dbms_output.put_line('errors := ' || to_char(error_count));

 END;

 /

PL/SQL procedure successfully completed

--结束在线重定义

SQL> EXEC  dbms_redefinition.finish_redef_table('JGPT_JX', 'YW_LS_CHELIANGTJXCLS', 'YW_LS_CHELIANGTJXCLS2');

PL/SQL procedure successfully completed

SQL> select * from  YW_LS_CHELIANGTJXCLS PARTITION (YW_LS_CHELIANGTJXCLS_202005);

SQL> select * from YW_LS_CHELIANGTJXCLS2

-- 删除中间表

DROP TABLE YW_LS_CHELIANGTJXCLS2

--创建完最后的分区是maxvalue,要拆分

  ALTER TABLE YW_LS_CHELIANGTJXCLS SPLIT PARTITION YW_LS_CHELIANGTJXCLS_202006  at (1593532800) INTO (PARTITION YW_LS_CHELIANGTJXCLS_202006 , PARTITION YW_LS_CHELIANGTJXCLS_202007);

--拆分完要查看数据是否都在07的分区里 

select  count(*) from yw_ls_cheliangtjxcls  partition (YW_LS_CHELIANGTJXCLS_202006)；

select  count(*) from yw_ls_cheliangtjxcls  partition (YW_LS_CHELIANGTJXCLS_202007)；

--如果分07里没有数据则删掉

--然后把分出来的 YW_LS_CHELIANGTJXCLS_202007分区删掉重新创建，因为分出来的07分区是默认存储最大值maxvalue,所以删除重建，然后添加到生成分区的存储过程脚本中，每月自动生产当前月分区。

alter table yw_ls_cheliangtjxcls drop partition YW_LS_CHELIANGTJXCLS_202007 

alter table YW_LS_CHELIANGTJXCLS   add partition YW_LS_CHELIANGTJXCLS_202007 values  less than (1596211200) tablespace tbsp_his_data_202004 pctfree 10 initrans 1 maxtrans 255