Monday, October 10, 2011

Dropping old partitions with a script

Lets say that you created a partitioned table some time ago to hold a lot of data generated by day:

CREATE TABLE MY_TABLE
(
SOMETEXT VARCHAR2(4000),
MOREINFO VARCHAR2(256),
ADATE DATE
) PARTITION BY RANGE (ADATE)
INTERVAL (NUMTODSINTERVAL(1,'day'))
(partition p0 values less than (to_date('01-jan-2000','dd-mon-yyyy'))
);

This data is needed to generate some reports, but after some time it is not necessary anymore so you have to drop old partitions in order to not waste disk space. What a simple task, isn't it? You just have to look for information in USER_TAB_PARTITIONS and drop the oldest partitions:

SQL> select PARTITION_NAME, HIGH_VALUE from USER_TAB_PARTITIONS
where TABLE_NAME='MY_TABLE' and PARTITION_NAME!='P0';

PARTITION_NAME HIGH_VALUE
------------------------------ --------------------------------------------------------------------------------
SYS_P193448 TO_DATE(' 2011-10-08 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SYS_P193449 TO_DATE(' 2011-10-09 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SYS_P193450 TO_DATE(' 2011-10-10 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

SQL> select PARTITION_NAME, HIGH_VALUE from USER_TAB_PARTITIONS where TABLE_NAME='MY_TABLE' and PARTITION_NAME!='P0' order by HIGH_VALUE;
select PARTITION_NAME, HIGH_VALUE from USER_TAB_PARTITIONS where TABLE_NAME='MY_TABLE' and PARTITION_NAME!='P0' order by HIGH_VALUE
*
ERROR at line 1:
ORA-00997: illegal use of LONG datatype


SQL> select PARTITION_NAME, SUBSTR(HIGH_VALUE,11,19) from USER_TAB_PARTITIONS where TABLE_NAME='MY_TABLE' and PARTITION_NAME!='P0';
select PARTITION_NAME, SUBSTR(HIGH_VALUE,11,19) from USER_TAB_PARTITIONS where TABLE_NAME='MY_TABLE' and PARTITION_NAME!='P0'
*
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected NUMBER got LONG

What a problem; there is a lot of restrictions manipulating LONG datatypes so you cannot just get the date from the HIGH_VALUE column. For this task you'll need a PL/SQL script:

SQL> DECLARE
v_sql varchar2(500);
v_date date;
v_partition_name user_tab_partitions.partition_name%TYPE;
v_high_value user_tab_partitions.high_value%TYPE;
CURSOR c1 IS select PARTITION_NAME, HIGH_VALUE
from user_tab_partitions where table_name='MY_TABLE' and PARTITION_NAME!='P0';
BEGIN
OPEN c1;
LOOP
FETCH c1 INTO v_partition_name, v_high_value;
EXIT WHEN c1%NOTFOUND;
v_date := to_date(SUBSTR(v_high_value,11,19),'YYYY-MM-DD HH24:MI:SS');
IF v_date < (sysdate - 2) then
v_sql := 'alter table MY_TABLE drop partition ' || v_partition_name;
execute immediate v_sql;
END IF;
END LOOP;
CLOSE c1;
END;
/

PL/SQL procedure successfully completed.

SQL> select PARTITION_NAME, HIGH_VALUE from USER_TAB_PARTITIONS
where TABLE_NAME='MY_TABLE' and PARTITION_NAME!='P0';

PARTITION_NAME HIGH_VALUE
------------------------------ --------------------------------------------------------------------------------
SYS_P193449 TO_DATE(' 2011-10-09 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SYS_P193450 TO_DATE(' 2011-10-10 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

With this script you can drop partitions from 2 days ago and older, and put it as a daily job in order to automate this task.

7 comments:

  1. Nice script! Exactely what I was looking for, even the timespan of 2 days matches my requirement ;-)

    Thanks,

    Sebastian

    ReplyDelete
  2. Hi this is almost exactly what I need for deleting partitions older than 14 days! Where would I add the update indexes argument? do I just add || update indexes in the v_sql:= ??

    ReplyDelete
    Replies
    1. Sorry, I don't get your question. But if you want to modify the script to drop partitions older than 14 days, then you have to change this line:

      IF v_date < (sysdate - 2) then

      for this one:

      IF v_date < (sysdate - 14) then

      Don't forget to change the name of the table as well, and testing it before using it with real data will be better.

      Delete
  3. I tested - and it seems to work but naturally complains about deleting the oldest partition. It's an interval by day with a new partition created each day.

    Script output:

    Error starting at line : 1 in command -
    DECLARE
    v_sql varchar2(500);
    v_date date;
    v_partition_name user_tab_partitions.partition_name%TYPE;
    v_high_value user_tab_partitions.high_value%TYPE;
    CURSOR c1 IS select PARTITION_NAME, HIGH_VALUE
    from user_tab_partitions where table_name='TRACKING_DATA_T' and PARTITION_NAME!='P0';
    BEGIN
    OPEN c1;
    LOOP
    FETCH c1 INTO v_partition_name, v_high_value;
    EXIT WHEN c1%NOTFOUND;
    v_date := to_date(SUBSTR(v_high_value,11,19),'YYYY-MM-DD HH24:MI:SS');
    IF v_date < (sysdate - 14) then
    v_sql := 'alter table TRACKING_DATA_T drop partition ' || v_partition_name || ' ' || ' UPDATE INDEXES ' ;
    execute immediate v_sql;

    END IF;
    END LOOP;
    CLOSE c1;
    END;
    Error report -
    ORA-14758: Last partition in the range section cannot be dropped
    ORA-06512: at line 16

    ReplyDelete
    Replies
    1. You can exclude the last partition from deletion modifying the cursor clause:

      from user_tab_partitions where table_name='MY_TABLE' and PARTITION_NAME!='P0';

      Just change P0 by the name of the partition that should not be deleted.

      Delete
  4. I used a range to solve the problem and it works.

    IF v_date < (sysdate - 14) and v_date > (sysdate -30) then ...

    Thanks very much for this it solved a problem for me! I am limited on undo table space the old delete job needed a lot of it.

    ReplyDelete
    Replies
    1. This would work as well, glad to have helped you. =)

      Delete