最近在尝试创建分区表时遇到了ORA-30078报错，提示“ORA-30078: 分区界限必须是 TIME/TIMESTAMP WITH TIME ZONE 文字”，很纳闷儿为什么会报错。
首先我创建一个测试表hoegh1，其中包含一个date类型的时间字段，并以该字段做范围分区，创建成功；
紧接着，尝试创建测试表hoegh2，其中包含一个timestamp类型的时间字段，并以该字段做范围分区，创建失败，遇到了ORA-30078报错。
过程如下：
点击(此处)折叠或打开
-
SQL>
-
SQL> select * from v$version;
-
-
BANNER
-
----------------------------------------------------------------
-
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
-
PL/SQL Release 10.2.0.4.0 - Production
-
CORE 10.2.0.4.0 Production
-
TNS for 64-bit Windows: Version 10.2.0.4.0 - Production
-
NLSRTL Version 10.2.0.4.0 - Production
-
-
SQL>
-
SQL>
-
SQL> create table hoegh1
-
2 (
-
3 id NUMBER,
-
4 time date
-
5 )
-
6 partition by range(time)
-
7 (
-
8 partition p_201505 values less than(to_date(‘20150601‘,‘yyyymmdd‘)),
-
9 partition p_201506 values less than(to_date(‘20150701‘,‘yyyymmdd‘)),
-
10 partition p_201507 values less than(to_date(‘20150801‘,‘yyyymmdd‘)),
-
11 partition p_201508 values less than(to_date(‘20150901‘,‘yyyymmdd‘)),
-
12 partition p_201509 values less than(to_date(‘20151001‘,‘yyyymmdd‘)),
-
13 partition p_201510 values less than(to_date(‘20151101‘,‘yyyymmdd‘)),
-
14 partition p_201511 values less than(to_date(‘20151201‘,‘yyyymmdd‘)),
-
15 partition p_201512 values less than(to_date(‘20160101‘,‘yyyymmdd‘)),
-
16 partition p_201601 values less than(to_date(‘20160201‘,‘yyyymmdd‘)),
-
17 partition p_201602 values less than(to_date(‘20160301‘,‘yyyymmdd‘)),
-
18 partition p_201603 values less than(to_date(‘20160401‘,‘yyyymmdd‘)),
-
19 partition p_201604 values less than(to_date(‘20160501‘,‘yyyymmdd‘)),
-
20 partition p_201605 values less than(to_date(‘20160601‘,‘yyyymmdd‘)),
-
21 partition p_201606 values less than(to_date(‘20160701‘,‘yyyymmdd‘)),
-
22 partition p_201607 values less than(to_date(‘20160801‘,‘yyyymmdd‘))
-
23 );
-
-
表已创建。
-
-
SQL>
-
SQL>
-
SQL> create table hoegh2
-
2 (
-
3 id NUMBER,
-
4 TIMESTAMP TIMESTAMP(6)
-
5 )
-
6 partition by range(TIMESTAMP)
-
7 (
-
8 partition p_201505 values less than(to_timestamp(‘20150601‘,‘yyyymmdd‘)),
-
9 partition p_201506 values less than(to_timestamp(‘20150701‘,‘yyyymmdd‘)),
-
10 partition p_201507 values less than(to_timestamp(‘20150801‘,‘yyyymmdd‘)),
-
11 partition p_201508 values less than(to_timestamp(‘20150901‘,‘yyyymmdd‘)),
-
12 partition p_201509 values less than(to_timestamp(‘20151001‘,‘yyyymmdd‘)),
-
13 partition p_201510 values less than(to_timestamp(‘20151101‘,‘yyyymmdd‘)),
-
14 partition p_201511 values less than(to_timestamp(‘20151201‘,‘yyyymmdd‘)),
-
15 partition p_201512 values less than(to_timestamp(‘20160101‘,‘yyyymmdd‘)),
-
16 partition p_201601 values less than(to_timestamp(‘20160201‘,‘yyyymmdd‘)),
-
17 partition p_201602 values less than(to_timestamp(‘20160301‘,‘yyyymmdd‘)),
-
18 partition p_201603 values less than(to_timestamp(‘20160401‘,‘yyyymmdd‘)),
-
19 partition p_201604 values less than(to_timestamp(‘20160501‘,‘yyyymmdd‘)),
-
20 partition p_201605 values less than(to_timestamp(‘20160601‘,‘yyyymmdd‘)),
-
21 partition p_201606 values less than(to_timestamp(‘20160701‘,‘yyyymmdd‘)),
-
22 partition p_201607 values less than(to_timestamp(‘20160801‘,‘yyyymmdd‘))
-
23 );
-
partition p_201505 values less than(to_timestamp(‘20150601‘,‘yyyymmdd‘)),
-
*
-
第 8 行出现错误:
-
ORA-30078: 分区界限必须是 TIME/TIMESTAMP WITH TIME ZONE 文字
-
-
-
SQL>
鉴于timestamp类型的时间精度比date类型的要高，因此不能修改列的类型。那怎么办呢？在这儿列出两种解决方案。
一、PARTITION value 类型更改为to_date(‘20150601‘,‘yyyymmdd‘)
点击(此处)折叠或打开
-
SQL>
-
SQL> create table hoegh2
-
2 (
-
3 id NUMBER,
-
4 TIMESTAMP TIMESTAMP(6)
-
5 )
-
6 partition by range(TIMESTAMP)
-
7 (
-
8 partition p_201505 values less than(to_date(‘20150601‘,‘yyyymmdd‘)),
-
9 partition p_201506 values less than(to_date(‘20150701‘,‘yyyymmdd‘)),
-
10 partition p_201507 values less than(to_date(‘20150801‘,‘yyyymmdd‘)),
-
11 partition p_201508 values less than(to_date(‘20150901‘,‘yyyymmdd‘)),
-
12 partition p_201509 values less than(to_date(‘20151001‘,‘yyyymmdd‘)),
-
13 partition p_201510 values less than(to_date(‘20151101‘,‘yyyymmdd‘)),
-
14 partition p_201511 values less than(to_date(‘20151201‘,‘yyyymmdd‘)),
-
15 partition p_201512 values less than(to_date(‘20160101‘,‘yyyymmdd‘)),
-
16 partition p_201601 values less than(to_date(‘20160201‘,‘yyyymmdd‘)),
-
17 partition p_201602 values less than(to_date(‘20160301‘,‘yyyymmdd‘)),
-
18 partition p_201603 values less than(to_date(‘20160401‘,‘yyyymmdd‘)),
-
19 partition p_201604 values less than(to_date(‘20160501‘,‘yyyymmdd‘)),
-
20 partition p_201605 values less than(to_date(‘20160601‘,‘yyyymmdd‘)),
-
21 partition p_201606 values less than(to_date(‘20160701‘,‘yyyymmdd‘)),
-
22 partition p_201607 values less than(to_date(‘20160801‘,‘yyyymmdd‘))
-
23 );
-
-
表已创建。
-
-
SQL>
二、PARTITION value 类型更改为timestamp‘2015-06-01 00:00:00.000000‘
点击(此处)折叠或打开
-
SQL>
-
SQL> drop table hoegh2 purge;
-
-
表已删除。
-
-
SQL> create table hoegh2
-
2 (
-
3 id NUMBER,
-
4 TIMESTAMP TIMESTAMP(6)
-
5 )
-
6 partition by range(TIMESTAMP)
-
7 (
-
8 partition p_201505 values less than(timestamp‘2015-06-01 00:00:00.000000‘),
-
9 partition p_201506 values less than(timestamp‘2015-07-01 00:00:00.000000‘),
-
10 partition p_201507 values less than(timestamp‘2015-08-01 00:00:00.000000‘),
-
11 partition p_201508 values less than(timestamp‘2015-09-01 00:00:00.000000‘),
-
12 partition p_201509 values less than(timestamp‘2015-10-01 00:00:00.000000‘),
-
13 partition p_201510 values less than(timestamp‘2015-11-01 00:00:00.000000‘),
-
14 partition p_201511 values less than(timestamp‘2015-12-01 00:00:00.000000‘),
-
15 partition p_201512 values less than(timestamp‘2016-01-01 00:00:00.000000‘),
-
16 partition p_201601 values less than(timestamp‘2016-02-01 00:00:00.000000‘),
-
17 partition p_201602 values less than(timestamp‘2016-03-01 00:00:00.000000‘),
-
18 partition p_201603 values less than(timestamp‘2016-04-01 00:00:00.000000‘),
-
19 partition p_201604 values less than(timestamp‘2016-05-01 00:00:00.000000‘),
-
20 partition p_201605 values less than(timestamp‘2016-06-01 00:00:00.000000‘),
-
21 partition p_201606 values less than(timestamp‘2016-07-01 00:00:00.000000‘),
-
22 partition p_201607 values less than(timestamp‘2016-08-01 00:00:00.000000‘)
-
23 );
-
-
表已创建。
-
-
SQL>
虽然问题得到了顺利解决，但是为什么会报错还是没弄明白，如果有了解原理的朋友还望不吝赐教。
~~~~~~~ the end~~~~~~~~~
hoegh
2016.07.12
ORA-30078报错的两种解决方案
原文：http://blog.itpub.net/30162081/viewspace-2121903/
