Oracle 对旧表创建表分区

首先确认登录用户是否有执行dbms_redefinition的权限，如果没有，需要登录sys授予权限

grant execute on dbms_redefinition to Operation_Develop_Cnemc

 

--当前用户Operation_Develop_Cnemc
--表空间cnemc

--对旧表创建表分区
select count(*) from sys_accesslog
--1.需要验证该表是否可以进行联机重新定义，并指定使用主键执行重新定义
begin 
  DBMS_REDEFINITION.CAN_REDEF_TABLE('Operation_Develop_Cnemc', 'sys_accesslog', DBMS_REDEFINITION.CONS_USE_PK);
end;

--2.创建中间表，定义分区
 CREATE TABLE sys_accesslog_new 
(
  id           NUMBER not null,
  permissionid VARCHAR2(38),
  accessuserid VARCHAR2(38),
  accesstime   DATE,
  type         VARCHAR2(38)
)
 PARTITION BY RANGE (accesstime) 
(
PARTITION P1 VALUES LESS THAN (TO_DATE('2017-01-01', 'YYYY-MM-DD')) tablespace cnemc, 
PARTITION P2 VALUES LESS THAN (TO_DATE('2018-01-01', 'YYYY-MM-DD')) tablespace cnemc, 
PARTITION P3 VALUES LESS THAN (TO_DATE('2019-01-01', 'YYYY-MM-DD')) tablespace cnemc, 
PARTITION P4 VALUES LESS THAN (TO_DATE('2020-01-01', 'YYYY-MM-DD')) tablespace cnemc, 
PARTITION P5 VALUES LESS THAN (TO_DATE('2021-01-01', 'YYYY-MM-DD')) tablespace cnemc,
PARTITION P6 VALUES LESS THAN (TO_DATE('2022-01-01', 'YYYY-MM-DD')) tablespace cnemc,
PARTITION P7 VALUES LESS THAN (TO_DATE('2023-01-01', 'YYYY-MM-DD')) tablespace cnemc,
PARTITION P8 VALUES LESS THAN (TO_DATE('2024-01-01', 'YYYY-MM-DD')) tablespace cnemc,
PARTITION P9 VALUES LESS THAN (TO_DATE('2025-01-01', 'YYYY-MM-DD')) tablespace cnemc,
PARTITION P10 VALUES LESS THAN (TO_DATE('2026-01-01', 'YYYY-MM-DD')) tablespace cnemc,
PARTITION P11 VALUES LESS THAN (TO_DATE('2027-01-01', 'YYYY-MM-DD')) tablespace cnemc,
PARTITION P12 VALUES LESS THAN (TO_DATE('2028-01-01', 'YYYY-MM-DD')) tablespace cnemc,
PARTITION P13 VALUES LESS THAN (TO_DATE('2029-01-01', 'YYYY-MM-DD')) tablespace cnemc,
PARTITION P14 VALUES LESS THAN (TO_DATE('2030-01-01', 'YYYY-MM-DD')) tablespace cnemc,
PARTITION P15 VALUES LESS THAN (MAXVALUE) tablespace cnemc
);

     
--3.同步数据到中间表
--快速组装语句：
select Listagg(column_name||' '||column_name, ',') WITHIN GROUP(ORDER BY column_name)    
from user_tab_columns   
where table_name = upper('sys_accesslog')   
order by column_name 

begin 
   DBMS_REDEFINITION.START_REDEF_TABLE('Operation_Develop_Cnemc', 'sys_accesslog', 'sys_accesslog_new','ACCESSTIME ACCESSTIME,ACCESSUSERID ACCESSUSERID,ID ID,PERMISSIONID PERMISSIONID,TYPE TYPE', DBMS_REDEFINITION.CONS_USE_PK);
end;

--4.在中间表上自动创建触发器、索引和约束

declare no_errors int := 0;
BEGIN
  DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('Operation_Develop_Cnemc', 'sys_accesslog','sys_accesslog_new',1, TRUE, TRUE, TRUE, FALSE, no_errors);
  DBMS_OUTPUT.put_line ('No of errors during copy of dependents ' || no_errors );
END;

--5.同步中间表？
BEGIN                       
  DBMS_REDEFINITION.SYNC_INTERIM_TABLE('Operation_Develop_Cnemc', 'sys_accesslog', 'sys_accesslog_new');
END;

--6.完成重组
begin
   DBMS_REDEFINITION.FINISH_REDEF_TABLE('Operation_Develop_Cnemc', 'sys_accesslog', 'sys_accesslog_new');
end;

--7.删除中间表
DROP TABLE sys_accesslog_new CASCADE CONSTRAINTS PURGE;


select to_char(accesstime,'yyyy'),count(*) from sys_accesslog
group by to_char(accesstime,'yyyy');


select count(*) from sys_accesslog PARTITION (P1);
select count(*) from sys_accesslog PARTITION (P2);
select count(*) from sys_accesslog PARTITION (P3);
select count(*) from sys_accesslog PARTITION (P4);

select count(*) from sys_accesslog
where to_char(accesstime,'yyyy')='2020'