Database Reference
In-Depth Information
Figure 13-3.
List partition insert example
As we saw for range partitioning, if we try to insert a value that isn't specified in the list partition, Oracle will raise
an appropriate error back to the client application. In other words, a list partitioned table without a
DEFAULT
partition
will implicitly impose a constraint much like a check constraint on the table:
EODA@ORA12CR1> insert into list_example values ( 'VA', 'data' );
insert into list_example values ( 'VA', 'data' )
*
ERROR at line 1:
ORA-14400: inserted partition key does not map to any partition
If we want to segregate these seven states into their separate partitions, as we have, but have all remaining state
codes (or, in fact, any other row that happens to be inserted that doesn't have one of these seven codes) go into
a third partition, then we can use the
VALUES ( DEFAULT )
clause. Here, we'll alter the table to add this partition
(we could use this in the
CREATE TABLE
statement as well):
EODA@ORA12CR1> alter table list_example
2 add partition
3 part_3 values ( DEFAULT );
Table altered.
EODA@ORA12CR1> insert into list_example values ( 'VA', 'data' );
1 row created.
