I have a table with partition based on date field. Now, I have to write a procedure to drop all partitions older than 2 months i.e. test_date is older than 2 months. How do I do it?
create table test_table
(
test_id number,
test_date date,
constraint pk_test primary key (test_id)
)
partition by range (test_date)
(
PARTITION pt01122012 VALUES LESS THAN (TO_DATE('01-DEC-
2012', 'DD-MON-YYYY')),
PARTITION pt01022013 VALUES LESS THAN (TO_DATE('01-FEB-
2013', 'DD-MON-YYYY')),
PARTITION pt01042013 VALUES LESS THAN (TO_DATE('01-APR-
2013', 'DD-MON-YYYY')),
PARTITION pt01062013 VALUES LESS THAN (TO_DATE('01-JUN-
2013', 'DD-MON-YYYY')),
PARTITION pt01082013 VALUES LESS THAN (TO_DATE('01-AUG-
2013', 'DD-MON-YYYY'))
);
Thanks in advance...
First, I'd like to point that partitioning is an advanced subject in Oracle, therefore you may want to run all DDL manually at the beginning, until you're confident enough with running DDL scripts. I would also advise you to never run a script found on the internet without having both understanding it and testing it thoroughly, especially DDL scripts.
Now for the matter at hand, you can query the *_TAB_PARTITIONS
dictionary view to retrieve the partition boundary:
SQL> SELECT partition_name, high_value
2 FROM user_tab_partitions
3 WHERE table_name = 'TEST_TABLE';
PARTITION_NAME HIGH_VALUE
--------------- --------------------------------------------------------
PT01122012 TO_DATE(' 2012-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'
PT01022013 TO_DATE(' 2013-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'
PT01042013 TO_DATE(' 2013-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'
PT01062013 TO_DATE(' 2013-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'
PT01082013 TO_DATE(' 2013-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'
This column is of type LONG
(=deprecated LOB) so we have few tools to filter with SQL, we'll have to use PL/SQL.
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 /
PL/SQL procedure successfully completed
SQL> SELECT partition_name, high_value
2 FROM user_tab_partitions
3 WHERE table_name = 'TEST_TABLE';
PARTITION_NAME HIGH_VALUE
--------------- -----------------------------------------------------------
PT01022013 TO_DATE(' 2013-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
PT01042013 TO_DATE(' 2013-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
PT01062013 TO_DATE(' 2013-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
PT01082013 TO_DATE(' 2013-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',