【Oracle】Oracle给已有分区表上添加分区（含子分区）

---

一、原有分区表结构

create table TZQ_LOG_T(
  log_id           NUMBER not null,
  log_name         VARCHAR2(50),
  log_title        VARCHAR2(200),
  log_content      VARCHAR2(4000),
  log_type         NUMBER default 1,
  log_account_no   VARCHAR2(200),
  active_date      DATE default SYSDATE not null,
  delete_flag      NUMBER default 0,
  created_by       NUMBER not null,
  creation_date    DATE default SYSDATE not null,
  last_updated_by  NUMBER not null,
  last_update_date DATE default SYSDATE not null,
  description      VARCHAR2(4000),
)
partition    by range (CREATION_DATE)
subpartition by list  (log_type)
(
  partition TZQ_LOG_T_P1 values less than (TO_DATE(' 2014-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
  (
    subpartition TZQ_LOG_T_P1_SUBP1 values (1),
    subpartition TZQ_LOG_T_P1_SUBP2 values (2)
  ),
  partition TZQ_LOG_T_P2 values less than (TO_DATE(' 2015-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
  (
    subpartition TZQ_LOG_T_P2_SUBP1 values (1),
    subpartition TZQ_LOG_T_P2_SUBP2 values (2)
  ),
  partition TZQ_LOG_T_P3 values less than (TO_DATE(' 2016-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
  (
    subpartition TZQ_LOG_T_P3_SUBP1 values (1),
    subpartition TZQ_LOG_T_P3_SUBP2 values (2)
  ),
  partition TZQ_LOG_T_P4 values less than (TO_DATE(' 2017-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
  (
    subpartition TZQ_LOG_T_P4_SUBP1 values (1),
    subpartition TZQ_LOG_T_P4_SUBP2 values (2)
  ),
  partition TZQ_LOG_T_P5 values less than (TO_DATE(' 2018-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
  (
    subpartition TZQ_LOG_T_P5_SUBP1 values (1),
    subpartition TZQ_LOG_T_P5_SUBP2 values (2)
  ),
  partition TZQ_LOG_T_P6 values less than (TO_DATE(' 2019-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
  (
    subpartition TZQ_LOG_T_P6_SUBP1 values (1),
    subpartition TZQ_LOG_T_P6_SUBP2 values (2)
  ),
  partition TZQ_LOG_T_P7 values less than (TO_DATE(' 2020-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
  (
    subpartition TZQ_LOG_T_P7_SUBP1 values (1),
    subpartition TZQ_LOG_T_P7_SUBP2 values (2)
  ),
  partition TZQ_LOG_T_P8 values less than (TO_DATE(' 2021-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
  (
    subpartition TZQ_LOG_T_P8_SUBP1 values (1),
    subpartition TZQ_LOG_T_P8_SUBP2 values (2)
  ),
  partition TZQ_LOG_T_P9 values less than (TO_DATE(' 2022-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
  (
    subpartition TZQ_LOG_T_P9_SUBP1 values (1),
    subpartition TZQ_LOG_T_P9_SUBP2 values (2)
  ),
  partition TZQ_LOG_T_P10 values less than (TO_DATE(' 2023-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
  (
    subpartition TZQ_LOG_T_P10_SUBP1 values (1),
    subpartition TZQ_LOG_T_P10_SUBP2 values (2)
  )
);
COMMENT ON TABLE  TZQ_LOG_T IS 'TZQ日志表';
COMMENT ON COLUMN TZQ_LOG_T.LOG_ID IS '日志表ID,主键';
COMMENT ON COLUMN TZQ_LOG_T.LOG_NAME IS '日志名';
COMMENT ON COLUMN TZQ_LOG_T.LOG_TITLE IS '日志标题';
COMMENT ON COLUMN TZQ_LOG_T.LOG_CONTENT IS '日志内容';
COMMENT ON COLUMN TZQ_LOG_T.LOG_TYPE IS '日志类型：1-超级日志；2-普通日志';
COMMENT ON COLUMN TZQ_LOG_T.LOG_ACCOUNT_NO IS '日志账户编号';
COMMENT ON COLUMN TZQ_LOG_T.ACTIVE_DATE IS '生效日期';
COMMENT ON COLUMN TZQ_LOG_T.DELETE_FLAG IS '删除标识：记录该记录是否删除，Y/N';
COMMENT ON COLUMN TZQ_LOG_T.CREATED_BY IS '创建人: IT审计字段';
COMMENT ON COLUMN TZQ_LOG_T.CREATION_DATE IS '创建时间:IT审计字段';
COMMENT ON COLUMN TZQ_LOG_T.LAST_UPDATED_BY IS '最后修改人:IT审计字段';
COMMENT ON COLUMN TZQ_LOG_T.LAST_UPDATE_DATE IS '最后修改时间:IT审计字段';
COMMENT ON COLUMN TZQ_LOG_T.DESCRIPTION IS '对本条记录的说明:IT审计字段';
/* INDEX */
ALTER  TABLE TZQ_LOG_T ADD CONSTRAINT PK_TZQ_LOG_T PRIMARY KEY (LOG_ID);
CREATE UNIQUE INDEX UK_TZQ_LOG_1 ON TZQ_LOG_T (CASE  WHEN (DELETE_FLAG=0 AND LOG_TYPE=1) THEN LOG_ACCOUNT_NO ELSE NULL END, CASE  WHEN (DELETE_FLAG=0 AND LOG_TYPE=1) THEN ACTIVE_DATE ELSE NULL END);
CREATE INDEX IDX_TZQ_LOG_1 ON TZQ_LOG_T (LOG_TYPE);
CREATE INDEX IDX_TZQ_LOG_2 ON TZQ_LOG_T (DELETE_FLAG);
CREATE INDEX IDX_TZQ_LOG_3 ON TZQ_LOG_T (CREATION_DATE);
CREATE INDEX IDX_TZQ_LOG_4 ON TZQ_LOG_T (LAST_UPDATE_DATE);
CREATE INDEX IDX_TZQ_LOG_5 ON ETS_SWHTH_CASH_STAT_HEAD_T (LOG_ID, TO_NUMBER(LOG_ACCOUNT_NO), DELETE_FLAG, CREATION_DATE);
CREATE SEQUENCE TZQ_LOG_S;

二、添加分区（含子分区）

alter table TZQ_LOG_T
add partition TZQ_LOG_T_P12 values less than (TO_DATE(' 2025-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
(subpartition TZQ_LOG_T_P12_SUBP1 values (1),
 subpartition TZQ_LOG_T_P12_SUBP2 values (2));