Square DBA – Oracle DBA Talk

Truncate date range partitions

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.

Managing Oracle Partitioned Tables

I deal a lot with call detail records. Currently we process several million calls per day so that means I get several million rows or data daily. To deal with all that data I use Oracle’s partitioned tables. Oracle partitions addresses the key problem of supporting very large tables and indexes by allowing you to decompose them into smaller and more manageable pieces called partitions. Once partitions are defined, SQL statements can access and manipulate the partitions rather than entire tables or indexes. Partitions are especially useful in data warehouse applications, which commonly store and analyze large amounts of historical data. Here are some common SQL statements I use, some of them I use daily.

SELECT * FROM schema.table PARTITION (part_name);

ALTER TABLE aaa MOVE PARTITION bbb
TABLESPACE rrr NOLOGGING;

ALTER TABLE edu
ADD PARTITION jan99 VALUES LESS THAN ( '990201' )
TABLESPACE tsjan99;

ALTER TABLE DROP PARTITION bbb

ALTER TABLE TRUNCATE PARTITION bbb