共计 5622 个字符,预计需要花费 15 分钟才能阅读完成。
最近在尝试创建分区表时遇到了 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>
虽然问题得到了顺利解决,但是为什么会报错还是没弄明白,如果有了解原理的朋友还望不吝赐教。
更多 Oracle 相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx?tid=12
本文永久更新链接地址:http://www.linuxidc.com/Linux/2016-07/133123.htm
正文完
星哥玩云-微信公众号