oracle 删除超过 2 个月的分区
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14933828/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Drop partitions older than 2 months
提问by Srikrishnan
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?
我有一个基于日期字段的分区表。现在，我必须编写一个程序来删除所有早于 2 个月的分区，即 test_date 早于 2 个月。我该怎么做？
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...
提前致谢...
回答by Vincent Malgrat
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 understandingit and testingit thoroughly, especially DDL scripts.
首先，我想指出分区是 Oracle 中的高级主题，因此您可能希望在开始时手动运行所有 DDL，直到您对运行 DDL 脚本有足够的信心为止。我还建议您不要在没有完全理解和测试的情况下运行在 Internet 上找到的脚本，尤其是 DDL 脚本。
Now for the matter at hand, you can query the *_TAB_PARTITIONSdictionary view to retrieve the partition boundary:
现在对于手头的事情，您可以查询*_TAB_PARTITIONS字典视图以检索分区边界：
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.
此列的类型是LONG(=deprecated LOB)，因此我们几乎没有工具可以使用 SQL 进行过滤，因此我们必须使用 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',
