每到年底对于DBA而言一个管理就是检查分区表是否有新一年的分区，确保新的一年数据不溢出，以下是某个分区表添加分区的示例，添加分区的同时我们需要确保全局索引正常：

以TAB_JASON表为例：

-- Create table

create table TAB_JASON

(

  js_mxjl_id     NUMBER not null,

  js_lsh_id      VARCHAR2(20) not null,

  pat_id         VARCHAR2(20) not null,

  fpfylx_id      VARCHAR2(20),

  je             NUMBER(20,4),

  zxt_id         VARCHAR2(20) not null,

  fb_id          VARCHAR2(20),

  fy_id          VARCHAR2(20),

  cjsj           DATE not null,

  cjr            VARCHAR2(20) not null,

  zhgxsj         DATE not null,

  zhgxr          VARCHAR2(20) not null,

  patition_field NUMBER default to_number(to_char(sysdate,'yyyy'))

)

partition by range (PATITION_FIELD)

subpartition by hash (PAT_ID)

(

  partition INVOICE_COST_PART08 values less than (2023)

    tablespace TBS_JASON

    pctfree 10

    initrans 1

    maxtrans 255

  (

    subpartition SYS_SUBP725 tablespace TBS_JASON,

    subpartition SYS_SUBP726 tablespace TBS_JASON,

    subpartition SYS_SUBP727 tablespace TBS_JASON,

    subpartition SYS_SUBP728 tablespace TBS_JASON,

    subpartition SYS_SUBP729 tablespace TBS_JASON,

    subpartition SYS_SUBP730 tablespace TBS_JASON,

    subpartition SYS_SUBP731 tablespace TBS_JASON,

    subpartition SYS_SUBP732 tablespace TBS_JASON,

    subpartition SYS_SUBP733 tablespace TBS_JASON,

    subpartition SYS_SUBP734 tablespace TBS_JASON,

    subpartition SYS_SUBP735 tablespace TBS_JASON,

    subpartition SYS_SUBP736 tablespace TBS_JASON

  ),

  partition INVOICE_COST_PART09 values less than (2024)

    tablespace TBS_JASON

    pctfree 10

    initrans 1

    maxtrans 255

  (

    subpartition SYS_SUBP737 tablespace TBS_JASON,

    subpartition SYS_SUBP738 tablespace TBS_JASON,

    subpartition SYS_SUBP739 tablespace TBS_JASON,

    subpartition SYS_SUBP740 tablespace TBS_JASON,

    subpartition SYS_SUBP741 tablespace TBS_JASON,

    subpartition SYS_SUBP742 tablespace TBS_JASON,

    subpartition SYS_SUBP743 tablespace TBS_JASON,

    subpartition SYS_SUBP744 tablespace TBS_JASON,

    subpartition SYS_SUBP745 tablespace TBS_JASON,

    subpartition SYS_SUBP746 tablespace TBS_JASON,

    subpartition SYS_SUBP747 tablespace TBS_JASON,

    subpartition SYS_SUBP748 tablespace TBS_JASON

  ),

  partition INVOICE_COST_PART10 values less than (MAXVALUE)

    tablespace TBS_JASON

    pctfree 10

    initrans 1

    maxtrans 255

  (

    subpartition SYS_SUBP749 tablespace TBS_JASON,

    subpartition SYS_SUBP750 tablespace TBS_JASON,

    subpartition SYS_SUBP751 tablespace TBS_JASON,

    subpartition SYS_SUBP752 tablespace TBS_JASON,

    subpartition SYS_SUBP753 tablespace TBS_JASON,

    subpartition SYS_SUBP754 tablespace TBS_JASON,

    subpartition SYS_SUBP755 tablespace TBS_JASON,

    subpartition SYS_SUBP756 tablespace TBS_JASON,

    subpartition SYS_SUBP757 tablespace TBS_JASON,

    subpartition SYS_SUBP758 tablespace TBS_JASON,

    subpartition SYS_SUBP759 tablespace TBS_JASON,

    subpartition SYS_SUBP760 tablespace TBS_JASON

  )

);

-- Create/Recreate indexes 

create index IDX_MZFPMX_CJSJ on TAB_JASON (CJSJ)

  tablespace TBS_JASON

  pctfree 10

  initrans 2

  maxtrans 255

  storage

  (

    initial 64K

    next 1M

    minextents 1

    maxextents unlimited

  );

create index JS_LSH_ID_IDX on TAB_JASON (JS_LSH_ID)

  tablespace TBS_JASON

  pctfree 10

  initrans 2

  maxtrans 255

  storage

  (

    initial 8000K

    next 1M

    minextents 1

    maxextents unlimited

  );

create index PATITION_OUT_INVOICE_COST_IDX on TAB_JASON (PATITION_FIELD)

  tablespace TBS_JASON

  pctfree 10

  initrans 2

  maxtrans 255

  storage

  (

    initial 7232K

    next 1M

    minextents 1

    maxextents unlimited

  );

-- Create/Recreate primary, unique and foreign key constraints 

alter table TAB_JASON

  add constraint PK_DTAB_JASON primary key (JS_MXJL_ID)

  using index 

  tablespace TBS_JASON

  pctfree 10

  initrans 2

  maxtrans 255

  storage

  (

    initial 5504K

    next 1M

    minextents 1

    maxextents unlimited

  );

将最后一个INVOICE_COST_PART10分区拆分成2025到2030的分区

ALTER TABLE TAB_JASON

SPLIT PARTITION INVOICE_COST_PART10 AT (2025)

INTO (

PARTITION INVOICE_COST_PART10_NEW TABLESPACE TBS_JASON,

PARTITION INVOICE_COST_MAX TABLESPACE TBS_JASON

);

ALTER TABLE TAB_JASON

SPLIT PARTITION INVOICE_COST_MAX AT (2026)

INTO (

PARTITION INVOICE_COST_PART11 TABLESPACE TBS_JASON,

PARTITION INVOICE_COST_MAX TABLESPACE TBS_JASON

);

ALTER TABLE TAB_JASON

SPLIT PARTITION INVOICE_COST_MAX AT (2027)

INTO (

PARTITION INVOICE_COST_PART12 TABLESPACE TBS_JASON,

PARTITION INVOICE_COST_MAX TABLESPACE TBS_JASON

);

ALTER TABLE TAB_JASON

SPLIT PARTITION INVOICE_COST_MAX AT (2028)

INTO (

PARTITION INVOICE_COST_PART13 TABLESPACE TBS_JASON,

PARTITION INVOICE_COST_MAX TABLESPACE TBS_JASON

);

ALTER TABLE TAB_JASON

SPLIT PARTITION INVOICE_COST_MAX AT (2029)

INTO (

PARTITION INVOICE_COST_PART14 TABLESPACE TBS_JASON,

PARTITION INVOICE_COST_MAX TABLESPACE TBS_JASON

);

ALTER TABLE TAB_JASON

SPLIT PARTITION INVOICE_COST_MAX AT (2030)

INTO (

PARTITION INVOICE_COST_PART15 TABLESPACE TBS_JASON,

PARTITION INVOICE_COST_MAX TABLESPACE TBS_JASON

);

拆分的进度可以通过v$session_longops进行查看具体语句如下：

SELECT sid, serial#, opname,TO_CHAR(start_time,'HH24:MI:SS') AS start_time,(sofar/totalwork)*100 AS PERCENT_COMPLETE,ELAPSED_SECONDS FROM v$session_longops;

进一步检查索引状态，对于不可用的索引及时重建

SELECT table_name,index_name,index_type,degree,status FROM user_indexes WHERE table_name in ('TAB_JASON') order by table_name;

重建时根据主机的性能适当调大并行度加快重建速度

select 'ALTER INDEX ' || index_name || ' REBUILD ONLINE PARALLEL 4;' || '' FROM user_indexes WHERE table_name in ('TAB_JASON') and status <>'VALID' order by table_name;

重建完成需要将并行度调整回1，否则会影响后续的执行计划

select 'ALTER INDEX ' || index_name || ' PARALLEL 1;' || '' FROM user_indexes WHERE table_name in ('TAB_JASON') order by table_name;