|
分区写法：  几大点： 1.分区表 2.分区的区exp和imp 3.自动给分区表添加索引 自动分配表空间 http://space./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./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:///2009/09/11g-interval-partitioning/
|
|
|
