Procedure to create partition table on daily basis, truncate old partition & records-CodePudding

I have written 1 Stored Procedure where it should create new partition on daily basis, truncate partition & records older than 9 months. Below is the SP-

create or replace procedure sp_partn as 
 hm varchar2(255);
 mm varchar2(50);
 yr varchar2(50);
 dd varchar2(10);
 ym varchar2(50);
 dt varchar2(50);

 procedure tbl1_prtn as
 begin
  for i in (select partition_name, high_value from user_tab_partition where table_name='tbl1')
   loop
    hm := i.high_value;
    mm := substr(hm,6,7);
    yr := substr(hm,1,4);
    dd := substr(hm,9,10);
    ym := yr||mm||dd;
    dt := to_date(ym,'yyyymmdd');
    
   if sysdate >= add_months(dt, 9) then
      execute immediate 'alter table tbl1 drop partition' || i.partition_name;
   end if;

  end loop;
end tbl1_prtn;
begin
 tbl1_prtn;
end sp_partn;
/

When i execute above SP then neither partition create or neither drop partition of older than 9 months. (eg: tbl_20211116)

CodePudding user response：

run

SELECT HIGH_VALUE FROM USER_TAB_PARTITIONS

because for a date partition, the HIGH_VALUE is normally something like "TO_DATE(' 2016-01-04 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'" so I'm not sure your substr commands will work. A better option might be:

hm := i.high_value;
execute immediate 'select '||hm||' from dual' into dt;

which will be immune to string position etc.

CodePudding user response：

If you store dates as dates instead of strings it will make your life much easier and you can use INTERVAL PARTITIONs and this will solve your problem.

SQL> BEGIN
  2     FOR cc IN (SELECT partition_name, high_value --
  3                  FROM user_tab_partitions
  4                 WHERE table_name = 'TEST_TABLE') LOOP
  5        EXECUTE IMMEDIATE
  6           'BEGIN
  7               IF sysdate >= ADD_MONTHS(' || cc.high_value || ', 2) THEN
  8                  EXECUTE IMMEDIATE
  9                     ''ALTER TABLE TEST_TABLE DROP PARTITION '
 10                     || cc.partition_name || '
 11                     '';
 12               END IF;
 13            END;';
 14     END LOOP;
 15  END;
 16  /