Partitioning using CREATE TABLE AS (CTAS) and Column Default Values

This is about the CTAS (Create Table As...) operations during the creation of partitioned tables from normal tables in an Oracle database.

The CTAS operation although copies column constraints such as NULL, NOT NULL from the normal table to the partitioned table during the partitioned table creation, it does not copy DEFAULT VALUEs of the columns. This might lead to a nasty surprise if you are doing RANGE PARTITIONING and the partition key DATE column has a DEFAULT VALUE of SYSDATE in the normal table. This DEFAULT VALUE setting is NOT copied to the partitioned table!

What will happen is, if you do not specify a date explicitly in your INSERT statements you will get an 'ORA-14400: inserted partition key does not map to any partition' error. As the partition key value passed in will be NULL and the partitioned table will NOT know about the DEFAULT value.

Things can be worse if you are using a combination of CTAS to create partitioned tables from normal tables, and then you use an ALTER TABLE ... RENAME TO .. to replace your normal production tables with the new partitioned tables. Let me tell you how. If your application's INSERT statements into these new partitioned tables, do not explicitly specify the date value of the partition key, you will pass in NULLs and hit ORA-14400. Watch out you will suffer production outage!

To fix this problem, you will have to either explicitly change your code to pass in a date value for the partition key column, or alter the partitioned table after CTAS and modify the column to have a default value.

Here is a demonstration on Oracle 10g R2 of how easily this thing can happen.

Create the normal table

conn scott/tiger
Connected.

drop table big_table;

create table big_table
(id number primary key,
subject varchar2(500),
created_date date default sysdate
)
/

insert into big_table (id, subject) values (4,'tset3')
/

1 row created.

commit;

Commit complete.


Create the partitioned table with CTAS from the normal table above, consider using NOLOGGING table creation option to avoid trashing the logs if you think this data is recoverable from elsewhere. This will also create the table faster.

drop table par_big_table
/
-- change dates below appropriately to include the SYSDATE
-- at the time you run this example.

create table par_big_table
partition by range (created_date)
(
partition p200809 values less than (to_date('01-10-2008', 'DD-MM-YYYY')),
partition p200810 values less than (to_date('01-11-2008', 'DD-MM-YYYY'))
)
as
select * from big_table
/

Now try to insert into the new partitioned table without passing the CREATED_DATE value, as you will wrongly assume the new partitioned table will have a DEFAULT VALUE. See how you get the error.

insert into par_big_table (id, subject) values (5,'test4')
/

insert into par_big_table (id, subject) values (5,'test4')
*
ERROR at line 1:
ORA-14400: inserted partition key does not map to any partition

This happens because the value the INSERT statement is passing for the CREATED_DATE column is NULL and the partitioned table doesn't have a DEFAULT VALUE for this column.

To fix this error and stop the production outage you might have caused :-) you have two choices:

* ALTER the new partitioned table and make the column to have a DEFAULT VALUE
* Change the application code to always include a value for the CREATED_DATE

Let's ALTER the table.


alter table par_big_table modify ( created_date date default sysdate);

insert into par_big_table (id, subject) values (5,'test4');

1 row created.

commit;

Commit complete.


Again watch for those DEFAULT VALUES in columns when you are creating partitioned tables with CTAS.