暂无图片
暂无图片
暂无图片
暂无图片
暂无图片

ORA-14024 number of partitions of LOCAL index must equal that of the underlying table

原创 不吃草的牛_Nick 2023-07-31
1397
[oracle@db02 ~]$ oerr ora 14024
14024, 00000, "number of partitions of LOCAL index must equal that of the underlying table"
// *Cause:  User attempted to create a LOCAL partitioned index with a
//          number of partitions which is different from that of the
//          underlying table.
//* Action: Correct the CREATE INDEX statement to specify a correct
//          number of partitions

https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:9532074300346088715

I have created a plsql program which does the following.

  1. Create Backup tables using script from dbms_metadata.get_ddl utility.
  2. Insert records from Main tables into backup tables.
  3. Rename indexes on Main table
  4. Create Indexes (With same name as Main table) and Constraints on backup tables.

Some of the tables have partitions defined on them. So, when the indexes are getting created I get the following error.

"ORA-14024: number of partitions of LOCAL index must equal that of the underlying table"

That normally happens when you are explicitly naming partitions. Here's an example

conn nick/nick
drop table t purge;

create table t ( x int, y int )
partition by range ( x )
interval (100)
(
  partition p1 values less than ( 0 )
);

insert into t select rownum, rownum from dual connect by level <= 1000;
commit;

select partition_name from user_tab_partitions where table_name = 'T';

PARTITION_NAME
---------------------------------
SYS_P18344
SYS_P18343
SYS_P18342
SYS_P18341
SYS_P18340
SYS_P18339
SYS_P18338
SYS_P18337
SYS_P18336
SYS_P18335
SYS_P18334
P1


create index IX on T ( y  )
local
(
    partition p1,
    partition p2,
    partition p3,
    partition p4,
    partition p5,
    partition p6,
    partition p7,
    partition p8,
    partition p9,
    partition p10,
    partition p11,
    partition p12
);

select PARTITION_NAME from dba_ind_partitions where index_name='IX';

PARTITION_NAME
---------------------------------
P1
P2
P3
P4
P5
P6
P7
P8
P9
P10
P11
P12

So far so good... I created 12 partitions in my table, and I explicitly listed 12 partitions in my index. Now I will drop the index, and add a new row to the table (which creates another partition )

drop index ix;

insert into t  values (2000,2000);
commit;
select partition_name from user_tab_partitions where table_name = 'T';

PARTITION_NAME
--------------------------------------------------------------------------------
P1
SYS_P2647
SYS_P2648
SYS_P2649
SYS_P2650
SYS_P2651
SYS_P2652
SYS_P2653
SYS_P2654
SYS_P2655
SYS_P2656
SYS_P2657
SYS_P2658

13 rows selected.


create index IX on T ( y  )
local
(
    partition p1,
    partition p2,
    partition p3,
    partition p4,
    partition p5,
    partition p6,
    partition p7,
    partition p8,
    partition p9,
    partition p10,
    partition p11,
    partition p12
);

ERROR at line 1:
ORA-14024: number of partitions of LOCAL index must equal that of the underlying table

Now my same DDL does not work, because there are 13 table partitions and I only listed 12 index partitions. 
The easy workaround is not to list the partitions at all.

create index IX on T (y) local;
select PARTITION_NAME from dba_ind_partitions where index_name='IX';

PARTITION_NAME
--------------------------------------------------------------------------------
P1
SYS_P2647
SYS_P2648
SYS_P2649
SYS_P2650
SYS_P2651
SYS_P2652
SYS_P2653
SYS_P2654
SYS_P2655
SYS_P2656
SYS_P2657
SYS_P2658

13 rows selected.

How to resolve IMP-3 ORA-14024 IMP-57 on import [ID 96668.1]



「喜欢这篇文章,您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容,转载时必须标注文章的来源(墨天轮),文章链接,文章作者等基本信息,否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容,欢迎发送邮件至:contact@modb.pro进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。

评论