ORA-14306 and ORA-14400

Oracle apparently introduced list partitioning in Oracle 9. I decided to try it out in an Oracle 11 database.

In this simple example I created a table to store the names of towns in Buckinghamshire, together with the people living in each one:

SQL> create table buckinghamshire_people
  2  (town varchar2(30),
  3   first_name varchar2(30),
  4   last_name varchar2(30))
  5  partition by list (town)
  6  (partition town1 values ('AYLESBURY'),
  7   partition town2 values ('AMERSHAM'))
  8  /

Table created.

SQL>

I inserted a valid row into the table:

SQL> insert into buckinghamshire_people
  2  (town, first_name, last_name)
  3  values
  4  ('AYLESBURY', 'FRED', 'BLOGGS')
  5  /

1 row created.

SQL>

...then I inserted a row for somebody from Slough. It used to be in Buckinghamshire but is now in Berkshire so the table did not have a partition for it. This failed with an ORA-14400:

SQL> insert into buckinghamshire_people
  2  (town, first_name, last_name)
  3  values
  4  ('SLOUGH', 'JOHN', 'SMITH')
  5  /
insert into buckinghamshire_people
            *
ERROR at line 1:
ORA-14400: inserted partition key does not map to any
partition
 
SQL>

I tried to recreate the table with three partitions but the first and third stored rows for the same town. Oracle would not have known where to put rows for people from Aylesbury so it returned an ORA-14306:

SQL> drop table buckinghamshire_people
  2  /

Table dropped.

SQL> create table buckinghamshire_people
  2  (town varchar2(30),
  3   first_name varchar2(30),
  4   last_name varchar2(30))
  5  partition by list (town)
  6  (partition town1 values ('AYLESBURY'),
  7   partition town2 values ('AMERSHAM'),
  8   partition town3 values ('AYLESBURY'))
  9  /
create table buckinghamshire_people
*
ERROR at line 1:
ORA-14306: List value ''AYLESBURY'' specified twice in
partitions 'TOWN1', 'TOWN3'

SQL>