ORA-30078報錯的兩種解決方案

Hoegh發表於2016-07-12
    最近在嘗試建立分割槽表時遇到了ORA-30078報錯，提示“ORA-30078: 分割槽界限必須是 TIME/TIMESTAMP WITH TIME ZONE 文字”，很納悶兒為什麼會報錯。
   首先我建立一個測試表hoegh1，其中包含一個date型別的時間欄位，並以該欄位做範圍分割槽，建立成功；
   緊接著，嘗試建立測試表hoegh2，其中包含一個timestamp型別的時間欄位，並以該欄位做範圍分割槽，建立失敗，遇到了ORA-30078報錯。
   過程如下：
點選(此處)摺疊或開啟
  1. SQL>
  2. SQL> select * from v$version;

  4. BANNER
  5. ----------------------------------------------------------------
  6. Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
  7. PL/SQL Release 10.2.0.4.0 - Production
  8. CORE 10.2.0.4.0 Production
  9. TNS for 64-bit Windows: Version 10.2.0.4.0 - Production
  10. NLSRTL Version 10.2.0.4.0 - Production

  12. SQL>
  13. SQL>
  14. SQL> create table hoegh1
  15.   2 (
  16.   3 id NUMBER,
  17.   4 time date
  18.   5 )
  19.   6 partition by range(time)
  20.   7 (
  21.   8 partition p_201505 values less than(to_date('20150601','yyyymmdd')),
  22.   9 partition p_201506 values less than(to_date('20150701','yyyymmdd')),
  23.  10 partition p_201507 values less than(to_date('20150801','yyyymmdd')),
  24.  11 partition p_201508 values less than(to_date('20150901','yyyymmdd')),
  25.  12 partition p_201509 values less than(to_date('20151001','yyyymmdd')),
  26.  13 partition p_201510 values less than(to_date('20151101','yyyymmdd')),
  27.  14 partition p_201511 values less than(to_date('20151201','yyyymmdd')),
  28.  15 partition p_201512 values less than(to_date('20160101','yyyymmdd')),
  29.  16 partition p_201601 values less than(to_date('20160201','yyyymmdd')),
  30.  17 partition p_201602 values less than(to_date('20160301','yyyymmdd')),
  31.  18 partition p_201603 values less than(to_date('20160401','yyyymmdd')),
  32.  19 partition p_201604 values less than(to_date('20160501','yyyymmdd')),
  33.  20 partition p_201605 values less than(to_date('20160601','yyyymmdd')),
  34.  21 partition p_201606 values less than(to_date('20160701','yyyymmdd')),
  35.  22 partition p_201607 values less than(to_date('20160801','yyyymmdd'))
  36.  23 );

  38. 表已建立。

  40. SQL>
  41. SQL>
  42. SQL> create table hoegh2
  43.   2 (
  44.   3 id NUMBER,
  45.   4 TIMESTAMP TIMESTAMP(6)
  46.   5 )
  47.   6 partition by range(TIMESTAMP)
  48.   7 (
  49.   8 partition p_201505 values less than(to_timestamp('20150601','yyyymmdd')),
  50.   9 partition p_201506 values less than(to_timestamp('20150701','yyyymmdd')),
  51.  10 partition p_201507 values less than(to_timestamp('20150801','yyyymmdd')),
  52.  11 partition p_201508 values less than(to_timestamp('20150901','yyyymmdd')),
  53.  12 partition p_201509 values less than(to_timestamp('20151001','yyyymmdd')),
  54.  13 partition p_201510 values less than(to_timestamp('20151101','yyyymmdd')),
  55.  14 partition p_201511 values less than(to_timestamp('20151201','yyyymmdd')),
  56.  15 partition p_201512 values less than(to_timestamp('20160101','yyyymmdd')),
  57.  16 partition p_201601 values less than(to_timestamp('20160201','yyyymmdd')),
  58.  17 partition p_201602 values less than(to_timestamp('20160301','yyyymmdd')),
  59.  18 partition p_201603 values less than(to_timestamp('20160401','yyyymmdd')),
  60.  19 partition p_201604 values less than(to_timestamp('20160501','yyyymmdd')),
  61.  20 partition p_201605 values less than(to_timestamp('20160601','yyyymmdd')),
  62.  21 partition p_201606 values less than(to_timestamp('20160701','yyyymmdd')),
  63.  22 partition p_201607 values less than(to_timestamp('20160801','yyyymmdd'))
  64.  23 );
  65.   partition p_201505 values less than(to_timestamp('20150601','yyyymmdd')),
  66.                                       *
  67. 第 8 行出現錯誤:
  68. ORA-30078: 分割槽界限必須是 TIME/TIMESTAMP WITH TIME ZONE 文字


  71. SQL>
    鑑於timestamp型別的時間精度比date型別的要高，因此不能修改列的型別。那怎麼辦呢？在這兒列出兩種解決方案。

一、PARTITION value 型別更改為to_date('20150601','yyyymmdd')

點選(此處)摺疊或開啟
  1. SQL>
  2. SQL> create table hoegh2
  3.   2 (
  4.   3 id NUMBER,
  5.   4 TIMESTAMP TIMESTAMP(6)
  6.   5 )
  7.   6 partition by range(TIMESTAMP)
  8.   7 (
  9.   8 partition p_201505 values less than(to_date('20150601','yyyymmdd')),
  10.   9 partition p_201506 values less than(to_date('20150701','yyyymmdd')),
  11.  10 partition p_201507 values less than(to_date('20150801','yyyymmdd')),
  12.  11 partition p_201508 values less than(to_date('20150901','yyyymmdd')),
  13.  12 partition p_201509 values less than(to_date('20151001','yyyymmdd')),
  14.  13 partition p_201510 values less than(to_date('20151101','yyyymmdd')),
  15.  14 partition p_201511 values less than(to_date('20151201','yyyymmdd')),
  16.  15 partition p_201512 values less than(to_date('20160101','yyyymmdd')),
  17.  16 partition p_201601 values less than(to_date('20160201','yyyymmdd')),
  18.  17 partition p_201602 values less than(to_date('20160301','yyyymmdd')),
  19.  18 partition p_201603 values less than(to_date('20160401','yyyymmdd')),
  20.  19 partition p_201604 values less than(to_date('20160501','yyyymmdd')),
  21.  20 partition p_201605 values less than(to_date('20160601','yyyymmdd')),
  22.  21 partition p_201606 values less than(to_date('20160701','yyyymmdd')),
  23.  22 partition p_201607 values less than(to_date('20160801','yyyymmdd'))
  24.  23 );

  26. 表已建立。

  28. SQL>

二、PARTITION value 型別更改為timestamp'2015-06-01 00:00:00.000000'
點選(此處)摺疊或開啟

  1. SQL>
  2. SQL> drop table hoegh2 purge;

  4. 表已刪除。

  6. SQL> create table hoegh2
  7.   2 (
  8.   3 id NUMBER,
  9.   4 TIMESTAMP TIMESTAMP(6)
  10.   5 )
  11.   6 partition by range(TIMESTAMP)
  12.   7 (
  13.   8 partition p_201505 values less than(timestamp'2015-06-01 00:00:00.000000'),
  14.   9 partition p_201506 values less than(timestamp'2015-07-01 00:00:00.000000'),
  15.  10 partition p_201507 values less than(timestamp'2015-08-01 00:00:00.000000'),
  16.  11 partition p_201508 values less than(timestamp'2015-09-01 00:00:00.000000'),
  17.  12 partition p_201509 values less than(timestamp'2015-10-01 00:00:00.000000'),
  18.  13 partition p_201510 values less than(timestamp'2015-11-01 00:00:00.000000'),
  19.  14 partition p_201511 values less than(timestamp'2015-12-01 00:00:00.000000'),
  20.  15 partition p_201512 values less than(timestamp'2016-01-01 00:00:00.000000'),
  21.  16 partition p_201601 values less than(timestamp'2016-02-01 00:00:00.000000'),
  22.  17 partition p_201602 values less than(timestamp'2016-03-01 00:00:00.000000'),
  23.  18 partition p_201603 values less than(timestamp'2016-04-01 00:00:00.000000'),
  24.  19 partition p_201604 values less than(timestamp'2016-05-01 00:00:00.000000'),
  25.  20 partition p_201605 values less than(timestamp'2016-06-01 00:00:00.000000'),
  26.  21 partition p_201606 values less than(timestamp'2016-07-01 00:00:00.000000'),
  27.  22 partition p_201607 values less than(timestamp'2016-08-01 00:00:00.000000')
  28.  23 );

  30. 表已建立。

  32. SQL>

   雖然問題得到了順利解決，但是為什麼會報錯還是沒弄明白，如果有了解原理的朋友還望不吝賜教。



~~~~~~~ the end~~~~~~~~~
hoegh
2016.07.12

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/30162081/viewspace-2121903/，如需轉載，請註明出處，否則將追究法律責任。

相關文章