一、需要分区的表
create table PARTITION_TABLE_INFO
(
table_name VARCHAR2(500),
comments VARCHAR2(500),
enable VARCHAR2(500) default 'Y'
)/ comment on table PARTITION_TABLE_INFOis '分区维护表' / comment on column PARTITION_TABLE_INFO.table_name is '表名称' / comment on column PARTITION_TABLE_INFO.comments is '表中文名称' / comment on column PARTITION_TABLE_INFO.enable is 'Y-创建分区，N-不创建分区' /
二、创建存储过程
CREATE OR REPLACE PROCEDURE PR_ADD_PARTITION(P_DATA_DATE IN VARCHAR2) IS
/******************************************************************************
NAME : PR_ADD_PARTITION
FUNCTIONS : 创建系统分区
PURPOSE :
REVISIONS OR COMMENTS
VER DATE AUTHOR DESCRIPTION
--------- ---------- --------------- ------------------------------------
1.0 2024-3-22 RC 1. CREATED THIS PACKAGE.
******************************************************************************/
V_PARTITION_NAME VARCHAR(200) := 'P_' || REPLACE(P_DATA_DATE, '-', '');
V_PARTITION_DATE VARCHAR(10) := P_DATA_DATE;
ALTER_SQL VARCHAR(500);
BEGIN
for tab in (select table_name
from partition_table_info
where table_name in -- 只对分区表进行分区创建
(select table_name from USER_TAB_PARTITIONS)
AND TABLE_NAME NOT IN -- 跳过已经创建指定分区的表
(select table_name from USER_TAB_PARTITIONS WHERE PARTITION_NAME = V_PARTITION_NAME)
) loop
ALTER_SQL := 'ALTER TABLE ' || tab.table_name || ' add partition ' ||
V_PARTITION_NAME || ' values(' || '''' || V_PARTITION_DATE || '''' ||
') tablespace users pctfree 10 initrans 1 maxtrans 255';
--sys.dbms_output.put_line(ALTER_SQL);
EXECUTE IMMEDIATE ALTER_SQL;
END LOOP;
END;
/
三、批量创建分区
declare
start_date VARCHAR2(10 CHAR):= '2022-01-01';
end_date VARCHAR2(10 CHAR):= '2024-06-30';
begin
FOR DATE_INFO IN (Select Rownum, To_char(Add_months(
Last_day(To_date(start_date, 'yyyy-mm-dd')), Rownum - 1), 'yyyy-mm-dd') As monEnd
From Dual
Connect By Rownum <= (Select
Months_between(To_date(end_date, 'yyyy-mm-dd'), To_date(start_date, 'yyyy-mm-dd')) + 1
From Dual)) LOOP
sys.dbms_output.put_line(DATE_INFO.MONEND);
pr_add_partition(DATE_INFO.MONEND);
END LOOP;
end;
四、自动调度每个月月初调度
declare
job number;
begin
sys.dbms_job.submit(job => job,
what => 'DECLARE
BEGIN
PR_ADD_PARTITION(TO_CHAR(LAST_DAY(SYSDATE), ''YYYY-MM-DD''));
END;',
next_date => sysdate+10/(24*60),
interval => 'TRUNC(LAST_DAY(SYSDATE))+1+1/24');
commit;
end;