对oracle表进行分区,Oracle 对旧表创建表分区

--当前用户Operation_Develop_Cnemc

--表空间cnemc--对旧表创建表分区

select count(*) fromsys_accesslog--1.需要验证该表是否可以进行联机重新定义，并指定使用主键执行重新定义

beginDBMS_REDEFINITION.CAN_REDEF_TABLE('Operation_Develop_Cnemc', 'sys_accesslog', DBMS_REDEFINITION.CONS_USE_PK);end;--2.创建中间表，定义分区

CREATE TABLEsys_accesslog_new

(

idNUMBER not null,

permissionidVARCHAR2(38),

accessuseridVARCHAR2(38),

accesstime DATE,

typeVARCHAR2(38)

)

PARTITIONBYRANGE (accesstime)

(

PARTITION P1VALUES LESS THAN (TO_DATE('2017-01-01', 'YYYY-MM-DD')) tablespace cnemc,

PARTITION P2VALUES LESS THAN (TO_DATE('2018-01-01', 'YYYY-MM-DD')) tablespace cnemc,

PARTITION P3VALUES LESS THAN (TO_DATE('2019-01-01', 'YYYY-MM-DD')) tablespace cnemc,

PARTITION P4VALUES LESS THAN (TO_DATE('2020-01-01', 'YYYY-MM-DD')) tablespace cnemc,

PARTITION P5VALUES LESS THAN (TO_DATE('2021-01-01', 'YYYY-MM-DD')) tablespace cnemc,

PARTITION P6VALUES LESS THAN (TO_DATE('2022-01-01', 'YYYY-MM-DD')) tablespace cnemc,

PARTITION P7VALUES LESS THAN (TO_DATE('2023-01-01', 'YYYY-MM-DD')) tablespace cnemc,

PARTITION P8VALUES LESS THAN (TO_DATE('2024-01-01', 'YYYY-MM-DD')) tablespace cnemc,

PARTITION P9VALUES LESS THAN (TO_DATE('2025-01-01', 'YYYY-MM-DD')) tablespace cnemc,

PARTITION P10VALUES LESS THAN (TO_DATE('2026-01-01', 'YYYY-MM-DD')) tablespace cnemc,

PARTITION P11VALUES LESS THAN (TO_DATE('2027-01-01', 'YYYY-MM-DD')) tablespace cnemc,

PARTITION P12VALUES LESS THAN (TO_DATE('2028-01-01', 'YYYY-MM-DD')) tablespace cnemc,

PARTITION P13VALUES LESS THAN (TO_DATE('2029-01-01', 'YYYY-MM-DD')) tablespace cnemc,

PARTITION P14VALUES LESS THAN (TO_DATE('2030-01-01', 'YYYY-MM-DD')) tablespace cnemc,

PARTITION P15VALUESLESS THAN (MAXVALUE) tablespace cnemc

);--3.同步数据到中间表--快速组装语句：

select Listagg(column_name||' '||column_name, ',') WITHIN GROUP(ORDER BYcolumn_name)fromuser_tab_columnswhere table_name = upper('sys_accesslog')order bycolumn_namebeginDBMS_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;BEGINDBMS_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.同步中间表？

BEGINDBMS_REDEFINITION.SYNC_INTERIM_TABLE('Operation_Develop_Cnemc', 'sys_accesslog', 'sys_accesslog_new');END;--6.完成重组

beginDBMS_REDEFINITION.FINISH_REDEF_TABLE('Operation_Develop_Cnemc', 'sys_accesslog', 'sys_accesslog_new');end;--7.删除中间表

DROP TABLE sys_accesslog_new CASCADECONSTRAINTS PURGE;select to_char(accesstime,'yyyy'),count(*) fromsys_accessloggroup by to_char(accesstime,'yyyy');select count(*) fromsys_accesslog PARTITION (P1);select count(*) fromsys_accesslog PARTITION (P2);select count(*) fromsys_accesslog PARTITION (P3);select count(*) fromsys_accesslog PARTITION (P4);select count(*) fromsys_accesslogwhere to_char(accesstime,'yyyy')='2020'