间隔分区表的删除逻辑

来源：这里教程网时间：2026-03-03 12:08:34 作者：

本文主要讨论如下： 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语句中。

编辑推荐：

返回列表

间隔分区表的删除逻辑

编辑推荐：

相关推荐

excel表格怎样在数字前输入0

excel表格里怎么替换数据

excel表格里面如何插入圆形

excel表格里怎么添加表格数据透视表

excel表格中怎么添加线边框

excel表格里怎样设置条件自动排序

excel表格例怎样导入数据库

Excel表格如何设置数据排序

excel表格里怎么选择性粘贴快捷键

excel表格怎么插入分隔符号

excel表格怎么设置多行多列数据内容排序

Excel表格如何设置行和列相互转换

excel表格连接公式怎么使用

excel表格怎样链接到cad中

雷神推出 MIX PRO II 迷你主机：基于 Ultra 200H，玻璃上盖 + ARGB 灯效

制造商 Musnap 推出彩色墨水屏电纸书 Ocean C：支持手写笔、第三方安卓应用

最新软件资讯

热文推荐

天极热推