Square DBA – Oracle DBA Talk

Have you ever needed to truncate old range partitions that are broken up by day? I do all the time. Much of my data warehouse data is located in range partitioned tables. These are generally partitioned by day. I put together the following script to take care of the job for me.

CREATE OR REPLACE  PROCEDURE "LCR"."TRUNCATEOLDPART"  (
 i_table_name IN varchar2,
 i_table_owner IN varchar2,
 i_start_day IN varchar2,
 i_end_day IN varchar2
)

as
   v_value varchar2(255);
   v_name varchar2(30);
   p_s_date date := trunc(sysdate-i_start_day);
   p_e_date date := trunc(sysdate-i_end_day);    

begin
   FOR c1 IN (select high_value, partition_name
     from all_tab_partitions
    where table_name = i_table_name and table_owner = i_table_owner)

   loop

   v_value := c1.high_value;
   v_name := c1.partition_name;

      if to_date(substr(v_value,11,10),'YYYY-MM-DD') between p_s_date
      and p_e_date then dbms_output.put_line('Start Truncating
      Partition '||substr(v_name,1,100)||' at '||
      to_char(sysdate,'YYYY-MM-DD HH24:MI:SS'));
      EXECUTE IMMEDIATE 'ALTER TABLE '||i_table_owner||'.'||
      i_table_name||' truncate partition '||v_name;
      dbms_output.put_line('Completed Truncating Partition '||
      v_name||' at '||to_char(sysdate,'YYYY-MM-DD HH24:MI:SS'));
      dbms_output.put_line('------------------------------------');
      end if;
   end loop;
end;

The usage of this script is as follows.

execute dbms_output.enable(null);
execute lcr.truncateoldpart(table_name,table_owner,
oldest_days_to_truncate, newest_days_to_truncate);

So to truncate all partitions between 60 and 30 days old on the LCR.ENUM_CDR table just run:

execute dbms_output.enable(null);
execute lcr.truncateoldpart('ENUM_CDR','LCR',60,30);

Output Will look like this:

Start Truncating Partition SYS_P2505671 at 2009-09-22 13:59:30
Completed Truncating Partition SYS_P2505671 at 2009-09-22 13:59:30
------------------------------------
Start Truncating Partition SYS_P2505651 at 2009-09-22 13:59:30
Completed Truncating Partition SYS_P2505651 at 2009-09-22 13:59:31

One caveat with a script like this is permissions. This works fine for running again any table in the schema owned by the package owner. If you try to run this against another schema you will get permission errors. To solve this you need to grant “drop any table” to the package owner. Obviously this is potential security risk. I prefer to just re-create the package in every schema that needs to perform this function. That way I don’t need to give any one user the ability to drop any table in my database.