分区表2025年添加新分区

每到年底对于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_JASONSPLIT 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_JASONSPLIT PARTITION INVOICE_COST_MAX AT (2026)INTO (PARTITION INVOICE_COST_PART11 TABLESPACE TBS_JASON,PARTITION INVOICE_COST_MAX TABLESPACE TBS_JASON);ALTER TABLE TAB_JASONSPLIT PARTITION INVOICE_COST_MAX AT (2027)INTO (PARTITION INVOICE_COST_PART12 TABLESPACE TBS_JASON,PARTITION INVOICE_COST_MAX TABLESPACE TBS_JASON);ALTER TABLE TAB_JASONSPLIT PARTITION INVOICE_COST_MAX AT (2028)INTO (PARTITION INVOICE_COST_PART13 TABLESPACE TBS_JASON,PARTITION INVOICE_COST_MAX TABLESPACE TBS_JASON);ALTER TABLE TAB_JASONSPLIT PARTITION INVOICE_COST_MAX AT (2029)INTO (PARTITION INVOICE_COST_PART14 TABLESPACE TBS_JASON,PARTITION INVOICE_COST_MAX TABLESPACE TBS_JASON);ALTER TABLE TAB_JASONSPLIT 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;