Script for manage table partition.

I want to manage table partition that have partition with RANGE and sub partition with HASH and the developer does not need the data older than 4 months so I decided to create the script to add partition the next 2 month and drop the fifth previous month in script.

create or replace manage_table_partition_xx
as
next_2_month char(8);
next_month char(8);
PREV_5_MONTH CHAR(8);

which_month char(3);
which_part char(6);
begin
select to_char(add_months(sysdate,2),'MON_YYYY') into next_2_month from dual;
select to_char(add_months(sysdate,1),'MON_YYYY') into next_month from dual;
select to_char(add_months(sysdate,-5),'MON_YYYY') into PREV_5_MONTH from dual;

which_month := substr(next_month,1,3);
if ( which_month = 'JAN' or which_month = 'JUL') THEN
which_part := 'PART01';
elsif ( which_month = 'FEB' or which_month = 'AUG') THEN
which_part := 'PART02';
elsif ( which_month = 'MAR' or which_month = 'SEP') THEN
which_part := 'PART03';
elsif ( which_month = 'APR' or which_month = 'OCT') THEN
which_part := 'PART04';
elsif ( which_month = 'MAY' or which_month = 'NOV') THEN
which_part := 'PART05';
elsif ( which_month = 'JUN' or which_month = 'DEC') THEN
which_part := 'PART06';
end if;

-- DROP PARTITION
execute immediate ('alter table TATSHUYA.XX drop partition XX_' || PREV_5_MONTH );


-- Alter table add partition
execute immediate ('alter table TATSHUYA.XX add partition XX_' || next_month || ' values less than (to_date(''01_' || next_2_month || ''',''DD_MON_YYYY''))
PCTFREE 10 PCTUSED 40 INITRANS 10 MAXTRANS 255 TABLESPACE DATA
(subpartition XX_' || next_month || '_01 tablespace XX_' || which_part || '_01,
subpartition XX_' || next_month || '_02 tablespace XX_' || which_part || '_02,
subpartition XX_' || next_month || '_03 tablespace XX_' || which_part || '_03,
subpartition XX_' || next_month || '_04 tablespace XX_' || which_part || '_04,
subpartition XX_' || next_month || '_05 tablespace XX_' || which_part || '_05,
subpartition XX_' || next_month || '_06 tablespace XX_' || which_part || '_06,
subpartition XX_' || next_month || '_07 tablespace XX_' || which_part || '_07,
subpartition XX_' || next_month || '_08 tablespace XX_' || which_part || '_08,
subpartition XX_' || next_month || '_09 tablespace XX_' || which_part || '_09,
subpartition XX_' || next_month || '_10 tablespace XX_' || which_part || '_10)
');

execute immediate ('alter index TATSHUYA.XX_IDX01 rebuild subpartition XX_' || next_month || '_01 tablespace INDX_XX_' || which_part || '_01');
execute immediate ('alter index TATSHUYA.XX_IDX01 rebuild subpartition XX_' || next_month || '_02 tablespace INDX_XX_' || which_part || '_02');
execute immediate ('alter index TATSHUYA.XX_IDX01 rebuild subpartition XX_' || next_month || '_03 tablespace INDX_XX_' || which_part || '_03');
execute immediate ('alter index TATSHUYA.XX_IDX01 rebuild subpartition XX_' || next_month || '_04 tablespace INDX_XX_' || which_part || '_04');
execute immediate ('alter index TATSHUYA.XX_IDX01 rebuild subpartition XX_' || next_month || '_05 tablespace INDX_XX_' || which_part || '_05');
execute immediate ('alter index TATSHUYA.XX_IDX01 rebuild subpartition XX_' || next_month || '_06 tablespace INDX_XX_' || which_part || '_06');
execute immediate ('alter index TATSHUYA.XX_IDX01 rebuild subpartition XX_' || next_month || '_07 tablespace INDX_XX_' || which_part || '_07');
execute immediate ('alter index TATSHUYA.XX_IDX01 rebuild subpartition XX_' || next_month || '_08 tablespace INDX_XX_' || which_part || '_08');
execute immediate ('alter index TATSHUYA.XX_IDX01 rebuild subpartition XX_' || next_month || '_09 tablespace INDX_XX_' || which_part || '_09');
execute immediate ('alter index TATSHUYA.XX_IDX01 rebuild subpartition XX_' || next_month || '_10 tablespace INDX_XX_' || which_part || '_10');


dbms_stats.gather_table_stats(ownname => 'DMC', tabname => 'XX', cascade => TRUE, partname => 'XX_' || next_month );

end;

== TaTsHuYa ==