Sunday, September 21, 2008

ORA-14120: incompletely specified partition bound for a DATE column

In order to create partitioning table of FORM_AT whenever I try to create a temporary partitioned table name FORM_AT_PART it fails with ORA-14120: incompletely specified partition bound for a DATE column error as below.

SQL>CREATE TABLE "FORM_AT_PART"
( "ID" NUMBER NOT NULL ENABLE,
"AT" VARCHAR2(32) NOT NULL ENABLE,
"CREATED_DATE" TIMESTAMP (3) NOT NULL ENABLE
)partition by range (created_date)
(
partition fa_part1 values less than (to_date('01-JAN-06','DD-MON-YY')) tablespace DATA02,
partition fa_part2 values less than (to_date('01-JUN-06','DD-MON-YY')) tablespace DATA02,
partition fa_part3 values less than (to_date('01-SEP-06','DD-MON-YY')) tablespace DATA02,
partition fa_part4 values less than (to_date('01-DEC-06','DD-MON-YY')) tablespace DATA02,
partition fa_part5 values less than (to_date('01-MAR-07','DD-MON-YY')) tablespace DATA02,
partition fa_part6 values less than (to_date('01-JUN-07','DD-MON-YY')) tablespace DATA02,
partition fa_part7 values less than (to_date('01-SEP-07','DD-MON-YY')) tablespace DATA02,
partition fa_part8 values less than (to_date('01-DEC-07','DD-MON-YY')) tablespace DATA02,
partition fa_part9 values less than (to_date('01-MAR-08','DD-MON-YY')) tablespace DATA02,
partition fa_part10 values less than (to_date('01-JUN-08','DD-MON-YY')) tablespace DATA02,
partition fa_part11 values less than (maxvalue) tablespace DATA02);

partition fa_part1 values less than (to_date('01-JAN-06','DD-MON-YY')) tablespace DATA02,
*
ERROR at line 7:
ORA-14120: incompletely specified partition bound for a DATE column

Cause of The Problem

There may be several causes behind the problem. One reason is you don't use TO_DATE conversion of date column while range partition and you specified date format inside partitioning column does not match with the NLS_DATE_FORMAT setting.

Another reason is starting with 8.0.3, Oracle insists that a partition bound for a DATE
partitioning column is fully specified. Fully specified indicates that it will at least contain day, month and year with 4 digits. Here inside to_date conversion I used 2 digits of date and hence error appears. Instead of '01-JUN-08' we have to use '01-JUN-2008'.

Solution of The Problem
Along with to_date conversion use 4 digits of year for a DATE partitioning column in oracle.
After fixing format it look like below,
SQL>CREATE TABLE "FORM_AT_PART"
( "ID" NUMBER NOT NULL ENABLE,
"AT" VARCHAR2(32) NOT NULL ENABLE,
"CREATED_DATE" TIMESTAMP (3) NOT NULL ENABLE
)partition by range (created_date)
(
partition fa_part1 values less than (to_date('01-JAN-2006','DD-MON-YYYY')) tablespace DATA02,
partition fa_part2 values less than (to_date('01-JUN-2006','DD-MON-YYYY')) tablespace DATA02,
partition fa_part3 values less than (to_date('01-SEP-2006','DD-MON-YYYY')) tablespace DATA02,
partition fa_part4 values less than (to_date('01-DEC-2006','DD-MON-YYYY')) tablespace DATA02,
partition fa_part5 values less than (to_date('01-MAR-2007','DD-MON-YYYY')) tablespace DATA02,
partition fa_part6 values less than (to_date('01-JUN-2007','DD-MON-YYYY')) tablespace DATA02,
partition fa_part7 values less than (to_date('01-SEP-2007','DD-MON-YYYY')) tablespace DATA02,
partition fa_part8 values less than (to_date('01-DEC-2007','DD-MON-YYYY')) tablespace DATA02,
partition fa_part9 values less than (to_date('01-MAR-2008','DD-MON-YYYY')) tablespace DATA02,
partition fa_part10 values less than (to_date('01-JUN-2008','DD-MON-YYYY')) tablespace DATA02,
partition fa_part11 values less than (maxvalue) tablespace DATA02);


Table created.

No comments:

Post a Comment