oracle 10g分区表,oracle10g--11gR2分区表汇总九

试验3：修改interval

试验开始前介绍：目前有一个interval分区表tab_1，分区键是id，number类型的，interval设定是100，第一个分区范围是1000，目前tab_1表中共有4条记录，分别对应分区如下：

分区一：P_TAB_1_0(1000以下)对应记录id=999/10

分区二：SYS_P173(1000-1099)对应记录id=1000

分区三：SYS_P171(1100-1199)对应记录id=1100

开始试验：

SQL> select PARTITION_NAME,partition_positionfrom user_tab_partitions where table_name = 'TAB_1';

PARTITION_NAMEPARTITION_POSITION

------------------------------ ------------------

P_TAB_1_01

SYS_P1732

SYS_P1713

SQL> select * from tab_1 ;

TIMEIDCITY_IDVALUE1 VALUE2

-------------- ---------- ---------- ---------- ----------

10-3月-11999101 a

10-3月-1110101 a

10-3月-111000101 a

10-3月-111100101 a

SQL> alter table tab_1 set interval(1000);

表已更改。

---修改interval为1000

SQL> INSERT INTO tab_1 values(to_date('2011-3-10 0:00:00','yyyy-mm-dd hh24:mi:ss'),1001,10,1,'a');

已创建1行。

SQL> select PARTITION_NAME,partition_positionfrom user_tab_partitions where table_name = 'TAB_1';

PARTITION_NAMEPARTITION_POSITION

------------------------------ ------------------

P_TAB_1_01

SYS_P1732

SYS_P1713

SQL> INSERT INTO tab_1 values(to_date('2011-3-10 0:00:00','yyyy-mm-dd hh24:mi:ss'),1200,10,1,'a');

已创建1行。

SQL> select PARTITION_NAME,partition_positionfrom user_tab_partitions where table_name = 'TAB_1';

PARTITION_NAMEPARTITION_POSITION

------------------------------ ------------------

P_TAB_1_01

SYS_P1732

SYS_P1713

SYS_P1754

--新增了一个分区

SQL> INSERT INTO tab_1 values(to_date('2011-3-10 0:00:00','yyyy-mm-dd hh24:mi:ss'),1300,10,1,'a');

已创建1行。

SQL> select PARTITION_NAME,partition_positionfrom user_tab_partitions where table_name = 'TAB_1';

PARTITION_NAMEPARTITION_POSITION

------------------------------ ------------------

P_TAB_1_01

SYS_P1732

SYS_P1713

SYS_P1754

---没有再新增分区

SQL> select * from tab_1 partition(sys_p171);

TIMEIDCITY_IDVALUE1 VALUE2

-------------- ---------- ---------- ---------- ----------

10-3月-111100101 a

SQL> select * from tab_1 partition(sys_p175);

TIMEIDCITY_IDVALUE1 VALUE2

-------------- ---------- ---------- ---------- ----------

10-3月-111200101 a

10-3月-111300101 a

通过试验可得出：原有记录分区规则保持不变，一旦超出原有分区的最后一个分区的最高值将新增分区，并且分区interval也随之改变。

试验4：range分区表改变为interval分区表

SQL> create table tab_1 (time date,

2id number,

3city_id number,

4value1 number,

5value2 varchar2(10)

6) partition by range(time)

7( partition p_tab_1_0 values less than (to_date('2011-3-10 1:00:00','yyyy-mm-dd hh24:mi:ss')) tablespace tbs_1)

8;

表已创建。

SQL> insert into tab_1 values(to_date('2011-3-10 0:00:00','yyyy-mm-dd hh24:mi:ss'),1,10,1,'a');

已创建1行。

SQL> commit;

提交完成。

SQL> alter table tab_1

2add partition p0 values less than (to_date('2011-3-10 3:00:00','yyyy-mm-dd hh24:mi:ss'))

3tablespace tbs_2

4;

表已更改。

SQL> insert into tab_1 values(to_date('2011-3-10 1:00:00','yyyy-mm-dd hh24:mi:ss'),1,10,1,'a');

已创建1行。

SQL> select PARTITION_NAME,partition_positionfrom user_tab_partitions where table_name = 'TAB_1';

PARTITION_NAMEPARTITION_POSITION

------------------------------ ------------------

P_TAB_1_01

P02

SQL> select * from tab_1;

TIMEIDCITY_IDVALUE1 VALUE2

-------------- ---------- ---------- ---------- ----------

10-3月-111101 a

10-3月-111101 a

SQL> alter table tab_1 set interval(numtodsinterval(1,'hour'));

表已更改。

SQL> select PARTITION_NAME,partition_positionfrom user_tab_partitions where table_name = 'TAB_1';

PARTITION_NAMEPARTITION_POSITION

------------------------------ ------------------

P_TAB_1_01

P02

SQL> insert into tab_1 values(to_date('2011-3-10 3:00:00','yyyy-mm-dd hh24:mi:ss'),1,10,1,'a');

已创建1行。

SQL> select PARTITION_NAME,partition_positionfrom user_tab_partitions where table_name = 'TAB_1';

PARTITION_NAMEPARTITION_POSITION

------------------------------ ------------------

P_TAB_1_01

P02

SYS_P1773

SQL> select * from tab_1;

TIMEIDCITY_IDVALUE1 VALUE2

-------------- ---------- ---------- ---------- ----------

10-3月-111101 a

10-3月-111101 a

10-3月-111101 a

SQL> select * from tab_1 partition(p_tab_1_0);

TIMEIDCITY_IDVALUE1 VALUE2

-------------- ---------- ---------- ---------- ----------

10-3月-111101 a

SQL> select * from tab_1 partition(p0);

TIMEIDCITY_IDVALUE1 VALUE2

-------------- ---------- ---------- ---------- ----------

10-3月-111101 a

SQL> select * from tab_1 partition(sys_p177);

TIMEIDCITY_IDVALUE1 VALUE2

-------------- ---------- ---------- ---------- ----------

10-3月-111101 a

试验5：range-*复合分区表改变为interval分区表

结论与试验4一致