How to insert data in partition table which is not defined in partition?

Question

Anybody can tell me how can we insert data in partitioned table which is not satisfying partitioning condition.

Answer 1

Hi P Sharma,

you will get a ORA-14400 error:

SQL> CREATE TABLE t (ID NUMBER)
  2  PARTITION BY RANGE (ID)
  3  (  PARTITION t1 VALUES LESS THAN (100),
  4     PARTITION t2 VALUES LESS THAN (200)
  5  );

Table created

SQL> insert into t values (1);

1 row inserted

SQL> insert into t values (201);

insert into t values (201)
            ~
ORA-14400: inserted partition key does not map to any partition

To avoid this, you could use a default partition with LESS THAN (MAXVALUE):

SQL> CREATE TABLE t (ID NUMBER)
  2  PARTITION BY RANGE (ID)
  3  (  PARTITION t1 VALUES LESS THAN (100),
  4     PARTITION t2 VALUES LESS THAN (200),
  5     PARTITION tmax VALUES LESS THAN (MAXVALUE)
  6  );

Table created

Answer 2

If you're referring to range-partitioned tables and are using 11g, look into defining the tables with interval partitioning. This is similar to range partitioning except that Oracle will create new partitions or split existing partitions automatically for you. If you are on an earlier release then Vincent's suggestion of creating a MAXVALUE partition is the answer for range partitioning.