I decided to write a plsql script that runs in period by a scheduled job. You can find the scheduled job settings in my previous postings. In this posting i will share the procedure that automatically creates the new months partition, archives, compresses and deletes the older partitions by the supplied retention.
CREATE OR REPLACE procedure AVEA_SYSMON.p_check_partition2
(s_table_name IN varchar2,
s_schema_name IN varchar2,
s_archive_ts IN varchar2,
n_archive_ret number,
n_delete_ret number)
/*As i run this procedure from a job. I want running user to own the privileges*/
AUTHID CURRENT_USER
AS
s_compression varchar2(20);
s_ts_name varchar2(20);
n_max_month number;
n_max_year number;
n_next_month number;
n_next_year number;
n_this_year number;
n_this_month number;
part_next_month number;
part_next_year number;
n_ctl number;
d_next_month date;
s_part_less_than varchar2(200);
s_cmd varchar2(200);
begin
/*I will read the dba_tab_partitions management view to find the table partitions.
This procedure works with the formatted name of the partition.
Here you should have YYYYMM at the end of the partition name*/
select
max(substr(partition_name,-6,4)) into n_max_year
from
dba_tab_partitions t1
where
table_owner=s_schema_name and
table_name=s_table_name;
-- get max month of table partition
select
max(substr(partition_name,-2,2)) into n_max_month
from
dba_tab_partitions t1
where
table_owner=s_schema_name and
table_name=s_table_name and
substr(partition_name,-6,4) = n_max_year;
-- find this month this year;
n_this_year := to_number(to_char(sysdate,'YYYY'));
n_this_month := to_number(to_char(sysdate,'MM'));
-- find next month next year
n_next_year := to_number(to_char(add_months(sysdate,1),'YYYY'));
n_next_month := to_number(to_char(add_months(sysdate,1),'MM'));
-- partition definition for next month next year should be one more
part_next_year := to_number(to_char(add_months(sysdate,2),'YYYY'));
part_next_month := to_number(to_char(add_months(sysdate,2),'MM'));
-- check if next months partition exists, create it if not exists
if mod(n_this_month,12) = mod(n_max_month,12) then
s_part_less_than := 'to_date(''01.' || trim(to_char(part_next_month,'09')) || '.' || part_next_year || ''',''DD.MM.YYYY'')';
s_cmd:='alter table ' || s_schema_name || '.' || s_table_name || ' add partition ' || s_table_name || '_' || n_next_year || '_' || trim(to_char(n_next_month,'09')) || ' values less than (' || s_part_less_than || ') ';
--dbms_output.put_line(s_cmd);
begin
execute immediate (s_cmd);
exception
when others then
null;
end;
end if;
/*compress and move partitions to archive tablespace which are
months older*/
begin
select
compression, tablespace_name into s_compression, s_ts_name
from
dba_tab_partitions
where
table_owner=s_schema_name and
table_name=s_table_name and
substr(partition_name,-6,4)=to_char(add_months(sysdate,n_archive_ret),'YYYY') and
substr(partition_name,-2,2)=to_char(add_months(sysdate,n_archive_ret),'MM');
/*first i should be sure if the partition to be compressed and archived
is exists. And move the partition to archive tablespace if it is not already
there and compress if it is not already compresses*/
if s_ts_name!=s_archive_ts then
s_cmd := 'alter table ' || s_schema_name || '.' || s_table_name ||
' move partition ' || s_table_name || '_' ||
trim(to_char(add_months(sysdate,n_archive_ret),'YYYY')) || '' ||
trim(to_char(add_months(sysdate,n_archive_ret),'MM')) ||
' tablespace ' || s_archive_ts;
if s_compression!='ENABLED' then
s_cmd := s_cmd || ' COMPRESS';
end if;
s_cmd := s_cmd || ' update global indexes';
execute immediate (s_cmd);
end if;
s_cmd:='';
exception
when no_data_found then
null;
end;
-- drop partitions ofyear old
select
count(*) into n_ctl
from
dba_tab_partitions
where
table_owner=s_schema_name and
table_name=s_table_name and
substr(partition_name,-6,4)=to_char(add_months(sysdate,n_delete_ret),'YYYY') and
substr(partition_name,-2,2)=to_char(add_months(sysdate,n_delete_ret),'MM');
/* if the partition to be dropped exists then drop it */
if n_ctl=1 then
s_cmd := 'alter table ' || s_schema_name || '.' || s_table_name ||
' drop partition ' || s_table_name || '_' ||
to_char(add_months(sysdate,n_delete_ret),'YYYY') || '' ||
to_char(add_months(sysdate,n_delete_ret),'MM') ||
' update global indexes';
execute immediate (s_cmd);
end if;
end p_check_partition2;
/
The procedure is OK now. You should now be careful to give the right parameters. Be sure not to drop something should not be dropped. In our site our delete retention is one year and archive retention is 3 months. I run this procedure like the following parameters;
begin
p_check_partition2('PAYFORME','PQ','PQ_ARCHIVE',-3,-12);
end;
