[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.
- Create Backup tables using script from dbms_metadata.get_ddl utility.
- Insert records from Main tables into backup tables.
- Rename indexes on Main table
- 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进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。




