本文主要讨論如下：
1.間隔分區表的删除邏輯
2.如何處理ORA-14758報錯
建立間隔分區
create table t_interval
(
a DATE,
b int,
c int
)
PARTITION BY RANGE (a)
INTERVAL (numtodsinterval(3,'day'))
(
PARTITION P1 VALUES LESS THAN (TO_DATE('2018-1-2', 'YYYY-MM-DD'))
);
insert into t_interval values(TO_DATE('2018-01-01 10:00:00', 'YYYY-MM-DD HH24:MI:SS'),1,1);
insert into t_interval values(TO_DATE('2018-01-02 10:00:00', 'YYYY-MM-DD HH24:MI:SS'),1,1);
insert into t_interval values(TO_DATE('2018-01-05 10:00:00', 'YYYY-MM-DD HH24:MI:SS'),1,1);
insert into t_interval values(TO_DATE('2018-01-08 10:00:00', 'YYYY-MM-DD HH24:MI:SS'),1,1);
insert into t_interval values(TO_DATE('2018-01-11 10:00:00', 'YYYY-MM-DD HH24:MI:SS'),1,1);
insert into t_interval values(TO_DATE('2018-01-14 10:00:00', 'YYYY-MM-DD HH24:MI:SS'),1,1);
commit;
09:45:19 SQL> select PARTITION_NAME,HIGH_VALUE,PARTITION_POSITION,INTERVAL from dba_tab_partitions where table_name='T_INTERVAL';
PARTITION_NAME HIGH_VALUE PARTITION_POSITION INTERVAL
-------------------- -------------------------------------------------------------------------------- ------------------ ---------
P1 TO_DATE(' 2018-01-02 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 1 NO
SYS_P2876 TO_DATE(' 2018-01-05 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 2 YES
SYS_P2877 TO_DATE(' 2018-01-08 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 3 YES
SYS_P2878 TO_DATE(' 2018-01-11 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 4 YES
SYS_P2879 TO_DATE(' 2018-01-14 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 5 YES
SYS_P2880 TO_DATE(' 2018-01-17 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 6 YES
6 rows selected.
interval列訓示分區是否是間隔分區。建立表時指定的分區不屬于間隔分區範疇。
更改表的分區間隔
alter table t_interval set interval(NUMTODSINTERVAL(2,'day'));
09:46:57 SQL> select PARTITION_NAME,HIGH_VALUE,PARTITION_POSITION,INTERVAL from dba_tab_partitions where table_name='T_INTERVAL';
PARTITION_NAME HIGH_VALUE PARTITION_POSITION INTERVAL
-------------------- -------------------------------------------------------------------------------- ------------------ ---------
P1 TO_DATE(' 2018-01-02 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 1 NO
SYS_P2876 TO_DATE(' 2018-01-05 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 2 NO
SYS_P2877 TO_DATE(' 2018-01-08 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 3 NO
SYS_P2878 TO_DATE(' 2018-01-11 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 4 NO
SYS_P2879 TO_DATE(' 2018-01-14 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 5 NO
SYS_P2880 TO_DATE(' 2018-01-17 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 6 NO
6 rows selected.
Elapsed: 00:00:00.00
分區間隔更改之後，interval列都變成了NO。
插入資料産生新分區
insert into t_interval values(TO_DATE('2018-01-18 10:00:00', 'YYYY-MM-DD HH24:MI:SS'),1,1);
insert into t_interval values(TO_DATE('2018-01-20 10:00:00', 'YYYY-MM-DD HH24:MI:SS'),1,1);
insert into t_interval values(TO_DATE('2018-01-22 10:00:00', 'YYYY-MM-DD HH24:MI:SS'),1,1);
insert into t_interval values(TO_DATE('2018-01-24 10:00:00', 'YYYY-MM-DD HH24:MI:SS'),1,1);
insert into t_interval values(TO_DATE('2018-01-26 10:00:00', 'YYYY-MM-DD HH24:MI:SS'),1,1);
commit;
09:48:55 SQL> select PARTITION_NAME,HIGH_VALUE,PARTITION_POSITION,INTERVAL from dba_tab_partitions where table_name='T_INTERVAL';
PARTITION_NAME HIGH_VALUE PARTITION_POSITION INTERVAL
-------------------- -------------------------------------------------------------------------------- ------------------ ---------
P1 TO_DATE(' 2018-01-02 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 1 NO
SYS_P2876 TO_DATE(' 2018-01-05 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 2 NO
SYS_P2877 TO_DATE(' 2018-01-08 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 3 NO
SYS_P2878 TO_DATE(' 2018-01-11 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 4 NO
SYS_P2879 TO_DATE(' 2018-01-14 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 5 NO
SYS_P2880 TO_DATE(' 2018-01-17 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 6 NO
SYS_P2881 TO_DATE(' 2018-01-19 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 7 YES
SYS_P2882 TO_DATE(' 2018-01-21 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 8 YES
SYS_P2883 TO_DATE(' 2018-01-23 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 9 YES
SYS_P2884 TO_DATE(' 2018-01-25 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 10 YES
SYS_P2885 TO_DATE(' 2018-01-27 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 11 YES
11 rows selected.
新生成的分區屬于間隔分區。
嘗試删除分區
09:49:26 SQL> alter table t_interval drop partition SYS_P2880;
alter table t_interval drop partition SYS_P2880
*
ERROR at line 1:
ORA-14758: Last partition in the range section cannot be dropped
經過多次嘗試之後發現，發現無法删除間隔分區最後一個為NO的分區。
如果一定要删除最後一個為NO的分區的話，那麼需要把分區表dba_tab_partitions的interval列都變為NO。
方法很簡單，就是指定interval屬性為目前值，再執行一遍：
alter table t_interval set interval(NUMTODSINTERVAL(1,'day'));
然後删除
09:55:32 SQL> alter table t_interval drop partition SYS_P2880;
Table altered.
Elapsed: 00:00:00.01
再插入資料産生新的間隔分區
insert into t_interval values(TO_DATE('2018-02-01 10:00:00', 'YYYY-MM-DD HH24:MI:SS'),1,1);
insert into t_interval values(TO_DATE('2018-02-02 10:00:00', 'YYYY-MM-DD HH24:MI:SS'),1,1);
insert into t_interval values(TO_DATE('2018-02-03 10:00:00', 'YYYY-MM-DD HH24:MI:SS'),1,1);
commit;
抓取表的ddl
set pagesize 0
set long 90000
select dbms_metadata.get_ddl('TABLE','T_INTERVAL','MING') from dual;
10:35:19 SQL> select dbms_metadata.get_ddl('TABLE','T_INTERVAL','MING') from dual;
CREATE TABLE "MING"."T_INTERVAL"
( "A" DATE,
"B" NUMBER(*,0),
"C" NUMBER(*,0)
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
STORAGE(
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CAC
HE DEFAULT)
TABLESPACE "TBS_MING"
PARTITION BY RANGE ("A") INTERVAL (NUMTODSINTERVAL(1,'DAY'))
(PARTITION "SYS_P2877" VALUES LESS THAN (TO_DATE(' 20
18-01-08 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_C
ALENDAR=GREGORIAN')) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 8388608 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS
2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TBS_MING" ,
。。。省略。。。
PARTITION "SYS_P2890" VALUES LESS THAN (TO_DATE(' 2018-01-30 00:
00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREG
ORIAN')) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 8388608 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE
DEFAULT)
TABLESPACE "TBS_MING" ,
PARTITION "SYS_P2891" VALUES LESS THAN (TO_DATE(' 2018-01-31 00:00
:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGOR
IAN')) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 8388608 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DE
FAULT)
TABLESPACE "TBS_MING" )
會發現間隔分區定義并沒有出現在表的ddl定義語句中。當表分區屬性interval變為NO後，分區被轉變成範圍分區，然後才會加入到表的ddl語句中。
總結：
1.間隔分區interval為NO的最後一個分區無法删除，都變為NO的時候，則可以删除任意一個分區
2.alter table t_interval set interval指令可以将interval屬性都變為NO。
3.間隔分區定義不出現在表的ddl語句中，interval變為NO後，才會加入到表的ddl語句中。