年底了，分区扩了吗，ORA-14074 如何拆分

采用分区的往往设置了一个max分区，想扩分区时会遇到ORA-14704，需要用split拆分方式实现新增。

普通，没有设置max分区的，直接加即可：

create table TEST
(
  MSG_ID INTEGER,
  INSERT_TIME DATE
)
partition by range (INSERT_TIME)
(
  partition P2019 values less than (TO_DATE(' 2019-12-31 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace USERS
    pctfree 10
    initrans 1
    maxtrans 255,
  partition P2020 values less than (TO_DATE(' 2020-12-31 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace USERS
    pctfree 10
    initrans 1
    maxtrans 255
);

alter table test add partition P2022 values less than (TIMESTAMP '2022-12-31 00:00:00');

设置max的需要split

--当前表结构，有maxvalue分区
create table TEST2
(
  MSG_ID INTEGER,
  INSERT_TIME DATE
)
partition by range (INSERT_TIME)
(
  partition P2019 values less than (TO_DATE(' 2019-12-31 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace USERS
    pctfree 10
    initrans 1
    maxtrans 255,
  partition pmax values less than (maxvalue)
    tablespace USERS
    pctfree 10
    initrans 1
    maxtrans 255
);

--添加新分区（把最大的拆分）
alter table test2 split partition pmax at (to_date('2020-12-31','yyyy-mm-dd')) into (partition p2020 ,partition pmax);

--查看修改后的效果
create table TEST2
(
  MSG_ID INTEGER,
  INSERT_TIME DATE
)
partition by range (INSERT_TIME)
(
  partition P2019 values less than (TO_DATE(' 2019-12-31 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace USERS
    pctfree 10
    initrans 1
    maxtrans 255,
  partition P2020 values less than (TO_DATE(' 2020-12-31 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace USERS
    pctfree 10
    initrans 1
    maxtrans 255,
  partition PMAX values less than (MAXVALUE)
    tablespace USERS
    pctfree 10
    initrans 1
    maxtrans 255
);