數(shù)據(jù)庫(kù)中間隔分區(qū)表的刪除邏輯

這篇文章主要介紹了數(shù)據(jù)庫(kù)中間隔分區(qū)表的刪除邏輯，具有一定借鑒價(jià)值，感興趣的朋友可以參考下，希望大家閱讀完這篇文章之后大有收獲，下面讓小編帶著大家一起了解一下。

主要討論如下：
1.間隔分區(qū)表的刪除邏輯
2.如何處理ORA-14758報(bào)錯(cuò)

創(chuàng)建間隔分區(qū)
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列指示分區(qū)是否是間隔分區(qū)。創(chuàng)建表時(shí)指定的分區(qū)不屬于間隔分區(qū)范疇。

更改表的分區(qū)間隔
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
分區(qū)間隔更改之后，interval列都變成了NO。

插入數(shù)據(jù)產(chǎn)生新分區(qū)
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.
新生成的分區(qū)屬于間隔分區(qū)。

嘗試刪除分區(qū)
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
經(jīng)過(guò)多次嘗試之后發(fā)現(xiàn)，發(fā)現(xiàn)無(wú)法刪除間隔分區(qū)最后一個(gè)為NO的分區(qū)。

如果一定要?jiǎng)h除最后一個(gè)為NO的分區(qū)的話，那么需要把分區(qū)表dba_tab_partitions的interval列都變?yōu)镹O。
方法很簡(jiǎn)單，就是指定interval屬性為當(dāng)前值，再執(zhí)行一遍：
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
再插入數(shù)據(jù)產(chǎn)生新的間隔分區(qū)
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" )

會(huì)發(fā)現(xiàn)間隔分區(qū)定義并沒(méi)有出現(xiàn)在表的ddl定義語(yǔ)句中。當(dāng)表分區(qū)屬性interval變?yōu)镹O后，分區(qū)被轉(zhuǎn)變成范圍分區(qū)，然后才會(huì)加入到表的ddl語(yǔ)句中。

1.間隔分區(qū)interval為NO的最后一個(gè)分區(qū)無(wú)法刪除，都變?yōu)镹O的時(shí)候，則可以刪除任意一個(gè)分區(qū)
2.alter table t_interval set interval命令可以將interval屬性都變?yōu)镹O。
3.間隔分區(qū)定義不出現(xiàn)在表的ddl語(yǔ)句中，interval變?yōu)镹O后，才會(huì)加入到表的ddl語(yǔ)句中。

感謝你能夠認(rèn)真閱讀完這篇文章，希望小編分享的“數(shù)據(jù)庫(kù)中間隔分區(qū)表的刪除邏輯”這篇文章對(duì)大家有幫助，同時(shí)也希望大家多多支持億速云，關(guān)注億速云行業(yè)資訊頻道，更多相關(guān)知識(shí)等著你來(lái)學(xué)習(xí)!