ORA-14758: Last partition … cannot be dropped

As a DBA, its awesome when you have the Partitioning option at your disposal.  So many cool things suddenly become either possible or easier.  For example, all of a sudden you can separate the data of different time ranges into different partitions. But hand in hand with that, was that fear that one day we’d be getting nasty stares from either managers or customers, when the following happened:

As a DBA, its awesome when you have the partitioning option at your disposal.  So many cool things suddenly become either possible or easier.  But hand in hand with that, was that fear that one day we’d be getting nasty stares from either managers or customers, when the following happened:

SQL> create table T2
  2    ( x date,
  3      y int )
  4  partition by range ( x )
  5  (
  6    partition p1 values less than ( date '2015-06-01' ),
  7    partition p2 values less than ( date '2015-07-01' ),
  8    partition p3 values less than ( date '2015-08-01' ),
  9    partition p4 values less than ( date '2015-09-01' )
 10  )
 11  /

Table created.

SQL>
SQL> insert into T2 values ( sysdate,1);
insert into T2 values ( sysdate,1)
            *
ERROR at line 1:
ORA-14400: inserted partition key does not map to any partition

It is the terrible moment when you realise the job you created to ensure that you had sufficient partitions created either was not run, or had stopped working and you had missed it.  Luckily, a lot of this was solved when INTERVAL partitioning was created in Oracle Database 11g.  All we needed than was a starting point,  an interval (size), and at least one partition:

SQL> create table T1
  2    ( x date,
  3      y int )
  4  partition by range ( x )
  5  interval ( numtoyminterval (1,'MONTH') )
  6  (
  7    partition p1 values less than ( date '2015-06-01' )
  8  )
  9  /

Table created.

SQL>
SQL> insert into T1
  2  select add_months( date '2015-01-01',rownum), rownum
  3  from dual
  4  connect by level <= 10;

10 rows created.

In the example above, I’ve inserted a few rows for 2015 and one row for 2016, and partitions were automatically created for me, as you can see by looking at the dictionary

SQL> select partition_name, high_value
  2  from   user_tab_partitions
  3  where  table_name = 'T1';

PARTITION_NAME       HIGH_VALUE
-------------------- -----------------------------------------------------------------------------------
P1                   TO_DATE(' 2015-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
SYS_P517             TO_DATE(' 2015-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
SYS_P518             TO_DATE(' 2015-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
SYS_P519             TO_DATE(' 2015-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
SYS_P520             TO_DATE(' 2015-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
SYS_P521             TO_DATE(' 2015-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
SYS_P522             TO_DATE(' 2015-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
SYS_P523             TO_DATE(' 2016-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')

That’s very cool, and a lot of people are now using the interval strategy as their default partitioning means.  With Interval partitioning Oracle supports the concept of ‘holes’, where not all partitions have to be created consecutively. In our example we did not insert any values for December 2015, so we have not created a partition for it. However, the partition for January 2016 exists. If we ever insert data for December 2015, the missing partition will be created and the hole goes away; if we don’t then the hole stays forever, which does not matter either.

But eventually, people started to want to archive off their old data, either by switching partitions out, or dropping them.  Now we are seeing the following “What is error  ORA-14758?” come up on our question lists from time to time:

SQL> alter table T1 drop partition P1;
alter table T1 drop partition P1
                              *
ERROR at line 1:
ORA-14758: Last partition in the range section cannot be dropped

To explain what is happening, we need to look at the data dictionary for the type of each partition

SQL> select partition_name, interval
  2  from   user_tab_partitions
  3  where  table_name = 'T1';

PARTITION_NAME       INTERVAL
-------------------- --------
P1                   NO
SYS_P517             YES
SYS_P518             YES
SYS_P519             YES
SYS_P520             YES
SYS_P521             YES
SYS_P522             YES
SYS_P523             YES

When we defined our table, we nominated a starting point, and then a “size” (interval) for each partition (that’s why we required at least one partition at creation time). The error is telling us that we are trying to drop the starting point, and to do so, means than we dont really have a fixed point anymore to base our intervals on.  Notice in the list of partitions above, the first partition is not an interval partition, but it’s the only one of that type, so we cannot drop it.  While this is a nuisance that is addressed in a future release , you can easily address this today – we simply let the database know that the existing partitions can be fixed in place and need no longer be interval based. You basically evolve the interval partitions – whose upper and lower boundaries are mathematical based on the calculation of the starting point – into range partitions with fixed upper bound values.

SQL> alter table T1 set interval ( numtoyminterval (1,'MONTH') );

Table altered.

SQL>
SQL> select partition_name, interval
  2  from   user_tab_partitions
  3  where  table_name = 'T1';

PARTITION_NAME       INT
-------------------- ---
P1                   NO
SYS_P517             NO
SYS_P518             NO
SYS_P519             NO
SYS_P520             NO
SYS_P521             NO
SYS_P522             NO

Simply by restating the interval, we have now marked all of the existing partitions as standard range partitions.  So I can drop all (but one) of them and still have a starting point for my subsequent interval partitions.

SQL> alter table T1 drop partition P1;

Table altered.

There is one little subtlety to this method, though: unlike Interval partitions – which have an upper and a lower bound – range partitions only have an upper bound defined. The lower bound of a range partition is defined by the upper bound of the preceding partition. So by making an interval partition a range partition we are in effect removing the lower bound. In our example we had a non-existing interval partition for December 2015, which eventually would have been created if/when data for it was inserted.

But by marking all existing partitions as range partitions, however, the former interval partition for January 2016 becomes a range partition covering the range for December 2015 and January 2016.  It covers two months. This is intentional – the alternative design would have been to instantiate all missing partitions when marking interval as range partitions – which could have lead to the creation of potentially tens of thousands of unwanted partitions.