[20130513]Interval Partition的一些问题.txt
Interval Partition管理是11G的新特性,简单了分区管理,本文简单探讨维护时遇到的问题.
1.建立测试环境:
SQL> @ver
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
CREATE TABLE t
(
id number,
create_date date
)
partition by range(create_date) interval(numtoyminterval(1,'MONTH'))
(
partition p201303 values less than (date '2013-03-01'),
partition p201304 values less than (date '2013-04-01'),
partition p201305 values less than (date '2013-05-01')
);
--可以发现interval=NO.
2.插入数据看看.
--可以发现建立一个新的分区. HIGH_VALUE=TO_DATE(' 2013-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN').
3.删除分区:
--最后一个分区要删除如何处理呢?
4.简单一点是执行如下,先删除interval特性再建立:
alter table t set interval ();
然后在执行.
alter table t set interval (NUMTOYMINTERVAL(1,'month'));
自己测试一下,实际上执行如下:
--这样就可以很好的解决这个问题.
Interval Partition管理是11G的新特性,简单了分区管理,本文简单探讨维护时遇到的问题.
1.建立测试环境:
export NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'
SQL> @ver
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
CREATE TABLE t
(
id number,
create_date date
)
partition by range(create_date) interval(numtoyminterval(1,'MONTH'))
(
partition p201303 values less than (date '2013-03-01'),
partition p201304 values less than (date '2013-04-01'),
partition p201305 values less than (date '2013-05-01')
);
SQL> column high_value format a85
SQL> select partition_name, interval, high_value from dba_tab_partitions where table_owner=user and table_name = 'T' order by partition_position;
PARTITION_NAME INT HIGH_VALUE
------------------------------ --- -------------------------------------------------------------------------------------
P201303 NO TO_DATE(' 2013-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
P201304 NO TO_DATE(' 2013-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
P201305 NO TO_DATE(' 2013-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
--可以发现interval=NO.
2.插入数据看看.
insert into t values(1 ,date'2013-05-11');
commit;
SQL> select partition_name, interval, high_value from dba_tab_partitions where table_owner=user and table_name = 'T' order by partition_position;
PARTITION_NAME INT HIGH_VALUE
------------------------------ --- -------------------------------------------------------------------------------------
P201303 NO TO_DATE(' 2013-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
P201304 NO TO_DATE(' 2013-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
P201305 NO TO_DATE(' 2013-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
SYS_P3209 YES TO_DATE(' 2013-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
--可以发现建立一个新的分区. HIGH_VALUE=TO_DATE(' 2013-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN').
3.删除分区:
SQL> alter table t drop partition P201304;
Table altered.
SQL> alter table t drop partition P201305;
alter table t drop partition P201305
*
ERROR at line 1:
ORA-14758: Last partition in the range section cannot be dropped
--最后一个分区要删除如何处理呢?
4.简单一点是执行如下,先删除interval特性再建立:
alter table t set interval ();
然后在执行.
alter table t set interval (NUMTOYMINTERVAL(1,'month'));
自己测试一下,实际上执行如下:
alter table t set interval (NUMTOYMINTERVAL(1,'month'));
SQL> alter table t set interval (NUMTOYMINTERVAL(1,'month'));
Table altered.
SQL> select partition_name, interval, high_value from dba_tab_partitions where table_owner=user and table_name = 'T' order by partition_position;
PARTITION_NAME INT HIGH_VALUE
------------------------------ --- -------------------------------------------------------------------------------------
P201303 NO TO_DATE(' 2013-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
P201305 NO TO_DATE(' 2013-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
SYS_P3209 NO TO_DATE(' 2013-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
SQL> alter table t drop partition P201305;
Table altered.
--这样就可以很好的解决这个问题.
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/267265/viewspace-760897/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/267265/viewspace-760897/