时间戳

首页 > 解决方案 > 为什么它说 ORA-14006：无效的分区名称？

oracle - 为什么它说 ORA-14006：无效的分区名称？

问题描述

我在版本 13 的 Oracle PL/SQL 上使用了此代码

 ALTER TABLE instrument_balance_hist MODIFY  PARTITION BY RANGE( instrument_id )  INTERVAL (100)(
partition p1 VALUE LESS THAN (100),
partition p2 VALUE LESS THAN (200),
partition p3 VALUE LESS THAN (300),
partition p4 VALUE LESS THAN (400),
partition p5 VALUE LESS THAN (500),
partition p6 VALUE LESS THAN (600)
);

instrument_id 的数据类型是 number(6)。

它一直说无效的分区名称，并在“BY”处给出红色下划线。

标签： oracleddldatabase-partitioning

解决方案

将非分区表转换为分区表需要 Oracle 12.2 或更高版本。此外，它是VALUES，不是VALUE。

create table instrument_balance_hist (instrument_id, somecol) as
select 100, 'Kittens' from dual union all
select 200, 'Puppies' from dual;

alter table instrument_balance_hist
modify partition by range (instrument_id) interval (100)(
partition p1 values less than (100),
partition p2 values less than (200),
partition p3 values less than (300),
partition p4 values less than (400),
partition p5 values less than (500),
partition p6 values less than (600)
);

with function highval(t varchar2, p varchar2) return varchar2 as
         hv varchar2(4000);
     begin
         select high_value into hv
         from   user_tab_partitions
         where  table_name = t
         and    partition_name = p;
       
         return hv;
     end highval;
select partition_name
     , highval(p.table_name, p.partition_name) as high_value
from   user_tab_partitions p
where  p.table_name = 'INSTRUMENT_BALANCE_HIST'
/
PARTITION_NAME HIGH_VALUE
-------------- ----------------
P1             100
P2             200
P3             300
P4             400
P5             500
P6             600

6 rows selected

推荐阅读