Sample script to drop interval partitions

Situation:

The big benefit of using interval partition is automatic partition creation when new records are inserted. But we need purge the history partitions by developing in-house scripts.

SQL> desc user_tab_partitions;
Name Null? Type
———————– ——– —————-
TABLE_NAME VARCHAR2(30)
COMPOSITE VARCHAR2(3)
PARTITION_NAME VARCHAR2(30)
SUBPARTITION_COUNT NUMBER
HIGH_VALUE LONG
HIGH_VALUE_LENGTH NUMBER
PARTITION_POSITION NUMBER

Solution:

Here is a basic sample script for dropping partitions more than 20 days old.

SQL> Set serveroutput on
SQL> declare
dt date;
begin
for x in (select table_name,partition_name, high_value from user_tab_partitions where INTERVAL=’YES’ )
loop
execute immediate ‘select ‘||x.high_value||’ from dual’ into dt;
if dt < sysdate – 20
then
dbms_output.put_line(‘to drop partition: ‘||x.table_name||’.’||x.partition_name);
execute immediate ‘alter table ‘||x.table_name||’ drop partition ‘|| x.partition_name||’ update indexes ‘;
end if;
end loop;
end;

/

to drop partition: TBL_TEST1.SYS_P277
to drop partition: TBL_TEST1.SYS_P221
to drop partition: TBL_TEST2.SYS_P231
to drop partition: TBL_TEST2.SYS_P281
to drop partition: TBL_TEST3.SYS_P226
to drop partition: TBL_TEST3..SYS_P242

PL/SQL procedure successfully completed.

Advertisements
This entry was posted in Partitioning and tagged , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s