Hello Tom

I have an index issue with our partitioned table

this table cw_tb_zvlist11 has about 500 mio rows inserted and the insert process is still active

I see that unfortunality we created the primary index as a nonunique one.

Now I try to switch the primary key to another index which is unique,

drop and recreate the index which was nonunique now in unique form, and at the end switch back primary index to this newly created index

this is the plan:

alter table cw_tb_zvlist11 modify primary key using index cwtb_ci_uidx;
drop index cwtb_ri_pk;
create UNIQUE index cwtb_l11_ri_uidx on cw_tb_zvlist11("RESOURCE_ID")
GLOBAL PARTITION BY HASH ("RESOURCE_ID")
PARTITIONS 1024;
alter table cw_tb_zvlist11 modify primary key using index cwtb_l11_ri_uidx;

Now I have two questions

1. is this plan on a very high frequented database the right one to do this online and at the same time while inserting in this table is still active?

2. I get at first step an error ORA-14196. Howe could I solve this one
SQL> alter table cw_tb_zvlist11 modify primary key using index cwtb_ci_uidx;
alter table cw_tb_zvlist11 modify primary key using index cwtb_ci_uidx
*
FEHLER in Zeile 1:
ORA-14196: Angegebener Index kann nicht zur Durchsetzung des Constraints
benutzt werden.
>

create table t (
  x int
);
alter table t add constraint pk primary key (x) using index (
  create index i on t(x)
);

create unique index ui on t(x) invisible online;
alter table t modify primary key using index ui;
alter index i invisible;
alter index ui visible;
drop index i;