如何更改现有表以在 Oracle 中创建范围分区
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/35932771/
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 do I alter my existing table to create a range partition in Oracle
提问by Some Java Guy
I have existing table which has 10 years of data (I have taken dump).
我现有的表有 10 年的数据（我已经转储了）。
I would like to Range partition the existing table on one date key column within the table.
我想在表中的一个日期键列上对现有表进行范围分区。
Most of the examples I see are with CREATE TABLE..PARTITION BY RANGE...to add new partitions. But my table is existing table.
我看到的大多数示例都CREATE TABLE..PARTITION BY RANGE...用于添加新分区。但我的桌子是现有的桌子。
I assume I need some ALTERstatement.
我想我需要一些ALTER声明。
ALTER TABLE TABLE_NAME
PARTITION BY RANGE(CREATED_DATE)
PARTITION JAN16 VALUES LESS THAN (01-02-2016),
PARTITION FEB16 VALUES LESS THAN (01-03-2016) AND GREATER THAN(31-01-2016),//OR?
PARTITION MAR16 VALUES BETWEEN (01-03-2016) AND (31-03-2016), //OR?
Two questions..
两个问题。。
Do I need Alter statement to add partitioning mechanism or need to work with create statement?
What is the proper syntax for keeping each partition having only ONE MONTH data.
我需要 Alter 语句来添加分区机制还是需要使用 create 语句？
保持每个分区只有一个月数据的正确语法是什么。
采纳答案by hinotf
Beacuse your table non-partitioned you have two options:
因为您的表未分区，您有两个选择：
- Export data, drop table, create new patitioned table, import data.
- Use split then exchange partition method. https://oracle-base.com/articles/misc/partitioning-an-existing-table-using-exchange-partition
- 导出数据，删除表，创建新的分区表，导入数据。
- 使用拆分然后交换分区方法。https://oracle-base.com/articles/misc/partitioning-an-existing-table-using-exchange-partition
Also, if you want new partition per month read about SET INTERVAL. For example:
此外，如果您希望每个月都有新的分区，请阅读有关 SET INTERVAL 的信息。例如：
CREATE TABLE tst
(col_date DATE)
PARTITION BY RANGE (col_date) INTERVAL (NUMTOYMINTERVAL(1, 'MONTH'))
(PARTITION col_date_min VALUES LESS THAN (TO_DATE('2010-01-01', 'YYYY-MM-DD')));
回答by Lukasz Szozda
If you are using Oracle 12c Release 2you could use single ALTERto convert non-partitioned table to partitioned one (this is one way trip):
如果您正在使用，Oracle 12c Release 2您可以使用 singleALTER将非分区表转换为分区表（这是单程旅行）：
CREATE TABLE my_tab ( a NUMBER(38,0), b NUMBER(38,0));
ALTER TABLE MY_TAB MODIFY PARTITION BY RANGE (a) INTERVAL (1000) (
PARTITION p1 VALUES LESS THAN (1000)) ONLINE;
You could convert indexes too, adding:
您也可以转换索引，添加：
update indexes (index_name [local/global]);
