oracle 如何按日期列对oracle表进行分区？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/33808347/
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
How to partition an oracle table by a date column?
提问by victorio
I have a table in oracle:
我在 oracle 中有一张表：
CREATE TABLE transaction (
id INT NOT NULL,
accountnumber VARCHAR NOT NULL,
stmtvaluedate DATE NOT NULL,
...
)
And I want to partition this table by the stmtvaluedate column. My goal is to create a new partition after a month have passed.
我想通过 stmtvaluedate 列对这个表进行分区。我的目标是在一个月过去后创建一个新分区。
Is there any good script, for it? Or I have to create static numbers of partitions?
有什么好的剧本吗？或者我必须创建静态数量的分区？
The best would be: if a month have passed, a new partition will be created automatically.
最好是：如果一个月过去了，将自动创建一个新分区。
Can anyone give me an example about how to partition a table by a date column after every month? If the automatically partitioning is impossible, than I would need an example, which creates partitions to a year from now by a date column, about every month.
谁能给我一个关于如何在每个月后按日期列对表进行分区的示例？如果自动分区是不可能的，那么我需要一个示例，该示例通过日期列创建从现在到一年的分区，大约每个月。
Thanks!
谢谢！
回答by Matthew McPeak
What you want to do is completely possible. This should do it:
你想做的事完全有可能。这应该这样做：
CREATE TABLE transaction (
id INT NOT NULL,
accountnumber VARCHAR2(30) NOT NULL,
stmtvaluedate DATE NOT NULL
)
PARTITION BY RANGE (stmtvaluedate)
INTERVAL (NUMTOYMINTERVAL (1,'MONTH'))
( partition transaction_old values less than (to_date('01-JAN-2000','DD-MON-YYYY') ));
