CODEWITHSUNDEEP
oracle-databaseindexingpartition
Nikita Amin
Nikita Amin

Reputation: 11

how to alter LOCAL partitioned index?

I have this script already running in the production:

CREATE INDEX WZAQXS1A
ON WZAQTS1(PRO_PARTITION)
local
( PARTITION part_WZAQXS1A_74 tablespace wz0qdix1_74
, PARTITION part_WZAQXS1A_76 tablespace wz0qdix1_76
, PARTITION part_WZAQXS1A_77 tablespace wz0qdix1_77
, PARTITION part_WZAQXS1A_78 tablespace wz0qdix1_78
, PARTITION part_WZAQXS1A_7D tablespace wz0qdix1_7D
, PARTITION part_WZAQXS1A_7J tablespace wz0qdix1_7J
, PARTITION part_WZAQXS1A_81 tablespace wz0qdix1_81
, PARTITION part_WZAQXS1A_82 tablespace wz0qdix1_82
, PARTITION part_WZAQXS1A_85 tablespace wz0qdix1_85
, PARTITION part_WZAQXS1A_858 tablespace wz0qdix1_858
, PARTITION part_WZAQXS1A_859 tablespace wz0qdix1_859
, PARTITION part_WZAQXS1A_88 tablespace wz0qdix1_88
, PARTITION part_WZAQXS1A_89 tablespace wz0qdix1_89
, PARTITION part_WZAQXS1A_89M tablespace wz0qdix1_89M
, PARTITION part_WZAQXS1A_8E tablespace wz0qdix1_8E
, PARTITION part_WZAQXS1A_CA tablespace wz0qdix1_CA
, PARTITION part_WZAQXS1A_CC tablespace wz0qdix1_CC
, PARTITION part_WZAQXS1A_CH tablespace wz0qdix1_CH
, PARTITION part_WZAQXS1A_CJ tablespace wz0qdix1_CJ
, PARTITION part_WZAQXS1A_CP tablespace wz0qdix1_CP
, PARTITION part_WZAQXS1A_CS tablespace wz0qdix1_CS
, PARTITION part_WZAQXS1A_CX tablespace wz0qdix1_CX
, PARTITION part_WZAQXS1A_FL tablespace wz0qdix1_FL
, PARTITION part_WZAQXS1A_FT tablespace wz0qdix1_FT
, PARTITION part_WZAQXS1A_FV tablespace wz0qdix1_FV
, PARTITION part_WZAQXS1A_FW tablespace wz0qdix1_FW
, PARTITION part_WZAQXS1A_U9 tablespace wz0qdix1_U9
, PARTITION part_WZAQXS1A_UW tablespace wz0qdix1_UW
, PARTITION part_WZAQXS1A_UX tablespace wz0qdix1_UX
, PARTITION part_WZAQXS1A_YN tablespace wz0qdix1_YN
, PARTITION part_WZAQXS1A_72 tablespace WZ0QDIX1_72
, PARTITION part_WZAQXS1A_KI tablespace WZ0QDIX1_KI
);

i want to add a new partition to it

i try using the following script

alter index 
   WZAQXS1A 
add partition 
   part_WZAQXS1A_KI 
tablespace 
   WZ0QDIX1_KI;

but it gives me this error:

ORA-14076: submitted alter index partition/subpartition operation is not valid for local partitioned index 14076. 00000 - "submitted alter index partition/subpartition operation is not valid for local partitioned index" *Cause: User attempted to either drop, split, add or coalesce a partition or a subpartition of a local index which is illegal. *Action: Ensure that the index named in such statement is a global partitioned index.

how to i add a new partition 'KI'?

Upvotes: 1

Views: 1927

Answers (1)

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59436

You cannot add a partition to a local index. Add (or split) the partition on table, then the index partition will be created automatically.

The new partition will be stored in default tablespace, this you can set by

ALTER INDEX WZAQXS1A MODIFY DEFAULT ATTRIBUTES TABLESPACE WZ0QDIX1_KI;

Or you can afterwards move the index partition like

ALTER INDEX WZAQXS1A REBUILD PARTITION part_WZAQXS1A_KI TABLESPACE WZ0QDIX1_KI;

Upvotes: 1

Related Questions