Wednesday, July 21, 2010

Ora-30078 Error Creating A Partition Table With Timestamp As The Range

Partitions on column with datatype timestamp with or without tiem zone information on Oracle 9i and up(If this capability is implemented in the 11g i will edit the article).

You have the following table test and you want to partition it on the d column.
You write the following SQL:

create table test(
a varchar2(5) not null,
d timestamp not null
) partition by range (d) (
partition p1 values less than (to_timestamp('2010-08-15','YYYY-MM-DD')),
partition p_max values less than (maxvalue)
)
;

ORA-30078: partition bound must be TIME/TIMESTAMP WITH TIME ZONE literals

In order to create the table that you must write the SQL as follows:

create table test(
a varchar2(5) not null,
d timestamp not null
) partition by range (d) (
partition p1 values less than (timestamp '2010-08-15 00:00:00'),
partition p_max values less than (maxvalue)
)
;

create table succeeded.

Be careful of the nls_timestamp_format setting of your database.

If your column d was timestamp with time zone the statement would fail with :

ORA-03001: unimplemented feature

In 11g

But if you use the local time zone for column d

create table test(
a varchar2(5) not null,
d timestamp with local time zone not null
) partition by range (d) (
partition p1 values less than (timestamp '2010-08-15 00:00:00 +00:00'),
partition p_max values less than (maxvalue)
)
;

create table succeeded.

Be carefull that when you use the local time zone :
1 Data is normalized to the database time zone when it is stored in the database.
2 When the data is retrieved, users see the data in the session time zone.

No comments:

Post a Comment