Interval partitioning was a great feature when it arrived in version 11, because we no longer had to worry so much about ensuring partitions were available for new data when it arrived. Partitions would just be created on the fly as required. I’m not going to talk about interval partition in detail because there’s plenty of good content already out there. But one key element for interval partitioning is that the intervals have to start from somewhere, which is why you always have to define a table with at least one partition.
So what if I want to drop that partition that is the conceptual “starting point”. Well…I get problems 
SQL> create table sales
2 ( tstamp timestamp not null,
3 empno number(10) not null,
4 ename varchar2(10) not null,
5 deptno varchar2(10) not null
6 )
7 partition by range (tstamp)
8 interval( numtoyminterval(1,'YEAR'))
9 (
10 partition p00 values less than (timestamp '2010-01-01 00:00:00')
11 );
Table created.
SQL>
SQL> insert into sales values ( timestamp '2011-01-01 00:00:00' , 0,0,0);
1 row created.
SQL> insert into sales values ( timestamp '2012-01-01 00:00:00' , 0,0,0);
1 row created.
SQL> insert into sales values ( timestamp '2013-01-01 00:00:00' , 0,0,0);
1 row created.
SQL>
SQL> col high_value format a36
SQL> col pname format a10
SQL>
SQL> select
2 partition_name pname,
3 partition_position pos,
4 high_value,
5 interval
6 from user_tab_partitions
7 where table_name = 'SALES';
PNAME POS HIGH_VALUE INT
---------- ---------- ------------------------------------ ---
P00 1 TIMESTAMP' 2010-01-01 00:00:00' NO
SYS_P121 2 TIMESTAMP' 2012-01-01 00:00:00' YES
SYS_P122 3 TIMESTAMP' 2013-01-01 00:00:00' YES
SYS_P123 4 TIMESTAMP' 2014-01-01 00:00:00' YES
4 rows selected.
SQL>
SQL> alter table sales drop partition p00;
alter table sales drop partition p00
*
ERROR at line 1:
ORA-14758: Last partition in the range section cannot be dropped
There is a simple workaround for that issue. If you re-issue the INTERVAL definition for the table, all of the existing partitions will be “upgraded” (or should be it downgraded…I dunno) to being range partitions. Hence we will now have “moved” the starting point, and can then drop the problem partition.
SQL> alter table sales set interval( numtoyminterval(1,'YEAR'));
Table altered.
SQL> select
2 partition_name pname,
3 partition_position pos,
4 high_value,
5 interval
6 from user_tab_partitions
7 where table_name = 'SALES';
PNAME POS HIGH_VALUE INT
---------- ---------- ------------------------------------ ---
P00 1 TIMESTAMP' 2010-01-01 00:00:00' NO
SYS_P121 2 TIMESTAMP' 2012-01-01 00:00:00' NO
SYS_P122 3 TIMESTAMP' 2013-01-01 00:00:00' NO
SYS_P123 4 TIMESTAMP' 2014-01-01 00:00:00' NO
4 rows selected.
SQL> alter table sales drop partition p00;
Table altered.
So that’s all pretty easy, but of course, you must now run this “re-interval” command all the time to be sure that you will always be able to drop any partition you want.
Unless of course….. you’re on 12.2 ! Let’s repeat the demo on 12.2
SQL> create table sales
2 ( tstamp timestamp not null,
3 empno number(10) not null,
4 ename varchar2(10) not null,
5 deptno varchar2(10) not null
6 )
7 partition by range (tstamp)
8 interval( numtoyminterval(1,'YEAR'))
9 (
10 partition p00 values less than (timestamp '2010-01-01 00:00:00')
11 );
Table created.
SQL>
SQL> insert into sales values ( timestamp '2011-01-01 00:00:00' , 0,0,0);
1 row created.
SQL> insert into sales values ( timestamp '2012-01-01 00:00:00' , 0,0,0);
1 row created.
SQL> insert into sales values ( timestamp '2013-01-01 00:00:00' , 0,0,0);
1 row created.
SQL>
SQL> col high_value format a36
SQL> col pname format a10
SQL>
SQL> select
2 partition_name pname,
3 partition_position pos,
4 high_value,
5 interval
6 from user_tab_partitions
7 where table_name = 'SALES';
PNAME POS HIGH_VALUE INT
---------- ---------- ------------------------------------ ---
P00 1 TIMESTAMP' 2010-01-01 00:00:00' NO
SYS_P3415 2 TIMESTAMP' 2012-01-01 00:00:00' YES
SYS_P3416 3 TIMESTAMP' 2013-01-01 00:00:00' YES
SYS_P3417 4 TIMESTAMP' 2014-01-01 00:00:00' YES
4 rows selected.
SQL>
SQL> alter table sales drop partition p00;
Table altered.
SQL>
SQL> select
2 partition_name pname,
3 partition_position pos,
4 high_value,
5 interval
6 from user_tab_partitions
7 where table_name = 'SALES';
PNAME POS HIGH_VALUE INT
---------- ---------- ------------------------------------ ---
SYS_P3415 1 TIMESTAMP' 2012-01-01 00:00:00' NO
SYS_P3416 2 TIMESTAMP' 2013-01-01 00:00:00' YES
SYS_P3417 3 TIMESTAMP' 2014-01-01 00:00:00' YES
3 rows selected.
How cool is that! We now automatically modify one of the interval partitions to being a range partition, so you’ll not get the error.
The little features are often the coolest
