如何更改现有表以在Oracle中创建范围分区 [英] How do I alter my existing table to create a range partition in Oracle

问题描述

我有一个拥有10年数据的表(我已经进行了转储).

I have existing table which has 10 years of data (I have taken dump).

我想在表中的一个日期键列上对现有表进行分区.

I would like to Range partition the existing table on one date key column within the table.

我看到的大多数示例都是使用CREATE TABLE..PARTITION BY RANGE...添加新分区的.但是我的表是现有表.

Most of the examples I see are with CREATE TABLE..PARTITION BY RANGE... to add new partitions. But my table is existing table.

我认为我需要一些ALTER语句.

I assume I need some ALTER statement.

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?

两个问题..

1. 我需要Alter语句来添加分区机制还是需要使用create语句?

1. 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.

推荐答案

由于您的表未分区，因此您有两个选择:

Beacuse your table non-partitioned you have two options:

1. 导出数据，删除表，创建新的分区表，导入数据.
2. 使用拆分然后交换分区方法. https://oracle-base.com/articles /misc/partitioning-existing-table-using-exchange-partition

此外，如果您希望每月使用新分区，请阅读有关SET INTERVAL的信息.例如:

Also, if you want new partition per month read about SET INTERVAL. For example:

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

这篇关于如何更改现有表以在Oracle中创建范围分区的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！