Oracle分区技术-- interval parition实验及总结

Oracle分区技术-- interval parition实验及总结

---

---

分区写法：

几大点：

1.分区表

2.分区的区exp和imp

3.自动给分区表添加索引

自动分配表空间

http://space.itpub.net/17203031/viewspace-706173

alter table table_name drop partition partition_name;

interval分区

实验环境：

SQL> create tablespace part datafile '/u01/app/oradata/hou/part01.dbf' size 10M autoextend on next 10M maxsize 31G;

SQL> create user part identified by "part" default tablespace part;

SQL> grant connect,resource to part;

INTERVAL PARTITION

一、interval partition

11g之前，分区必须是手工或者存储过程预分配新分区。

interval 分区是oracle 11g引入的新技术，无需DBA预分配新分区，插入数据时系统会根据range列和已分配的分区自动判断新数据是否可以插入到已存在的分区中，如果不能满足插入已存在的分区，系统自动分配一个新分区来存放新插入的数据。

interal 分区减少了dba对分区的操作，保证了分区的准确安全性。

月自动创建分区

1.建表

create table month_part (c1 number,c3 date)
partition by range(c3)
interval(numtoyminterval (1,'month'))
(partition part1 values less than (to_date('2010-01-01','YYYY-MM-DD')),
 partition part2 values less than (to_date('2010-02-01','YYYY-MM-DD'))
);

2.查看现在表的分区

SQL> select TABLE_NAME,PARTITION_NAME,TABLESPACE_NAME from user_tab_partitions where table_name='MONTH_PART';

TABLE_NAME               PARTITION_NAME              TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
MONTH_PART               PART1                  PART
MONTH_PART               PART2                  PART

3.插入数据测试

begin

for i in 0..11 loop

insert into MONTH_PART values(i,add_months(to_date('2012-01-01','yyyy-mm-dd'),i));

end loop;

commit;

end;

/

4.看看数据

SQL> alter session set nls_date_format='yyyy-mm-dd';

Session altered.

SQL>  select * from MONTH_PART;

    C1 C3
---------- ----------
     0 2012-01-01
     1 2012-02-01
     2 2012-03-01
     3 2012-04-01
     4 2012-05-01
     5 2012-06-01
     6 2012-07-01
     7 2012-08-01
     8 2012-09-01
     9 2012-10-01
    10 2012-11-01
    11 2012-12-01

12 rows selected.

5.看是否自己创建分区

SQL> select TABLE_NAME,PARTITION_NAME,TABLESPACE_NAME from user_tab_partitions where table_name='MONTH_PART';

TABLE_NAME               PARTITION_NAME              TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
MONTH_PART               PART1                  PART
MONTH_PART               PART2                  PART
MONTH_PART               SYS_P11599              PART
MONTH_PART               SYS_P11600              PART
MONTH_PART               SYS_P11601              PART
MONTH_PART               SYS_P11602              PART
MONTH_PART               SYS_P11603              PART
MONTH_PART               SYS_P11604              PART
MONTH_PART               SYS_P11605              PART
MONTH_PART               SYS_P11606              PART
MONTH_PART               SYS_P11607              PART
MONTH_PART               SYS_P11608              PART
MONTH_PART               SYS_P11609              PART
MONTH_PART               SYS_P11610              PART

14 rows selected.

14个分区=创建表时定义的2个分区+插入12条数据自动产生的分区。

查看单个分区中的数据

SQL> select * from MONTH_PART partition(SYS_P11606);

    C1 C3
---------- ----------
     7 2012-08-01

二、interval partition+store in

分区表的创建目的，除了进行分区内局部扫描、便于管理外，还可以通过将分区存放在不同的表空间做到平衡分散IO的目的。所以，对分区的表空间规划，通常是DBA日常决策的一个重要内容。

interval partition中，分区的创建是由系统自动生成，这就存在一个问题：如何规划分区的存储,也就是系统自动分配的分区存放在哪些tablespace？

如果在store in后面标注上tablespaces的列表，那么新创建出的分区就会依次循环的均匀存放在各个分区上。

格式如下：

create table xx(c1,c2)

partition by range(c2)

interval(numtoyminterval (1,'month')) store in(tablespace1,tablespace2,....,tablespacen)

(partition xx......,

 partition xx......

)

实验环境准备：

添加表空间p1,p2

SQL> create tablespace p1 datafile '/u01/app/oradata/hou/p1.dbf' size 10M autoextend on next 10M maxsize 31G;
SQL> create tablespace p2 datafile '/u01/app/oradata/hou/p2.dbf' size 10M autoextend on next 10M maxsize 31G;

赋予part用户在p1和p2表空间的磁盘配额

alter user part quota unlimited on p1;

alter user part quota unlimited on p2;

1.创建分区表

create table interval_partition(c1 number,c3 date)
partition by range(c3)
interval(numtoyminterval (1,'month')) store in(p1,p2)
(partition part2010_01
  values less than (to_date('2010-02-01','yyyy-mm-dd')),
 partition part2010_02
  values less than (to_date('2010-03-01','yyyy-mm-dd'))
);

2.查看现在表的分区

SQL> select TABLE_NAME,PARTITION_NAME,TABLESPACE_NAME from user_tab_partitions where table_name='INTERVAL_PARTITION';

TABLE_NAME               PARTITION_NAME              TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
INTERVAL_PARTITION           PART2010_01              PART
INTERVAL_PARTITION           PART2010_02              PART

3.插入数据测试

begin

for i in 0..11 loop

insert into INTERVAL_PARTITION values(i,add_months(to_date('2010-01-01','yyyy-mm-dd'),i));

end loop;

commit;

end;

/

4.看看数据

SQL> alter session set nls_date_format='yyyy-mm-dd';

Session altered.

SQL> select * from INTERVAL_PARTITION;

    C1 C3
---------- ----------
     0 2010-01-01
     1 2010-02-01
     2 2010-03-01
     3 2010-04-01
     4 2010-05-01
     5 2010-06-01
     6 2010-07-01
     7 2010-08-01
     8 2010-09-01
     9 2010-10-01
    10 2010-11-01
    11 2010-12-01

12 rows selected.

5.看是否自己创建分区

SQL> select TABLE_NAME,PARTITION_NAME,TABLESPACE_NAME from user_tab_partitions where table_name='INTERVAL_PARTITION';

SQL> select TABLE_NAME,PARTITION_NAME,TABLESPACE_NAME from user_tab_partitions where table_name='INTERVAL_PARTITION' order by PARTITION_NAME;

TABLE_NAME               PARTITION_NAME              TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
INTERVAL_PARTITION           PART2010_01              PART
INTERVAL_PARTITION           PART2010_02              PART
INTERVAL_PARTITION           SYS_P11642              P1
INTERVAL_PARTITION           SYS_P11643              P2
INTERVAL_PARTITION           SYS_P11644              P1
INTERVAL_PARTITION           SYS_P11645              P2
INTERVAL_PARTITION           SYS_P11646              P1
INTERVAL_PARTITION           SYS_P11647              P2
INTERVAL_PARTITION           SYS_P11648              P1
INTERVAL_PARTITION           SYS_P11649              P2
INTERVAL_PARTITION           SYS_P11650              P1
INTERVAL_PARTITION           SYS_P11651              P2


12 rows selected.

系统自动分配的分区循环交替地存放在P1和P2表空间上，各为5个，这样就做到了I/O均衡。

当如可以看的更清楚

SQL> select TABLE_NAME,PARTITION_NAME,HIGH_VALUE,PARTITION_POSITION,TABLESPACE_NAME from user_tab_partitions where TABLE_NAME='INTERVAL_PARTITION';

TABLE_NAME         PARTITION_NAME  HIGH_VALUE                                       PARTITION_POSITION TABLESPACE_NAME
-------------------- --------------- -------------------------------------------------------------------------------- ------------------ ---------------
INTERVAL_PARTITION   PART2010_01     TO_DATE(' 2010-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA               1 PART
INTERVAL_PARTITION   PART2010_02     TO_DATE(' 2010-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA               2 PART
INTERVAL_PARTITION   SYS_P11642      TO_DATE(' 2010-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA               3 P1
INTERVAL_PARTITION   SYS_P11643      TO_DATE(' 2010-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA               4 P2
INTERVAL_PARTITION   SYS_P11644      TO_DATE(' 2010-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA               5 P1
INTERVAL_PARTITION   SYS_P11645      TO_DATE(' 2010-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA               6 P2
INTERVAL_PARTITION   SYS_P11646      TO_DATE(' 2010-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA               7 P1
INTERVAL_PARTITION   SYS_P11647      TO_DATE(' 2010-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA               8 P2
INTERVAL_PARTITION   SYS_P11648      TO_DATE(' 2010-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA               9 P1
INTERVAL_PARTITION   SYS_P11649      TO_DATE(' 2010-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA              10 P2
INTERVAL_PARTITION   SYS_P11650      TO_DATE(' 2010-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA              11 P1
INTERVAL_PARTITION   SYS_P11651      TO_DATE(' 2011-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA              12 P2

6.查看数据分布

数据根据月份正确的插入到了各个分区中

SQL> select * from INTERVAL_PARTITION partition(PART2010_01);

    C1 C3
---------- ----------
     0 2010-01-01

SQL> select * from INTERVAL_PARTITION partition(PART2010_02);

    C1 C3
---------- ----------
     1 2010-02-01

SQL> select * from INTERVAL_PARTITION partition(SYS_P11642);

    C1 C3
---------- ----------
     2 2010-03-01

SQL> select * from INTERVAL_PARTITION partition(SYS_P11643);

    C1 C3
---------- ----------
     3 2010-04-01

SQL> select * from INTERVAL_PARTITION partition(SYS_P11644);

    C1 C3
---------- ----------
     4 2010-05-01

.

.

.

SQL> select * from INTERVAL_PARTITION partition(SYS_P11651);

    C1 C3
---------- ----------
    11 2010-12-01

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

测试每月给interval 分区添加一个表空间

实验目的：每个月第一天0时新增加一个表空间X，系统自动分配新分区，然后将新分区存放到新增表空间X中，从而实现每个月的数据都存放到独立的表空间中。

给分区表INTERVAL_PARTITION添加一个新表空间P3

SQL> create tablespace p3 datafile '/u01/app/oradata/hou/p3.dbf' size 10M autoextend on next 10M maxsize 31G;

SQL> alter user part quota unlimited on p3;

为分区表 INTERVAL_PARTITION添加新的表空间

SQL> conn part/part

SQL> alter table INTERVAL_PARTITION  set store in (p1,p2,p3);

参考：

http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_3001.htm#i2087440

http://www.dba-oracle.com/t_interval_partitioning.htm

查看表的元数据

从元数据中看不到p3。

插入数据看看

begin

for i in 0..11 loop

insert into INTERVAL_PARTITION values(i,add_months(to_date('2011-02-01','yyyy-mm-dd'),i));

end loop;

commit;

end;

/

收集统计信息：

exec dbms_stats.gather_table_stats(user,'INTERVAL_PARTITION',cascade=>true);

查询分区表具体信息
SQL> select TABLE_NAME,PARTITION_NAME,HIGH_VALUE,PARTITION_POSITION,TABLESPACE_NAME,NUM_ROWS from user_tab_partitions where TABLE_NAME='INTERVAL_PARTITION';

TABLE_NAME              PARTITION_NAME  HIGH_VALUE                                                                                                            PARTITION_POSITION TABLESPACE_NAME   NUM_ROWS
---------------------------- ---------------------- ---------------------------------------------------------------------------------------------------------------------------- ------------------ --------------- ----------
INTERVAL_PARTITION   PART2010_01     TO_DATE(' 2010-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA               1   PART              1
INTERVAL_PARTITION   PART2010_02     TO_DATE(' 2010-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA               2   PART              1
INTERVAL_PARTITION   SYS_P11642      TO_DATE(' 2010-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA               3    P1              1
INTERVAL_PARTITION   SYS_P11643      TO_DATE(' 2010-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA               4    P2              1
INTERVAL_PARTITION   SYS_P11644      TO_DATE(' 2010-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA               5    P1              1
INTERVAL_PARTITION   SYS_P11645      TO_DATE(' 2010-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA               6    P2              1
INTERVAL_PARTITION   SYS_P11646      TO_DATE(' 2010-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA               7    P1              1
INTERVAL_PARTITION   SYS_P11647      TO_DATE(' 2010-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA               8    P2              1
INTERVAL_PARTITION   SYS_P11648      TO_DATE(' 2010-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA               9    P1              1
INTERVAL_PARTITION   SYS_P11649      TO_DATE(' 2010-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA              10   P2              1
INTERVAL_PARTITION   SYS_P11650      TO_DATE(' 2010-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA              11   P1              1
INTERVAL_PARTITION   SYS_P11651      TO_DATE(' 2011-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA              12   P2              1
INTERVAL_PARTITION   SYS_P11652      TO_DATE(' 2011-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA              13   P1              1
INTERVAL_PARTITION   SYS_P11653      TO_DATE(' 2011-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA              14   P2              1
INTERVAL_PARTITION   SYS_P11654      TO_DATE(' 2011-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA              15   P3              1
INTERVAL_PARTITION   SYS_P11655      TO_DATE(' 2011-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA              16   P1              1
INTERVAL_PARTITION   SYS_P11656      TO_DATE(' 2011-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA              17   P2              1
INTERVAL_PARTITION   SYS_P11657      TO_DATE(' 2011-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA              18   P3              1
INTERVAL_PARTITION   SYS_P11658      TO_DATE(' 2011-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA              19   P1              1
INTERVAL_PARTITION   SYS_P11659      TO_DATE(' 2011-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA              20   P2              1
INTERVAL_PARTITION   SYS_P11660      TO_DATE(' 2011-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA              21   P3              1
INTERVAL_PARTITION   SYS_P11661      TO_DATE(' 2011-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA              22   P1              1
INTERVAL_PARTITION   SYS_P11662      TO_DATE(' 2011-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA              23   P2              1
INTERVAL_PARTITION   SYS_P11663      TO_DATE(' 2012-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA              24   P3              1
INTERVAL_PARTITION   SYS_P11664      TO_DATE(' 2012-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA              25   P1              

上面绿色部分是插入数据产生的新分区。插入新时间段的数据，系统自动产生分区，用循环方式将新分区存放到P1 P2 P3分区中。

*做这个实验的本意是：每个月第一天0时新增加一个表空间X，系统自动分配新分区，然后将新分区存放到新增表空间X中，从而实现每个月的数据都存放到独立的表空间中。

通过实验，看来我的想法无法实现，oracle并不是发现新增表空间后，就把新增的分区存放到新的表空间，而是依然采用循环方式将新分区放到表空间中。

有个担忧：假如分区表INTERVAL_PARTITION可以将分区存放到P1 P2两个表空间，且这个分区表已经使用很久，P1 P2中存放着大量分区（也就是大量数据），这个时候P1 P2的分区数应该是均衡的，如果加入P3表空间，oracle采用什么方法实现P1 P2 P3的数据均衡呢？

开始实验：

1.摘除P3表空

SQL> alter table INTERVAL_PARTITION set store in(p1,p2);

Table altered.

但是查看INTERVAL_PARTITION的分区情况，发现存放到P3表空间的分区依然存在！！

SQL> 
SQL> select TABLE_NAME,PARTITION_NAME,HIGH_VALUE,PARTITION_POSITION,TABLESPACE_NAME,NUM_ROWS from user_tab_partitions where TABLE_NAME='INTERVAL_PARTITION';

TABLE_NAME         PARTITION_NAME  HIGH_VALUE                                       PARTITION_POSITION TABLESPACE_NAME   NUM_ROWS
-------------------- --------------- -------------------------------------------------------------------------------- ------------------ --------------- ----------
INTERVAL_PARTITION   PART2010_01     TO_DATE(' 2010-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA               1 PART              1
INTERVAL_PARTITION   PART2010_02     TO_DATE(' 2010-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA               2 PART              1
INTERVAL_PARTITION   SYS_P11642      TO_DATE(' 2010-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA               3 P1              1
INTERVAL_PARTITION   SYS_P11643      TO_DATE(' 2010-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA               4 P2              1
INTERVAL_PARTITION   SYS_P11644      TO_DATE(' 2010-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA               5 P1              1
INTERVAL_PARTITION   SYS_P11645      TO_DATE(' 2010-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA               6 P2              1
INTERVAL_PARTITION   SYS_P11646      TO_DATE(' 2010-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA               7 P1              1
INTERVAL_PARTITION   SYS_P11647      TO_DATE(' 2010-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA               8 P2              1
INTERVAL_PARTITION   SYS_P11648      TO_DATE(' 2010-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA               9 P1              1
INTERVAL_PARTITION   SYS_P11649      TO_DATE(' 2010-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA              10 P2              1
INTERVAL_PARTITION   SYS_P11650      TO_DATE(' 2010-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA              11 P1              1
INTERVAL_PARTITION   SYS_P11651      TO_DATE(' 2011-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA              12 P2              1
INTERVAL_PARTITION   SYS_P11652      TO_DATE(' 2011-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA              13 P1              1
INTERVAL_PARTITION   SYS_P11653      TO_DATE(' 2011-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA              14 P2              1
INTERVAL_PARTITION   SYS_P11654      TO_DATE(' 2011-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA              15 P3              1
INTERVAL_PARTITION   SYS_P11655      TO_DATE(' 2011-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA              16 P1              1
INTERVAL_PARTITION   SYS_P11656      TO_DATE(' 2011-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA              17 P2              1
INTERVAL_PARTITION   SYS_P11657      TO_DATE(' 2011-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA              18 P3              1
INTERVAL_PARTITION   SYS_P11658      TO_DATE(' 2011-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA              19 P1              1
INTERVAL_PARTITION   SYS_P11659      TO_DATE(' 2011-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA              20 P2              1
INTERVAL_PARTITION   SYS_P11660      TO_DATE(' 2011-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA              21 P3              1
INTERVAL_PARTITION   SYS_P11661      TO_DATE(' 2011-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA              22 P1              1
INTERVAL_PARTITION   SYS_P11662      TO_DATE(' 2011-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA              23 P2              1
INTERVAL_PARTITION   SYS_P11663      TO_DATE(' 2012-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA              24 P3              1
INTERVAL_PARTITION   SYS_P11664      TO_DATE(' 2012-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA              25 P1              1

看存放在P3的分区是否有数据,里面还有数据，我刚才摘除P3空间的操作没有生效吗？

SQL> select * from INTERVAL_PARTITION partition(SYS_P11663);

    C1 C3
---------- ------------
    10 01-DEC-11

SQL> select * from INTERVAL_PARTITION partition(SYS_P11660);

    C1 C3
---------- ------------
     7 01-SEP-11

SQL> select * from INTERVAL_PARTITION partition(SYS_P11657);

    C1 C3
---------- ------------
     4 01-JUN-11

SQL> select * from INTERVAL_PARTITION partition(SYS_P11654);

    C1 C3
---------- ------------
     1 01-MAR-11

2.插入新数据，看看新分区是否还存放在P3表空间

begin

for i in 0..11 loop

insert into INTERVAL_PARTITION values(i,add_months(to_date('2012-02-02','yyyy-mm-dd'),i));

end loop;

commit;

end;

/

SQL> select TABLE_NAME,PARTITION_NAME,HIGH_VALUE,PARTITION_POSITION,TABLESPACE_NAME,NUM_ROWS from user_tab_partitions where TABLE_NAME='INTERVAL_PARTITION';

TABLE_NAME         PARTITION_NAME  HIGH_VALUE                                       PARTITION_POSITION TABLESPACE_NAME   NUM_ROWS
-------------------- --------------- -------------------------------------------------------------------------------- ------------------ --------------- ----------
INTERVAL_PARTITION   PART2010_01     TO_DATE(' 2010-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA               1 PART              1
INTERVAL_PARTITION   PART2010_02     TO_DATE(' 2010-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA               2 PART              1
INTERVAL_PARTITION   SYS_P11642      TO_DATE(' 2010-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA               3 P1              1
INTERVAL_PARTITION   SYS_P11643      TO_DATE(' 2010-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA               4 P2              1
INTERVAL_PARTITION   SYS_P11644      TO_DATE(' 2010-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA               5 P1              1
INTERVAL_PARTITION   SYS_P11645      TO_DATE(' 2010-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA               6 P2              1
INTERVAL_PARTITION   SYS_P11646      TO_DATE(' 2010-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA               7 P1              1
INTERVAL_PARTITION   SYS_P11647      TO_DATE(' 2010-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA               8 P2              1
INTERVAL_PARTITION   SYS_P11648      TO_DATE(' 2010-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA               9 P1              1
INTERVAL_PARTITION   SYS_P11649      TO_DATE(' 2010-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA              10 P2              1
INTERVAL_PARTITION   SYS_P11650      TO_DATE(' 2010-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA              11 P1              1
INTERVAL_PARTITION   SYS_P11651      TO_DATE(' 2011-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA              12 P2              1
INTERVAL_PARTITION   SYS_P11652      TO_DATE(' 2011-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA              13 P1              1
INTERVAL_PARTITION   SYS_P11653      TO_DATE(' 2011-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA              14 P2              1
INTERVAL_PARTITION   SYS_P11654      TO_DATE(' 2011-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA              15 P3              1
INTERVAL_PARTITION   SYS_P11655      TO_DATE(' 2011-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA              16 P1              1
INTERVAL_PARTITION   SYS_P11656      TO_DATE(' 2011-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA              17 P2              1
INTERVAL_PARTITION   SYS_P11657      TO_DATE(' 2011-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA              18 P3              1
INTERVAL_PARTITION   SYS_P11658      TO_DATE(' 2011-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA              19 P1              1
INTERVAL_PARTITION   SYS_P11659      TO_DATE(' 2011-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA              20 P2              1
INTERVAL_PARTITION   SYS_P11660      TO_DATE(' 2011-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA              21 P3              1
INTERVAL_PARTITION   SYS_P11661      TO_DATE(' 2011-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA              22 P1              1
INTERVAL_PARTITION   SYS_P11662      TO_DATE(' 2011-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA              23 P2              1
INTERVAL_PARTITION   SYS_P11663      TO_DATE(' 2012-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA              24 P3              1
INTERVAL_PARTITION   SYS_P11664      TO_DATE(' 2012-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA              25 P1              1
INTERVAL_PARTITION   SYS_P11665      TO_DATE(' 2012-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA              26 P2
INTERVAL_PARTITION   SYS_P11666      TO_DATE(' 2012-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA              27 P1
INTERVAL_PARTITION   SYS_P11667      TO_DATE(' 2012-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA              28 P2
INTERVAL_PARTITION   SYS_P11668      TO_DATE(' 2012-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA              29 P1
INTERVAL_PARTITION   SYS_P11669      TO_DATE(' 2012-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA              30 P2
INTERVAL_PARTITION   SYS_P11670      TO_DATE(' 2012-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA              31 P1
INTERVAL_PARTITION   SYS_P11671      TO_DATE(' 2012-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA              32 P2
INTERVAL_PARTITION   SYS_P11672      TO_DATE(' 2012-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA              33 P1
INTERVAL_PARTITION   SYS_P11673      TO_DATE(' 2012-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA              34 P2
INTERVAL_PARTITION   SYS_P11674      TO_DATE(' 2012-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA              35 P1
INTERVAL_PARTITION   SYS_P11675      TO_DATE(' 2013-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA              36 P2
INTERVAL_PARTITION   SYS_P11676      TO_DATE(' 2013-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA              37 P1

上面绿色部分是新产生的分区，果然新分区不存放到P3表空间中，那么P3表空间中的数据为何依然存在呢？

试着删除P3表空间，看看数据是否被删除。

P3中含有的数据

10 01-DEC-11

7 01-SEP-11

4 01-JUN-11

1 01-MAR-11

SQL> conn / as sysdba
Connected.
SQL> drop tablespace p3 including contents and datafiles;
drop tablespace p3 including contents and datafiles
*
ERROR at line 1:
ORA-14404: partitioned table contains partitions in a different tablespace

P3表空间还有分区表的分区，看看能不能把P3表空间中的分区移到P1 P2中

conn part/part

alter table INTERVAL_PARTITION move partition SYS_P11654 tablespace p1;

alter table INTERVAL_PARTITION move partition SYS_P11657 tablespace p1;

alter table INTERVAL_PARTITION move partition SYS_P11660 tablespace p1;

alter table INTERVAL_PARTITION move partition SYS_P11663 tablespace p1;

SQL> select TABLE_NAME,PARTITION_NAME,HIGH_VALUE,PARTITION_POSITION,TABLESPACE_NAME,NUM_ROWS from user_tab_partitions where TABLE_NAME='INTERVAL_PARTITION';

TABLE_NAME         PARTITION_NAME  HIGH_VALUE                                       PARTITION_POSITION TABLESPACE_NAME   NUM_ROWS
-------------------- --------------- -------------------------------------------------------------------------------- ------------------ --------------- ----------
INTERVAL_PARTITION   PART2010_01     TO_DATE(' 2010-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA               1 PART              1
INTERVAL_PARTITION   PART2010_02     TO_DATE(' 2010-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA               2 PART              1
INTERVAL_PARTITION   SYS_P11642      TO_DATE(' 2010-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA               3 P1              1
INTERVAL_PARTITION   SYS_P11643      TO_DATE(' 2010-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA               4 P2              1
INTERVAL_PARTITION   SYS_P11644      TO_DATE(' 2010-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA               5 P1              1
INTERVAL_PARTITION   SYS_P11645      TO_DATE(' 2010-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA               6 P2              1
INTERVAL_PARTITION   SYS_P11646      TO_DATE(' 2010-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA               7 P1              1
INTERVAL_PARTITION   SYS_P11647      TO_DATE(' 2010-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA               8 P2              1
INTERVAL_PARTITION   SYS_P11648      TO_DATE(' 2010-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA               9 P1              1
INTERVAL_PARTITION   SYS_P11649      TO_DATE(' 2010-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA              10 P2              1
INTERVAL_PARTITION   SYS_P11650      TO_DATE(' 2010-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA              11 P1              1
INTERVAL_PARTITION   SYS_P11651      TO_DATE(' 2011-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA              12 P2              1
INTERVAL_PARTITION   SYS_P11652      TO_DATE(' 2011-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA              13 P1              1
INTERVAL_PARTITION   SYS_P11653      TO_DATE(' 2011-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA              14 P2              1
INTERVAL_PARTITION   SYS_P11654      TO_DATE(' 2011-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA              15 P1              1
INTERVAL_PARTITION   SYS_P11655      TO_DATE(' 2011-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA              16 P1              1
INTERVAL_PARTITION   SYS_P11656      TO_DATE(' 2011-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA              17 P2              1
INTERVAL_PARTITION   SYS_P11657      TO_DATE(' 2011-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA              18 P1              1
INTERVAL_PARTITION   SYS_P11658      TO_DATE(' 2011-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA              19 P1              1
INTERVAL_PARTITION   SYS_P11659      TO_DATE(' 2011-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA              20 P2              1
INTERVAL_PARTITION   SYS_P11660      TO_DATE(' 2011-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA              21 P1              1
INTERVAL_PARTITION   SYS_P11661      TO_DATE(' 2011-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA              22 P1              1
INTERVAL_PARTITION   SYS_P11662      TO_DATE(' 2011-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA              23 P2              1
INTERVAL_PARTITION   SYS_P11663      TO_DATE(' 2012-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA              24 P1              1
INTERVAL_PARTITION   SYS_P11664      TO_DATE(' 2012-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA              25 P1              1
INTERVAL_PARTITION   SYS_P11665      TO_DATE(' 2012-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA              26 P2
INTERVAL_PARTITION   SYS_P11666      TO_DATE(' 2012-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA              27 P1
INTERVAL_PARTITION   SYS_P11667      TO_DATE(' 2012-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA              28 P2
INTERVAL_PARTITION   SYS_P11668      TO_DATE(' 2012-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA              29 P1
INTERVAL_PARTITION   SYS_P11669      TO_DATE(' 2012-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA              30 P2
INTERVAL_PARTITION   SYS_P11670      TO_DATE(' 2012-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA              31 P1
INTERVAL_PARTITION   SYS_P11671      TO_DATE(' 2012-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA              32 P2
INTERVAL_PARTITION   SYS_P11672      TO_DATE(' 2012-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA              33 P1
INTERVAL_PARTITION   SYS_P11673      TO_DATE(' 2012-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA              34 P2
INTERVAL_PARTITION   SYS_P11674      TO_DATE(' 2012-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA              35 P1
INTERVAL_PARTITION   SYS_P11675      TO_DATE(' 2013-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA              36 P2
INTERVAL_PARTITION   SYS_P11676      TO_DATE(' 2013-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA              37 P1

37 rows selected.

上面红色部分就是以前在P3表空间的分区，现在都移到了P1表空间。

再次删除P3表空间

SQL> conn / as sysdba
Connected.
SQL> drop tablespace p3 including contents and datafiles;

Tablespace dropped.

OK，成功！

*************************

删除分区表中表空间的顺序:

1.摘除某个表空间，store in 中写要保留的表空间即可

alter table partition_table set store in(tablespace1,tabelspace2);

2.将要删除的表空间中的分区移到保留的表空间中

alter table partition_table move partition xx tablespace xx;

3.删除表空间

drop tablespace xx including contents and datafiles;

***********************

3.向P1表空间中的SYS_P11642 分区大量插入数据

SQL> select * from INTERVAL_PARTITION partition(SYS_P11642);

   

  C1  C3
---------- ------------
     2 01-MAR-10

SQL> ALTER TABLE INTERVAL_PARTITION NOLOGGING;

Table altered.

begin
for i in 0..27900040 loop
insert into INTERVAL_PARTITION values(i,to_date('2010-03-01','yyyy-mm-dd'));
end loop;
commit;
end;
  /

SQL> select FILE_NAME,BYTES/1024/1024 as M from dba_data_files;

FILE_NAME                            M
-------------------------------------------------- ----------
/u01/app/oradata/hou/users01.dbf            699.5
/u01/app/oradata/hou/undotbs01.dbf             1405
/u01/app/oradata/hou/sysaux01.dbf             613.0625
/u01/app/oradata/hou/system01.dbf             1170
/u01/app/oradata/hou/example01.dbf              100
/u01/app/oradata/hou/p1.dbf                531.5
/u01/app/oradata/hou/p2.dbf                   10
/u01/app/oradata/hou/part01.dbf                70

P1表空间已经达到500多兆，而P2只有10兆。

再次插入新时间段数据，看看新分区分配到什么表空间。

begin

for i in 0..11 loop

insert into INTERVAL_PARTITION values(i,add_months(to_date('2013-02-02','yyyy-mm-dd'),i));

end loop;

commit;

end;

/

下面是新分配的分区，发现依然存在循环交替使用p1 p2表空间的情况。

SQL> select TABLE_NAME,PARTITION_NAME,HIGH_VALUE,PARTITION_POSITION,TABLESPACE_NAME,NUM_ROWS from user_tab_partitions where TABLE_NAME='INTERVAL_PARTITION';

TABLE_NAME         PARTITION_NAME  HIGH_VALUE                                       PARTITION_POSITION TABLESPACE_NAME          NUM_ROWS
-------------------- --------------- -------------------------------------------------------------------------------- ------------------ ------------------------------ ----------

INTERVAL_PARTITION   SYS_P11677      TO_DATE(' 2013-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA              38 P2                     1
INTERVAL_PARTITION   SYS_P11678      TO_DATE(' 2013-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA              39 P1                     1
INTERVAL_PARTITION   SYS_P11679      TO_DATE(' 2013-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA              40 P2                     1
INTERVAL_PARTITION   SYS_P11680      TO_DATE(' 2013-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA              41 P1                     1
INTERVAL_PARTITION   SYS_P11681      TO_DATE(' 2013-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA              42 P2                     1
INTERVAL_PARTITION   SYS_P11682      TO_DATE(' 2013-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA              43 P1                     1
INTERVAL_PARTITION   SYS_P11683      TO_DATE(' 2013-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA              44 P2                     1
INTERVAL_PARTITION   SYS_P11684      TO_DATE(' 2013-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA              45 P1                     1
INTERVAL_PARTITION   SYS_P11685      TO_DATE(' 2013-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA              46 P2                     1
INTERVAL_PARTITION   SYS_P11686      TO_DATE(' 2013-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA              47 P1                     1
INTERVAL_PARTITION   SYS_P11687      TO_DATE(' 2014-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA              48 P2                     1
INTERVAL_PARTITION   SYS_P11688      TO_DATE(' 2014-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA              49 P1                     1

初步结论：interval并不能根据表空间使用率决定新分区分配到低使用率的表空间上，它只是遵循循环交替使用p1 p2表空间来分配新增的分区。

现在p1表空间只有一个数据文件p1.dbf,把尺寸固定到530M并且无法自动扩展，大量往P1中插数据，看看会怎么样

begin

for i in 0..50000 loop
insert into INTERVAL_PARTITION values(i,to_date('2010-03-01','yyyy-mm-dd'));
end loop;
commit;
end;

/

*
ERROR at line 1:
ORA-01688: unable to extend table PART.INTERVAL_PARTITION partition SYS_P11642 by 1024 in tablespace P1
ORA-06512: at line 3

p2表空间满， SYS_P11642分区不能再向p2中插入数据，从而可以看出，oracle并不能根据表空间的利用率自动均衡分配分区，

继续插入数据

begin

for i in 0..11 loop

insert into INTERVAL_PARTITION values(i,add_months(to_date('2014-02-02','yyyy-mm-dd'),i));

end loop;

commit;

end;

/

下面就是插入新数据后产生的新分区，发现oracle依然固执地循环分配新分区到P1 P2表空间，P2表空间已经满了，你还分配什么！

SQL> select TABLE_NAME,PARTITION_NAME,HIGH_VALUE,PARTITION_POSITION,TABLESPACE_NAME,NUM_ROWS from user_tab_partitions where TABLE_NAME='INTERVAL_PARTITION';

TABLE_NAME         PARTITION_NAME  HIGH_VALUE                                       PARTITION_POSITION TABLESPACE_NAME          NUM_ROWS
-------------------- --------------- -------------------------------------------------------------------------------- ------------------ ------------------------------ ----------

INTERVAL_PARTITION   SYS_P11689      TO_DATE(' 2014-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA              50 P2
INTERVAL_PARTITION   SYS_P11690      TO_DATE(' 2014-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA              51 P1
INTERVAL_PARTITION   SYS_P11691      TO_DATE(' 2014-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA              52 P2
INTERVAL_PARTITION   SYS_P11692      TO_DATE(' 2014-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA              53 P1
INTERVAL_PARTITION   SYS_P11693      TO_DATE(' 2014-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA              54 P2
INTERVAL_PARTITION   SYS_P11694      TO_DATE(' 2014-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA              55 P1
INTERVAL_PARTITION   SYS_P11695      TO_DATE(' 2014-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA              56 P2
INTERVAL_PARTITION   SYS_P11696      TO_DATE(' 2014-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA              57 P1
INTERVAL_PARTITION   SYS_P11697      TO_DATE(' 2014-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA              58 P2
INTERVAL_PARTITION   SYS_P11698      TO_DATE(' 2014-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA              59 P1
INTERVAL_PARTITION   SYS_P11699      TO_DATE(' 2015-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA              60 P2
INTERVAL_PARTITION   SYS_P11700      TO_DATE(' 2015-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA              61 P1

总终结论：11g之前，分区表都要预分配分区。而11g 的interval partition 技术，在插入数据时会根据range列自动分配新分区，更加自动、简单化。

              同时，interval partition可以指定将分区创建在指定的表空间中（store in字子句指定），oracle采用循环交替分配新分区到各个表空间，这个动作极其机械化，只是循环！不会根据表空间的利用率，智能均衡表空间的里  用率！（如存在A B两个表空间，A表空间已经满了，B表空间数据量很少，oracle不会把新分区全部分配到B表空间，而是依然循环分配新分区到A B两个表空间！）从而可见，interval partition实现I/O均衡的能力也不过如此，没有想象的那么智能。

想要实现想法，看来还是要采用传统的利用存储过程定时预分配表空间和分区的方法。

三、普通range分区表可以转换为interval分区表

http://gavinsoorma.com/2009/09/11g-interval-partitioning/

In versions prior to Oracle 11g, we were very likely to have faced the error shown below especially if we used range partitioning and the partition column was a date field.

We needed to ensure that we precreated all the partitions before hand based on the expected values of data that would be inserted (or updated) in a table.

ERROR at line 1:
ORA-14400: inserted partition key does not map to any partition

While we could use the MAXVALUE clause to create a ‘catch-all’ partition, this would not help us when we would like to perform any partition maintenance operations based on a date range or if wanted to use the partitioning feature to perform some kind of data archiving at the partition level.

11g Interval Partitioning

In Oracle 11g, the creation of partitions (for range) is automated and partitions are created as and when needed and takes the task of managing the creation of new partitions from the DBA. All that is required is to define the interval criteria and create the first partition. Subsequent partitions are created automatically based on the interval criteria.

create table mypart
   (ename varchar2(20), doj date)
   partition by range (doj) INTERVAL (NUMTOYMINTERVAL(1,'YEAR')) STORE IN (tbs1,tbs2)
  (partition p_2009 values less than (to_date('01-JAN-2010','DD-MON-YYYY'))
  )
;

Note the NUMTOYMINTERVAL is an SQL Funtion used to convert a number to an INTERVAL YEAR TO MONTH literal. The accepted values are ‘YEAR’ and ‘MONTH’.

The STORE IN clause will create in the partitions in a round robin manner in tablespaces tbs1 and tbs2 as we will see below.

Let us now insert some values into the table.

SQL> insert into mypart
  2   values
  3   ('Tom','21-SEP-2009');

1 row created.

SQL> insert into mypart
  2  values
  3  ('Joe','02-JAN-2010');

1 row created.

What has happened after the second insert? – a new partition ‘SYS_P42′ has been created for the year 2010 with a high value of ’01-JAN-2011’.

The first partition that we had precreated has been created in the default tablespace USERS since no tablespace name has been prescribed and the new partition has been created in the tablespace TBS2. The next partition that comes along will be created in tablespace TBS1 and so on.

SQL> select partition_name,high_value from user_tab_partitions
  2  where table_name='MYPART';

PARTITION_NAME                 HIGH_VALUE
------------------------------ --------------------------------------------------------------------------------
P_2009                         TO_DATE(' 2010-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SYS_P42                        TO_DATE(' 2011-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

SQL> select partition_name,tablespace_name from user_tab_partitions
  2  where table_name='MYPART';

PARTITION_NAME                 TABLESPACE_NAME
------------------------------ ------------------------------
P_2009                         USERS
SYS_P43                        TBS2

We can use the ALTER TABLE SET INTERVAL command to convert a range partitioned table to an interval partitioned table as shown below.

SQL> create table mypart2
   (ename varchar2(20), doj date)
   partition by range (doj)
  (partition p_2009 values less than (to_date('01-JAN-2010','DD-MON-YYYY'))
  )
;    2    3    4    5    6

Table created.

SQL> alter table mypart2
  2  SET INTERVAL (NUMTOYMINTERVAL(1,'YEAR');

Table altered.

We need to keep the following points in mind when using Interval Partitioning:

The partitioning column can be only one and it must be of type NUMBER or DATE We cannot use the MAXVALUE clause We cannot use this with Index Organised Tables

---

---

Interval类型的分区的store in属性的表空间存储在哪个表，或通过哪个视图可以查询呢？如何找到这个表呢？小麦苗通过10046事件找到了，是sys.INSERT_TSN_LIST$表。记录一下：




  CREATE TABLE TB_INTERVAL(time_col date) 
PARTITION BY RANGE (time_col) 
          INTERVAL ( NUMTOYMINTERVAL(1, 'month'))     STORE IN (APP1TBS, APP2TBS, IDXTBS, TS_LHR)
          (PARTITION P0 VALUES LESS THAN (TO_DATE('1-1-2010', 'dd-mm-yyyy'))); 

点击(此处)折叠或打开

1. [oracle@rhel6lhr ~]$ sqlplus / as sysdba
   
2. 
   
3. SQL*Plus: Release 11.2.0.3.0 Production on Thu Apr 13 00:38:39 2017
   
4. 
   
5. Copyright (c) 1982, 2011, Oracle. All rights reserved.
   
6. 
   
7. 
   
8. Connected to:
   
9. Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
   
10. With the Partitioning, Automatic Storage Management, OLAP, Data Mining
    
11. and Real Application Testing options
    
12. 
    
13. SYS@orclasm > ALTER SESSION SET EVENTS '10046 trace name context forever,level 12';
    
14. 
    
15. Session altered.
    
16. 
    
17. SYS@orclasm > alter table lhr.TB_INTERVAL set store in (APP1TBS, APP2TBS, IDXTBS);
    
18. 
    
19. Table altered.
    
20. 
    
21. SYS@orclasm > ALTER SESSION SET EVENTS '10046 trace name context off';
    
22. 
    
23. Session altered.
    
24. 
    
25. SYS@orclasm > SELECT VALUE FROM V$DIAG_INFO;
    
26. 
    
27. VALUE
    
28. --------------------------------------------------------------------------------
    
29. TRUE
    
30. /u01/app/oracle
    
31. /u01/app/oracle/diag/rdbms/orclasm/orclasm
    
32. /u01/app/oracle/diag/rdbms/orclasm/orclasm/trace
    
33. /u01/app/oracle/diag/rdbms/orclasm/orclasm/alert
    
34. /u01/app/oracle/diag/rdbms/orclasm/orclasm/incident
    
35. /u01/app/oracle/diag/rdbms/orclasm/orclasm/cdump
    
36. /u01/app/oracle/diag/rdbms/orclasm/orclasm/hm
    
37. /u01/app/oracle/diag/rdbms/orclasm/orclasm/trace/orclasm_ora_28836.trc
    
38. 0
    
39. 0
    
40. 
    
41. 11 rows selected.
    
42. 
    
43. SYS@orclasm >

点击(此处)折叠或打开

1. [root@rhel6lhr ~]# tkprof /u01/app/oracle/diag/rdbms/orclasm/orclasm/trace/orclasm_ora_28836.trc
   
2. output = b.txt
   
3. 
   
4. TKPROF: Release 11.2.0.3.0 - Development on Thu Apr 13 00:41:19 2017
   
5. 
   
6. Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
   
7. 
   
8. 
   
9. [root@rhel6lhr ~]#

 在b.txt中寻找update、delete、insert语句即可在b.txt的最后找到如下部分：

点击(此处)折叠或打开

1. delete from insert_tsn_list$
   
2. where
   
3.  bo# = :1
   
4. 
   
5. 
   
6. 
   
7. insert into insert_tsn_list$ (bo#, position#, ts#)
   
8. values
   
9.  (:1, :2, :3)
   

   查询该表即可验证，该表记录了interval分区的store in属性值。则，查询SQL如下：

点击(此处)折叠或打开

1. SELECT O.OBJECT_NAME,
   
2.        (SELECT NAME FROM V$TABLESPACE TS WHERE TS.TS# = A.TS#) TS_NAME
   
3.   FROM SYS.INSERT_TSN_LIST$ A, SYS.TS$ B, DBA_OBJECTS O
   
4.  WHERE A.TS# = B.TS#
   
5.    AND O.OBJECT_ID = A.BO#
   
6.    AND O.OBJECT_NAME = 'TB_INTERVAL'
   
7.    AND O.OWNER = 'LHR'
   
8.  ORDER BY A.POSITION#;
   
9. 
   
10. 
    
11. 
    
12. SELECT O.OWNER, O.OBJECT_NAME, B.NAME TABLESPACE, O.OBJECT_TYPE
    
13.   FROM SYS.INSERT_TSN_LIST$ A, SYS.TS$ B, DBA_OBJECTS O
    
14.  WHERE A.TS# = B.TS#
    
15.    AND O.OBJECT_ID = A.BO#
    
16.  ORDER BY A.POSITION#

点击(此处)折叠或打开

1. SYS@orclasm > SELECT O.OBJECT_NAME,
   
2.   2 (SELECT NAME FROM V$TABLESPACE TS WHERE TS.TS# = A.TS#) TS_NAME
   
3.   3 FROM SYS.INSERT_TSN_LIST$ A, SYS.TS$ B, DBA_OBJECTS O
   
4.   4 WHERE A.TS# = B.TS#
   
5.   5 AND O.OBJECT_ID = A.BO#
   
6.   6 AND O.OBJECT_NAME = 'TB_INTERVAL'
   
7.   7 AND O.OWNER = 'LHR'
   
8.   8 ORDER BY A.POSITION#;
   
9. 
   
10. OBJECT_NAME TS_NAME
    
11. -------------------------------------------------------------------------------------------------------------------------------- ------------------------------
    
12. TB_INTERVAL APP1TBS
    
13. TB_INTERVAL APP2TBS
    
14. TB_INTERVAL IDXTBS
    
15. 
    
16. SYS@orclasm >

  将 SYS . INSERT_TSN_LIST$拿到MOS中查询，则查到了  (文档 ID 1594740.1)，也说明了该问题。

---

---

Which Data Dictionary View Lists The Tablespace Rotation (STORE IN) For Interval Partitions? (文档 ID 1594740.1)

In this Document

Symptoms

Changes

Cause

Solution

References

---

APPLIES TO:

Oracle Database - Enterprise Edition - Version 11.1.0.7 and later
Information in this document applies to any platform.

SYMPTOMS

 At present, there is no dictionary view available that holds information about the tablespaces specified as part of the STORE IN clause for interval partitioned tables.

CHANGES

 None.

CAUSE

 A bug report has been filed to fix this situation.

Bug 10080569 - STORE IN () DEFINITION OF INTERVAL PARTITIONING NOT IN DATA DICTIONARY VIEWS

SOLUTION

 To work around this issue, you may define the following view to retrieve the information about the STORE IN () clause:

CREATE VIEW STORE_IN_TBS AS
select o.owner, o.object_name, B.NAME tablespace , o.object_type
from sys.INSERT_TSN_LIST$ a, sys.ts$ b, dba_objects o
where A.TS# = b.ts#
and o.object_id = A.BO#
order by a.position#

REFERENCES

NOTE:1326111.1  - How Default Tablespace Works for an Interval Partition?

---

---

About Me

............................................................................................................................... ● 本文作者：小麦苗，只专注于数据库的技术，更注重技术的运用 ● 本文在itpub（http://blog.itpub.net/26736162）、博客园（http://www.cnblogs.com/lhrbest）和个人微信公众号（xiaomaimiaolhr）上有同步更新 ● 本文itpub地址：http://blog.itpub.net/26736162/abstract/1/ ● 本文博客园地址：http://www.cnblogs.com/lhrbest ● 本文pdf版及小麦苗云盘地址：http://blog.itpub.net/26736162/viewspace-1624453/ ● QQ群：230161599     微信群：私聊 ● 联系我请加QQ好友(646634621)，注明添加缘由 ● 于 2017-04-12 22:00 ~ 2017-04-13 03:00 在魔都完成 ● 文章内容来源于小麦苗的学习笔记，部分整理自网络，若有侵权或不当之处还请谅解 ● 版权所有，欢迎分享本文，转载请保留出处 ............................................................................................................................... 拿起手机使用微信客户端扫描下边的左边图片来关注小麦苗的微信公众号：xiaomaimiaolhr，扫描右边的二维码加入小麦苗的QQ群，学习最实用的数据库技术。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26736162/viewspace-2137151/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26736162/viewspace-2137151/