[20130513]Interval Partition的一些問題.txt

lfree發表於2013-05-13
[20130513]Interval Partition的一些問題.txt

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/,如需轉載,請註明出處,否則將追究法律責任。

相關文章