当前位置 博文首页 > LuciferLiu_DBA：通过Procedure将普通表生成按月分区表结构
懒人自有懒方法，最近在做普通表转分区表，由于之前都是手动去构建分区表脚本，可能会出错，用时也需要很久。
于是参照梁敬彬大佬的普通表自动转化为按月分区表的脚本，改写了一版适合自己的，Procedure如下：
仅适用于按月分区的分表表结构
create or replace procedure ctas_par(p_tab in varchar2, --需要被分区表名称
p_part_colum in varchar2, --需要被分区列名称
p_part_nums in number default 24, --需要建立的分区月数
p_tablespace IN VARCHAR2) as
YYYYMMDD VARCHAR2(50) := TO_CHAR(TRUNC(SYSDATE), 'dd');
v_sql varchar2(32767);
v_min_date date;
v_first_day date;
v_next_day date;
v_prev_day date;
v_partiton varchar2(1000);
v_partitons varchar2(32767);
begin
--rename 原表，这里假设已经rename，仅仅为了获取脚本
--获取需要被分区的分区列最早记录的日期
v_sql := 'select min(' || p_part_colum || ') from ' || p_tab;
execute immediate (v_sql)
into v_min_date;
DBMS_OUTPUT.PUT_LINE('分区表获取分区列最小记录日期：' || v_min_date);
--获取分区表开始的下个月份
select to_date(to_char(v_min_date, 'yyyymm') || '01', 'yyyymmdd')
into v_first_day
from dual;
--通过循环组合partition分区的格式
for i in 1 .. p_part_nums loop
select add_months(v_first_day, i) into v_next_day from dual;
select add_months(v_next_day, -1) into v_prev_day from dual;
v_partiton := 'partition ' || p_tab || '_P' ||
to_char(v_prev_day, 'yyyymm') ||
' values less than (TO_DATE(''' ||
to_char(v_next_day, 'SYYYY-MM-DD HH24:MI:SS') ||
''', ''SYYYY-MM-DD HH24:MI:SS'', ''NLS_CALENDAR=GREGORIAN'')) tablespace ' ||
p_tablespace || ',' || chr(13) || chr(10);
v_partitons := v_partitons || v_partiton;
end loop;
--DBMS_OUTPUT.PUT_LINE('分区表的partition构造：');
--DBMS_OUTPUT.PUT_LINE(v_partitons);
v_sql := 'create table ' || p_tab || chr(13) || chr(10) ||
'partition BY RANGE(' || p_part_colum || ')(' || chr(13) ||
chr(10) || v_partitons || 'partition ' || p_tab ||
'_MAX values less than (maxvalue) tablespace ' || p_tablespace || ')
nologging
parallel 4
enable row movement
tablespace ' || p_tablespace || '
as select /*+parallel(' || p_tab || ',8)*/ * from ' ||
p_tab || '_' || YYYYMMDD || ' where 1 = 2;' || chr(13) ||
chr(10);
v_sql := v_sql || ' alter table' || p_tab || ' logging;
' || chr(13) || chr(10);
v_sql := v_sql || ' alter table ' || p_tab || ' noparalle; ';
DBMS_OUTPUT.PUT_LINE('分区表ctas创建的完整语句如下： ');
DBMS_OUTPUT.PUT_LINE(v_sql);
end ctas_par;
--创建表T2
CREATE TABLE T2 (T2ID NUMBER DEFAULT 8,
T1ID1 NUMBER NOT NULL,
T1ID2 NUMBER,
deal_date date,
primary key (T2ID,T1ID1));
--执行procedure
set serveroutput on size 1000000
exec ctas_par(p_tab => 'T2',p_part_colum => 'DEAL_DATE',p_part_nums=> 24,p_tablespace => 'USERS'); 分区表获取分区列最小记录日期：13-APR-21
分区表ctas创建的完整语句如下：
create table T2
partition BY RANGE(deal_date)(
partition T2_P202104 values less than (TO_DATE(' 2021-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace USERS,
partition T2_P202105 values less than (TO_DATE(' 2021-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace USERS,
partition T2_P202106 values less than (TO_DATE(' 2021-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace USERS,
partition T2_P202107 values less than (TO_DATE(' 2021-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace USERS,
partition T2_P202108 values less than (TO_DATE(' 2021-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace USERS,
partition T2_P202109 values less than (TO_DATE(' 2021-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace USERS,
partition T2_P202110 values less than (TO_DATE(' 2021-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace USERS,
partition T2_P202111 values less than (TO_DATE(' 2021-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace USERS,
partition T2_P202112 values less than (TO_DATE(' 2022-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace USERS,
partition T2_P202201 values less than (TO_DATE(' 2022-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace USERS,
partition T2_P202202 values less than (TO_DATE(' 2022-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace USERS,
partition T2_P202203 values less than (TO_DATE(' 2022-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace USERS,
partition T2_P202204 values less than (TO_DATE(' 2022-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace USERS,
partition T2_P202205 values less than (TO_DATE(' 2022-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace USERS,
partition T2_P202206 values less than (TO_DATE(' 2022-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace USERS,
partition T2_P202207 values less than (TO_DATE(' 2022-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace USERS,
partition T2_P202208 values less than (TO_DATE(' 2022-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace USERS,
partition T2_P202209 values less than (TO_DATE(' 2022-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace USERS,
partition T2_P202210 values less than (TO_DATE(' 2022-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace USERS,
partition T2_P202211 values less than (TO_DATE(' 2022-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace USERS,
partition T2_P202212 values less than (TO_DATE(' 2023-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace USERS,
partition T2_P202301 values less than (TO_DATE(' 2023-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace USERS,
partition T2_P202302 values less than (TO_DATE(' 2023-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace USERS,
partition T2_P202303 values less than (TO_DATE(' 2023-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace USERS,
partition T2_MAX values less than (maxvalue) tablespace USERS)
nologging
parallel 4
enable row movement
tablespace USERS
as select /*+parallel(T2,8)*/ * from T2_13 where 1 = 2;
alter tableT2 logging;
alter table T2 noparalle;
?
