删除两个月以上的分区 [英] Drop partitions older than 2 months

问题描述

我有一个带分区的表，该分区基于日期字段.现在，我必须编写一个程序来删除所有两个月以上的分区，即test_date大于两个月的分区.我该怎么办?

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'))
);

预先感谢...

推荐答案

首先，我想指出分区是Oracle中的高级主题，因此，您可能希望在一开始就手动运行所有DDL，直到您对运行DDL脚本有足够的信心.我还建议您不要运行Internet上找到的脚本，除非完全了解 和测试，尤其是DDL脚本.

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.

现在，您可以查询 *_TAB_PARTITIONS 字典视图以检索分区边界:

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'

此列的类型为LONG(=不建议使用的LOB)，因此我们很少有使用SQL进行过滤的工具，我们将不得不使用PL/SQL.

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', 

这篇关于删除两个月以上的分区的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！