Oracle数据库自动删除或增加表分区（写日志版）

spool test.log
-- 检查 TBL_PROC_ERRMSG 表是否存在，如果不存在则创建
DECLARE
    v_table_count NUMBER;
BEGIN
    SELECT COUNT(*)
    INTO v_table_count
    FROM all_tables
    WHERE table_name = 'TBL_PROC_ERRMSG';

    IF v_table_count = 0 THEN
        EXECUTE IMMEDIATE 'CREATE TABLE TBL_PROC_ERRMSG
        (
            PROC_NAME VARCHAR2(100),
            ERR_LINE VARCHAR2(1000),
            ERR_CODE VARCHAR2(10),
            MSG VARCHAR2(4000),
            CRT_TM DATE DEFAULT SYSDATE
        )';
    END IF;
END;
/

-- 检查 AUTO_ADD_TABLEPART 表是否存在，如果不存在则创建
DECLARE
    v_table_count NUMBER;
BEGIN
    SELECT COUNT(*)
    INTO v_table_count
    FROM all_tables
    WHERE table_name = 'AUTO_ADD_TABLEPART';

    IF v_table_count = 0 THEN
        EXECUTE IMMEDIATE 'CREATE TABLE AUTO_ADD_TABLEPART
        (
            table_name VARCHAR2(50),
            execute_sql VARCHAR2(200),
            message VARCHAR2(200),
            addtime DATE DEFAULT SYSDATE
        )';
    END IF;
END;
/
--保存错误信息的存过
CREATE OR REPLACE PROCEDURE PROC_SAVE_ERRMSG(
PROCNAME IN VARCHAR2,
ERRORLINE IN VARCHAR2,
ERRORCODE IN VARCHAR2,
MSG IN VARCHAR2) IS
/*必须要使用自治事务，否则commit会影响调用程序事务*/
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO TBL_PROC_ERRMSG
(PROC_NAME,ERR_LINE, ERR_CODE, MSG)
VALUES
(PROCNAME,ERRORLINE, ERRORCODE, MSG);
COMMIT ;
END ;
/
DECLARE
 o_dept NUMBER;
BEGIN
SELECT count(1) INTO o_dept FROM user_tables t where t.table_name='ACS_DINFO_TF_A';
    IF o_dept = 1 THEN
        execute immediate 'DROP  table ACS_DINFO_TF_A';
    END IF;
END;
/
--创建临时表
create table ACS_DINFO_TF_A tablespace EVCOM_DATA as select * from ACS_DINFO_TF;

commit;
--只有ACS_DINFO_TF_A已经存在才删除ACS_DINFO_TF
DECLARE
 o_dept NUMBER;
BEGIN
SELECT count(1) INTO o_dept FROM user_tables t where t.table_name='ACS_DINFO_TF_A';
    IF o_dept = 1 THEN
        execute immediate 'DROP table ACS_DINFO_TF';
    END IF;
END;
/

--重建ACS_DINFO_TF表为分区表，分区是2021年至2030年，以月为粒度

CREATE TABLE ACS_DINFO_TF
(
  acs_id           INTEGER not null,
  acs_functioncode INTEGER,
  acs_time         DATE not null,
  acs_recvtime     DATE not null,
  acs_uid          VARCHAR2(50),
  acs_name         VARCHAR2(30),
  acs_sex          VARCHAR2(10),
  acs_department   VARCHAR2(50),
  acs_event        VARCHAR2(500),
  acs_doorid       VARCHAR2(50),
  acs_stationid    INTEGER
)
PARTITION BY RANGE (acs_time)          -- 按 acs_time 做范围分区
(
partition ACS_DINFO_TF_202101   values less than (to_date('2021-02-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),
partition ACS_DINFO_TF_202102   values less than (to_date('2021-03-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),
partition ACS_DINFO_TF_202103   values less than (to_date('2021-04-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),
partition ACS_DINFO_TF_202104   values less than (to_date('2021-05-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),
partition ACS_DINFO_TF_202105   values less than (to_date('2021-06-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),
partition ACS_DINFO_TF_202106   values less than (to_date('2021-07-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),
partition ACS_DINFO_TF_202107   values less than (to_date('2021-08-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),
partition ACS_DINFO_TF_202108   values less than (to_date('2021-09-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),
partition ACS_DINFO_TF_202109   values less than (to_date('2021-10-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),
partition ACS_DINFO_TF_202110   values less than (to_date('2021-11-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),
partition ACS_DINFO_TF_202111   values less than (to_date('2021-12-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),
partition ACS_DINFO_TF_202112   values less than (to_date('2022-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),
partition ACS_DINFO_TF_202201   values less than (to_date('2022-02-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),
partition ACS_DINFO_TF_202202   values less than (to_date('2022-03-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),
partition ACS_DINFO_TF_202203   values less than (to_date('2022-04-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),
partition ACS_DINFO_TF_202204   values less than (to_date('2022-05-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),
partition ACS_DINFO_TF_202205   values less than (to_date('2022-06-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),
partition ACS_DINFO_TF_202206   values less than (to_date('2022-07-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),
partition ACS_DINFO_TF_202207   values less than (to_date('2022-08-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),
partition ACS_DINFO_TF_202208   values less than (to_date('2022-09-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),
partition ACS_DINFO_TF_202209   values less than (to_date('2022-10-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),
partition ACS_DINFO_TF_202210   values less than (to_date('2022-11-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),
partition ACS_DINFO_TF_202211   values less than (to_date('2022-12-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),
partition ACS_DINFO_TF_202212   values less than (to_date('2023-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),
partition ACS_DINFO_TF_202301   values less than (to_date('2023-02-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),
partition ACS_DINFO_TF_202302   values less than (to_date('2023-03-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),
partition ACS_DINFO_TF_202303   values less than (to_date('2023-04-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),
partition ACS_DINFO_TF_202304   values less than (to_date('2023-05-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),
partition ACS_DINFO_TF_202305   values less than (to_date('2023-06-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),
partition ACS_DINFO_TF_202306   values less than (to_date('2023-07-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),
partition ACS_DINFO_TF_202307   values less than (to_date('2023-08-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),
partition ACS_DINFO_TF_202308   values less than (to_date('2023-09-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),
partition ACS_DINFO_TF_202309   values less than (to_date('2023-10-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),
partition ACS_DINFO_TF_202310   values less than (to_date('2023-11-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),
partition ACS_DINFO_TF_202311   values less than (to_date('2023-12-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),
partition ACS_DINFO_TF_202312   values less than (to_date('2024-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),
partition ACS_DINFO_TF_202401   values less than (to_date('2024-02-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),
partition ACS_DINFO_TF_202402   values less than (to_date('2024-03-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),
partition ACS_DINFO_TF_202403   values less than (to_date('2024-04-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),
partition ACS_DINFO_TF_202404   values less than (to_date('2024-05-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),
partition ACS_DINFO_TF_202405   values less than (to_date('2024-06-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),
partition ACS_DINFO_TF_202406   values less than (to_date('2024-07-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),
partition ACS_DINFO_TF_202407   values less than (to_date('2024-08-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),
partition ACS_DINFO_TF_202408   values less than (to_date('2024-09-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),
partition ACS_DINFO_TF_202409   values less than (to_date('2024-10-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),
partition ACS_DINFO_TF_202410   values less than (to_date('2024-11-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),
partition ACS_DINFO_TF_202411   values less than (to_date('2024-12-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),
partition ACS_DINFO_TF_202412   values less than (to_date('2025-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),
partition ACS_DINFO_TF_202501   values less than (to_date('2025-02-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),
partition ACS_DINFO_TF_202502   values less than (to_date('2025-03-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),
partition ACS_DINFO_TF_202503   values less than (to_date('2025-04-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),
partition ACS_DINFO_TF_202504   values less than (to_date('2025-05-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),
partition ACS_DINFO_TF_202505   values less than (to_date('2025-06-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),
partition ACS_DINFO_TF_202506   values less than (to_date('2025-07-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),
partition ACS_DINFO_TF_202507   values less than (to_date('2025-08-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),
partition ACS_DINFO_TF_202508   values less than (to_date('2025-09-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),
partition ACS_DINFO_TF_202509   values less than (to_date('2025-10-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),
partition ACS_DINFO_TF_202510   values less than (to_date('2025-11-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),
partition ACS_DINFO_TF_202511   values less than (to_date('2025-12-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),
partition ACS_DINFO_TF_202512   values less than (to_date('2026-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),
partition ACS_DINFO_TF_202601   values less than (to_date('2026-02-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),
partition ACS_DINFO_TF_202602   values less than (to_date('2026-03-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),
partition ACS_DINFO_TF_202603   values less than (to_date('2026-04-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),
partition ACS_DINFO_TF_202604   values less than (to_date('2026-05-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),
partition ACS_DINFO_TF_202605   values less than (to_date('2026-06-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),
partition ACS_DINFO_TF_202606   values less than (to_date('2026-07-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),
partition ACS_DINFO_TF_202607   values less than (to_date('2026-08-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),
partition ACS_DINFO_TF_202608   values less than (to_date('2026-09-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),
partition ACS_DINFO_TF_202609   values less than (to_date('2026-10-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),
partition ACS_DINFO_TF_202610   values less than (to_date('2026-11-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),
partition ACS_DINFO_TF_202611   values less than (to_date('2026-12-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),
partition ACS_DINFO_TF_202612   values less than (to_date('2027-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),
partition ACS_DINFO_TF_202701   values less than (to_date('2027-02-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),
partition ACS_DINFO_TF_202702   values less than (to_date('2027-03-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),
partition ACS_DINFO_TF_202703   values less than (to_date('2027-04-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),
partition ACS_DINFO_TF_202704   values less than (to_date('2027-05-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),
partition ACS_DINFO_TF_202705   values less than (to_date('2027-06-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),
partition ACS_DINFO_TF_202706   values less than (to_date('2027-07-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),
partition ACS_DINFO_TF_202707   values less than (to_date('2027-08-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),
partition ACS_DINFO_TF_202708   values less than (to_date('2027-09-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),
partition ACS_DINFO_TF_202709   values less than (to_date('2027-10-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),
partition ACS_DINFO_TF_202710   values less than (to_date('2027-11-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),
partition ACS_DINFO_TF_202711   values less than (to_date('2027-12-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),
partition ACS_DINFO_TF_202712   values less than (to_date('2028-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),
partition ACS_DINFO_TF_202801   values less than (to_date('2028-02-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),
partition ACS_DINFO_TF_202802   values less than (to_date('2028-03-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),
partition ACS_DINFO_TF_202803   values less than (to_date('2028-04-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),
partition ACS_DINFO_TF_202804   values less than (to_date('2028-05-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),
partition ACS_DINFO_TF_202805   values less than (to_date('2028-06-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),
partition ACS_DINFO_TF_202806   values less than (to_date('2028-07-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),
partition ACS_DINFO_TF_202807   values less than (to_date('2028-08-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),
partition ACS_DINFO_TF_202808   values less than (to_date('2028-09-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),
partition ACS_DINFO_TF_202809   values less than (to_date('2028-10-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),
partition ACS_DINFO_TF_202810   values less than (to_date('2028-11-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),
partition ACS_DINFO_TF_202811   values less than (to_date('2028-12-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),
partition ACS_DINFO_TF_202812   values less than (to_date('2029-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),
partition ACS_DINFO_TF_202901   values less than (to_date('2029-02-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),
partition ACS_DINFO_TF_202902   values less than (to_date('2029-03-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),
partition ACS_DINFO_TF_202903   values less than (to_date('2029-04-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),
partition ACS_DINFO_TF_202904   values less than (to_date('2029-05-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),
partition ACS_DINFO_TF_202905   values less than (to_date('2029-06-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),
partition ACS_DINFO_TF_202906   values less than (to_date('2029-07-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),
partition ACS_DINFO_TF_202907   values less than (to_date('2029-08-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),
partition ACS_DINFO_TF_202908   values less than (to_date('2029-09-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),
partition ACS_DINFO_TF_202909   values less than (to_date('2029-10-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),
partition ACS_DINFO_TF_202910   values less than (to_date('2029-11-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),
partition ACS_DINFO_TF_202911   values less than (to_date('2029-12-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),
partition ACS_DINFO_TF_202912   values less than (to_date('2030-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),
partition ACS_DINFO_TF_203001   values less than (to_date('2030-02-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),
partition ACS_DINFO_TF_203002   values less than (to_date('2030-03-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),
partition ACS_DINFO_TF_203003   values less than (to_date('2030-04-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),
partition ACS_DINFO_TF_203004   values less than (to_date('2030-05-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),
partition ACS_DINFO_TF_203005   values less than (to_date('2030-06-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),
partition ACS_DINFO_TF_203006   values less than (to_date('2030-07-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),
partition ACS_DINFO_TF_203007   values less than (to_date('2030-08-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),
partition ACS_DINFO_TF_203008   values less than (to_date('2030-09-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),
partition ACS_DINFO_TF_203009   values less than (to_date('2030-10-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),
partition ACS_DINFO_TF_203010   values less than (to_date('2030-11-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),
partition ACS_DINFO_TF_203011   values less than (to_date('2030-12-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))，
partition ACS_DINFO_TF_203012   values less than (to_date('2031-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))
)
TABLESPACE CONFIG_DATA
  PCTFREE 10
  INITRANS 1
  MAXTRANS 255
  STORAGE
  (
    INITIAL 64K
    NEXT 1M
    MINExtENTS 1
    MAXExtENTS UNLIMITED
  );

-- 创建本地分区索引（按 acs_time）
CREATE INDEX INDX_ACS_DINFO_TF_ACS_TIME ON ACS_DINFO_TF (ACS_TIME) LOCAL  -- LOCAL 表示本地分区索引
  TABLESPACE CONFIG_INDX
  PCTFREE 10
  INITRANS 2
  MAXTRANS 255
  STORAGE
  (
    INITIAL 64K
    NEXT 1M
    MINExtENTS 1
    MAXExtENTS UNLIMITED
  );

-- 添加全局主键约束（acs_id 是独立主键，不依赖分区键）
ALTER TABLE ACS_DINFO_TF
  ADD CONSTRAINT ACS_DINFO_TF_PK PRIMARY KEY (ACS_ID)
  USING INDEX 
  TABLESPACE CONFIG_DATA
  PCTFREE 10
  INITRANS 2
  MAXTRANS 255
  STORAGE
  (
    INITIAL 64K
    NEXT 1M
    MINExtENTS 1
    MAXExtENTS UNLIMITED
  );


insert into ACS_DINFO_TF
select * from ACS_DINFO_TF_A;

--删除临时表
--DROP table ACS_DINFO_TF_A;

create or replace procedure P_ADD_DELETE_PARTITIONS
(p_keep_month int --分区保留月份
)
as
  v_sql              varchar2(300);
  VMESSAGE     VARCHAR(250);
  v_delete_part_date varchar2(64);
  v_add_part_date    varchar2(64);
  v_num               number default 0;
begin

  -- DROP指定月份之前的所有分区
  for i in (select table_name, min(partition_name) as min_part_name
              from user_tab_partitions
             where  table_name  not like '%$%'
             group by table_name) loop

    v_delete_part_date := substr(i.min_part_name,
                                 instr(i.min_part_name, '_',-1) + 1);

    while months_between(sysdate, to_date(v_delete_part_date, 'yyyymm')) > p_keep_month loop

       begin
      v_sql := 'ALTER TABLE ' || i.table_name || ' DROP PARTITION ' ||
               i.table_name || '_' || v_delete_part_date ||' update global indexes';

      execute immediate v_sql;

      VMESSAGE := 'The partition ' ||
               i.table_name || '_' || v_delete_part_date ||' of the table '||i.table_name||' was successfully deleted.';

         INSERT INTO AUTO_ADD_TABLEPART
        VALUES
          (i.table_name,
           v_sql,
           VMESSAGE,
           SYSDATE);

      EXCEPTION
        WHEN OTHERS THEN

          PROC_SAVE_ERRMSG('P_ADD_DELETE_PARTITIONS',DBMS_UTILITY.format_error_backtrace,sqlcode,SQLERRM);


      v_delete_part_date := to_char(add_months(to_date(v_delete_part_date,'yyyymm'),1),'yyyymm');
       end;
    end loop;
  end loop;
--alter table ev_combined drop partition EV_COMBINED_202103 update global indexes
  -- add  partition
  for i in (select table_name, max(partition_name) as max_part_name
              from user_tab_partitions
             where table_name  not like '%$%'
             group by table_name) loop

    v_add_part_date := substr(i.max_part_name,
                              instr(i.max_part_name, '_',-1) + 1);
  --当表的最大分区小于3个月后，就给表多新增3个月的分区
   if  months_between(to_date(v_add_part_date, 'yyyymm'),sysdate)  < 3 then
     for j in 1..3 loop
       v_num := v_num +1;
       v_add_part_date := to_char(add_months(to_date(v_add_part_date,'yyyymm'),1),'yyyymm');
        begin
        v_sql := 'ALTER TABLE ' || i.table_name || ' add PARTITION ' ||
                 i.table_name || '_' || v_add_part_date;
        execute immediate v_sql;
              VMESSAGE := 'The partition ' ||
               i.table_name || '_' || v_add_part_date ||' of the table '||i.table_name||' was successfully Add.';
                INSERT INTO AUTO_ADD_TABLEPART
        VALUES
          (i.table_name,
           v_sql,
           VMESSAGE,
           SYSDATE);

      EXCEPTION
        WHEN OTHERS THEN

          PROC_SAVE_ERRMSG('P_ADD_DELETE_PARTITIONS',DBMS_UTILITY.format_error_backtrace,sqlcode,SQLERRM);
        end;
     end loop;
    end if;
  end loop;


end;
/
declare job_id int;
begin
select max(job) into job_id from user_jobs where WHAT='p_add_delete_partitions(12);';
if job_id>0 then
dbms_job.remove(job_id);
commit;
end if;
end;
/

DECLARE
JOB NUMBER;
BEGIN
DBMS_JOB.SUBMIT(
JOB => JOB,
WHAT => 'p_add_delete_partitions(12);',
NEXT_DATE =>TRUNC(SYSDATE + 1) + 2/24,
INTERVAL => 'TRUNC(LAST_DAY(SYSDATE) + 1)',
no_parse => FALSE
);
END;
/
exit;
spool off

spool test.log-- 检查 TBL_PROC_ERRMSG 表是否存在，如果不存在则创建DECLARE v_table_count NUMBER;BEGIN SELECT COUNT(*) INTO v_table_count FROM all_tables WHERE table_name = 'TBL_PROC_ERRMSG';
IF v_table_count = 0 THEN EXECUTE IMMEDIATE 'CREATE TABLE TBL_PROC_ERRMSG ( PROC_NAME VARCHAR2(100), ERR_LINE VARCHAR2(1000), ERR_CODE VARCHAR2(10), MSG VARCHAR2(4000), CRT_TM DATE DEFAULT SYSDATE )'; END IF;END;/
-- 检查 AUTO_ADD_TABLEPART 表是否存在，如果不存在则创建DECLARE v_table_count NUMBER;BEGIN SELECT COUNT(*) INTO v_table_count FROM all_tables WHERE table_name = 'AUTO_ADD_TABLEPART';
IF v_table_count = 0 THEN EXECUTE IMMEDIATE 'CREATE TABLE AUTO_ADD_TABLEPART ( table_name VARCHAR2(50), execute_sql VARCHAR2(200), message VARCHAR2(200), addtime DATE DEFAULT SYSDATE )'; END IF;END;/--保存错误信息的存过CREATE OR REPLACE PROCEDURE PROC_SAVE_ERRMSG(PROCNAME IN VARCHAR2,ERRORLINE IN VARCHAR2,ERRORCODE IN VARCHAR2,MSG IN VARCHAR2) IS/*必须要使用自治事务，否则commit会影响调用程序事务*/PRAGMA AUTONOMOUS_TRANSACTION;BEGININSERT INTO TBL_PROC_ERRMSG(PROC_NAME,ERR_LINE, ERR_CODE, MSG)VALUES(PROCNAME,ERRORLINE, ERRORCODE, MSG);COMMIT ;END ;/DECLARE o_dept NUMBER;BEGINSELECT count(1) INTO o_dept FROM user_tables t where t.table_name='ACS_DINFO_TF_A';IF o_dept = 1 THENexecute immediate 'DROP table ACS_DINFO_TF_A';END IF;END;/--创建临时表create table ACS_DINFO_TF_A tablespace EVCOM_DATA as select * from ACS_DINFO_TF;
commit;--只有ACS_DINFO_TF_A已经存在才删除ACS_DINFO_TFDECLARE o_dept NUMBER;BEGINSELECT count(1) INTO o_dept FROM user_tables t where t.table_name='ACS_DINFO_TF_A';IF o_dept = 1 THENexecute immediate 'DROP table ACS_DINFO_TF';END IF;END;/
--重建ACS_DINFO_TF表为分区表，分区是2021年至2030年，以月为粒度
CREATE TABLE ACS_DINFO_TF( acs_id INTEGER not null, acs_functioncode INTEGER, acs_time DATE not null, acs_recvtime DATE not null, acs_uid VARCHAR2(50), acs_name VARCHAR2(30), acs_sex VARCHAR2(10), acs_department VARCHAR2(50), acs_event VARCHAR2(500), acs_doorid VARCHAR2(50), acs_stationid INTEGER)PARTITION BY RANGE (acs_time) -- 按 acs_time 做范围分区(partition ACS_DINFO_TF_202101 values less than (to_date('2021-02-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),partition ACS_DINFO_TF_202102 values less than (to_date('2021-03-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),partition ACS_DINFO_TF_202103 values less than (to_date('2021-04-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),partition ACS_DINFO_TF_202104 values less than (to_date('2021-05-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),partition ACS_DINFO_TF_202105 values less than (to_date('2021-06-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),partition ACS_DINFO_TF_202106 values less than (to_date('2021-07-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),partition ACS_DINFO_TF_202107 values less than (to_date('2021-08-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),partition ACS_DINFO_TF_202108 values less than (to_date('2021-09-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),partition ACS_DINFO_TF_202109 values less than (to_date('2021-10-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),partition ACS_DINFO_TF_202110 values less than (to_date('2021-11-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),partition ACS_DINFO_TF_202111 values less than (to_date('2021-12-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),partition ACS_DINFO_TF_202112 values less than (to_date('2022-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),partition ACS_DINFO_TF_202201 values less than (to_date('2022-02-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),partition ACS_DINFO_TF_202202 values less than (to_date('2022-03-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),partition ACS_DINFO_TF_202203 values less than (to_date('2022-04-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),partition ACS_DINFO_TF_202204 values less than (to_date('2022-05-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),partition ACS_DINFO_TF_202205 values less than (to_date('2022-06-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),partition ACS_DINFO_TF_202206 values less than (to_date('2022-07-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),partition ACS_DINFO_TF_202207 values less than (to_date('2022-08-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),partition ACS_DINFO_TF_202208 values less than (to_date('2022-09-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),partition ACS_DINFO_TF_202209 values less than (to_date('2022-10-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),partition ACS_DINFO_TF_202210 values less than (to_date('2022-11-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),partition ACS_DINFO_TF_202211 values less than (to_date('2022-12-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),partition ACS_DINFO_TF_202212 values less than (to_date('2023-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),partition ACS_DINFO_TF_202301 values less than (to_date('2023-02-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),partition ACS_DINFO_TF_202302 values less than (to_date('2023-03-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),partition ACS_DINFO_TF_202303 values less than (to_date('2023-04-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),partition ACS_DINFO_TF_202304 values less than (to_date('2023-05-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),partition ACS_DINFO_TF_202305 values less than (to_date('2023-06-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),partition ACS_DINFO_TF_202306 values less than (to_date('2023-07-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),partition ACS_DINFO_TF_202307 values less than (to_date('2023-08-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),partition ACS_DINFO_TF_202308 values less than (to_date('2023-09-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),partition ACS_DINFO_TF_202309 values less than (to_date('2023-10-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),partition ACS_DINFO_TF_202310 values less than (to_date('2023-11-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),partition ACS_DINFO_TF_202311 values less than (to_date('2023-12-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),partition ACS_DINFO_TF_202312 values less than (to_date('2024-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),partition ACS_DINFO_TF_202401 values less than (to_date('2024-02-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),partition ACS_DINFO_TF_202402 values less than (to_date('2024-03-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),partition ACS_DINFO_TF_202403 values less than (to_date('2024-04-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),partition ACS_DINFO_TF_202404 values less than (to_date('2024-05-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),partition ACS_DINFO_TF_202405 values less than (to_date('2024-06-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),partition ACS_DINFO_TF_202406 values less than (to_date('2024-07-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),partition ACS_DINFO_TF_202407 values less than (to_date('2024-08-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),partition ACS_DINFO_TF_202408 values less than (to_date('2024-09-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),partition ACS_DINFO_TF_202409 values less than (to_date('2024-10-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),partition ACS_DINFO_TF_202410 values less than (to_date('2024-11-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),partition ACS_DINFO_TF_202411 values less than (to_date('2024-12-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),partition ACS_DINFO_TF_202412 values less than (to_date('2025-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),partition ACS_DINFO_TF_202501 values less than (to_date('2025-02-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),partition ACS_DINFO_TF_202502 values less than (to_date('2025-03-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),partition ACS_DINFO_TF_202503 values less than (to_date('2025-04-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),partition ACS_DINFO_TF_202504 values less than (to_date('2025-05-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),partition ACS_DINFO_TF_202505 values less than (to_date('2025-06-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),partition ACS_DINFO_TF_202506 values less than (to_date('2025-07-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),partition ACS_DINFO_TF_202507 values less than (to_date('2025-08-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),partition ACS_DINFO_TF_202508 values less than (to_date('2025-09-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),partition ACS_DINFO_TF_202509 values less than (to_date('2025-10-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),partition ACS_DINFO_TF_202510 values less than (to_date('2025-11-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),partition ACS_DINFO_TF_202511 values less than (to_date('2025-12-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),partition ACS_DINFO_TF_202512 values less than (to_date('2026-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),partition ACS_DINFO_TF_202601 values less than (to_date('2026-02-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),partition ACS_DINFO_TF_202602 values less than (to_date('2026-03-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),partition ACS_DINFO_TF_202603 values less than (to_date('2026-04-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),partition ACS_DINFO_TF_202604 values less than (to_date('2026-05-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),partition ACS_DINFO_TF_202605 values less than (to_date('2026-06-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),partition ACS_DINFO_TF_202606 values less than (to_date('2026-07-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),partition ACS_DINFO_TF_202607 values less than (to_date('2026-08-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),partition ACS_DINFO_TF_202608 values less than (to_date('2026-09-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),partition ACS_DINFO_TF_202609 values less than (to_date('2026-10-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),partition ACS_DINFO_TF_202610 values less than (to_date('2026-11-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),partition ACS_DINFO_TF_202611 values less than (to_date('2026-12-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),partition ACS_DINFO_TF_202612 values less than (to_date('2027-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),partition ACS_DINFO_TF_202701 values less than (to_date('2027-02-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),partition ACS_DINFO_TF_202702 values less than (to_date('2027-03-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),partition ACS_DINFO_TF_202703 values less than (to_date('2027-04-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),partition ACS_DINFO_TF_202704 values less than (to_date('2027-05-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),partition ACS_DINFO_TF_202705 values less than (to_date('2027-06-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),partition ACS_DINFO_TF_202706 values less than (to_date('2027-07-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),partition ACS_DINFO_TF_202707 values less than (to_date('2027-08-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),partition ACS_DINFO_TF_202708 values less than (to_date('2027-09-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),partition ACS_DINFO_TF_202709 values less than (to_date('2027-10-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),partition ACS_DINFO_TF_202710 values less than (to_date('2027-11-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),partition ACS_DINFO_TF_202711 values less than (to_date('2027-12-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),partition ACS_DINFO_TF_202712 values less than (to_date('2028-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),partition ACS_DINFO_TF_202801 values less than (to_date('2028-02-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),partition ACS_DINFO_TF_202802 values less than (to_date('2028-03-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),partition ACS_DINFO_TF_202803 values less than (to_date('2028-04-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),partition ACS_DINFO_TF_202804 values less than (to_date('2028-05-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),partition ACS_DINFO_TF_202805 values less than (to_date('2028-06-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),partition ACS_DINFO_TF_202806 values less than (to_date('2028-07-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),partition ACS_DINFO_TF_202807 values less than (to_date('2028-08-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),partition ACS_DINFO_TF_202808 values less than (to_date('2028-09-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),partition ACS_DINFO_TF_202809 values less than (to_date('2028-10-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),partition ACS_DINFO_TF_202810 values less than (to_date('2028-11-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),partition ACS_DINFO_TF_202811 values less than (to_date('2028-12-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),partition ACS_DINFO_TF_202812 values less than (to_date('2029-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),partition ACS_DINFO_TF_202901 values less than (to_date('2029-02-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),partition ACS_DINFO_TF_202902 values less than (to_date('2029-03-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),partition ACS_DINFO_TF_202903 values less than (to_date('2029-04-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),partition ACS_DINFO_TF_202904 values less than (to_date('2029-05-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),partition ACS_DINFO_TF_202905 values less than (to_date('2029-06-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),partition ACS_DINFO_TF_202906 values less than (to_date('2029-07-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),partition ACS_DINFO_TF_202907 values less than (to_date('2029-08-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),partition ACS_DINFO_TF_202908 values less than (to_date('2029-09-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),partition ACS_DINFO_TF_202909 values less than (to_date('2029-10-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),partition ACS_DINFO_TF_202910 values less than (to_date('2029-11-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),partition ACS_DINFO_TF_202911 values less than (to_date('2029-12-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),partition ACS_DINFO_TF_202912 values less than (to_date('2030-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),partition ACS_DINFO_TF_203001 values less than (to_date('2030-02-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),partition ACS_DINFO_TF_203002 values less than (to_date('2030-03-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),partition ACS_DINFO_TF_203003 values less than (to_date('2030-04-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),partition ACS_DINFO_TF_203004 values less than (to_date('2030-05-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),partition ACS_DINFO_TF_203005 values less than (to_date('2030-06-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),partition ACS_DINFO_TF_203006 values less than (to_date('2030-07-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),partition ACS_DINFO_TF_203007 values less than (to_date('2030-08-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),partition ACS_DINFO_TF_203008 values less than (to_date('2030-09-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),partition ACS_DINFO_TF_203009 values less than (to_date('2030-10-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),partition ACS_DINFO_TF_203010 values less than (to_date('2030-11-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),partition ACS_DINFO_TF_203011 values less than (to_date('2030-12-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))，partition ACS_DINFO_TF_203012 values less than (to_date('2031-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')))TABLESPACE CONFIG_DATA PCTFREE 10 INITRANS 1 MAXTRANS 255 STORAGE ( INITIAL 64K NEXT 1M MINExtENTS 1 MAXExtENTS UNLIMITED );
-- 创建本地分区索引（按 acs_time）CREATE INDEX INDX_ACS_DINFO_TF_ACS_TIME ON ACS_DINFO_TF (ACS_TIME) LOCAL -- LOCAL 表示本地分区索引 TABLESPACE CONFIG_INDX PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 64K NEXT 1M MINExtENTS 1 MAXExtENTS UNLIMITED );
-- 添加全局主键约束（acs_id 是独立主键，不依赖分区键）ALTER TABLE ACS_DINFO_TF ADD CONSTRAINT ACS_DINFO_TF_PK PRIMARY KEY (ACS_ID) USING INDEX TABLESPACE CONFIG_DATA PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 64K NEXT 1M MINExtENTS 1 MAXExtENTS UNLIMITED );

insert into ACS_DINFO_TFselect * from ACS_DINFO_TF_A;
--删除临时表--DROP table ACS_DINFO_TF_A;
create or replace procedure P_ADD_DELETE_PARTITIONS(p_keep_month int --分区保留月份)as v_sql varchar2(300); VMESSAGE VARCHAR(250); v_delete_part_date varchar2(64); v_add_part_date varchar2(64); v_num number default 0;begin
-- DROP指定月份之前的所有分区 for i in (select table_name, min(partition_name) as min_part_name from user_tab_partitions where table_name not like '%$%' group by table_name) loop
v_delete_part_date := substr(i.min_part_name, instr(i.min_part_name, '_',-1) + 1);
while months_between(sysdate, to_date(v_delete_part_date, 'yyyymm')) > p_keep_month loop
begin v_sql := 'ALTER TABLE ' || i.table_name || ' DROP PARTITION ' || i.table_name || '_' || v_delete_part_date ||' update global indexes';
execute immediate v_sql;
VMESSAGE := 'The partition ' || i.table_name || '_' || v_delete_part_date ||' of the table '||i.table_name||' was successfully deleted.';
INSERT INTO AUTO_ADD_TABLEPART VALUES (i.table_name, v_sql, VMESSAGE, SYSDATE);
EXCEPTION WHEN OTHERS THEN
PROC_SAVE_ERRMSG('P_ADD_DELETE_PARTITIONS',DBMS_UTILITY.format_error_backtrace,sqlcode,SQLERRM);

v_delete_part_date := to_char(add_months(to_date(v_delete_part_date,'yyyymm'),1),'yyyymm'); end; end loop; end loop;--alter table ev_combined drop partition EV_COMBINED_202103 update global indexes -- add partition for i in (select table_name, max(partition_name) as max_part_name from user_tab_partitions where table_name not like '%$%' group by table_name) loop
v_add_part_date := substr(i.max_part_name, instr(i.max_part_name, '_',-1) + 1); --当表的最大分区小于3个月后，就给表多新增3个月的分区 if months_between(to_date(v_add_part_date, 'yyyymm'),sysdate) < 3 then for j in 1..3 loop v_num := v_num +1; v_add_part_date := to_char(add_months(to_date(v_add_part_date,'yyyymm'),1),'yyyymm'); begin v_sql := 'ALTER TABLE ' || i.table_name || ' add PARTITION ' || i.table_name || '_' || v_add_part_date; execute immediate v_sql; VMESSAGE := 'The partition ' || i.table_name || '_' || v_add_part_date ||' of the table '||i.table_name||' was successfully Add.'; INSERT INTO AUTO_ADD_TABLEPART VALUES (i.table_name, v_sql, VMESSAGE, SYSDATE);
EXCEPTION WHEN OTHERS THEN
PROC_SAVE_ERRMSG('P_ADD_DELETE_PARTITIONS',DBMS_UTILITY.format_error_backtrace,sqlcode,SQLERRM); end; end loop; end if; end loop;

end;/declare job_id int;beginselect max(job) into job_id from user_jobs where WHAT='p_add_delete_partitions(12);';if job_id>0 thendbms_job.remove(job_id);commit;end if;end;/
DECLAREJOB NUMBER;BEGINDBMS_JOB.SUBMIT(JOB => JOB,WHAT => 'p_add_delete_partitions(12);',NEXT_DATE =>TRUNC(SYSDATE + 1) + 2/24,INTERVAL => 'TRUNC(LAST_DAY(SYSDATE) + 1)',no_parse => FALSE);END;/exit;spool off

posted @ 2025-03-26 18:38 一只竹节虫阅读(28) 评论(0) 收藏举报

刷新页面返回顶部

一只竹节虫

Oracle数据库自动删除或增加表分区（写日志版）

公告