In an earlier posting I described how we can play games with primary key indexes in 12c because you can create multiple indexes on a table for the same (ordered) column list provided they have some differences in attributes and only one of them is visible at a time. But how, if you’re not on 12c, can you change a primary key index from unique to non-unique (or vice versa, as this question on OTN wants) without any down-time ?
Of course you can’t “change” the uniqueness of an index – that attribute embedded in the way that Oracle stores rowids for the index – but you can create a new index for the constraint; and you can’t avoid the little bit of “not quite down”-time that it takes to start and finish an online rebuild. But how do you get around the limitation of Oracle error: ORA-01408: such column list already indexed
Easy – though it requires you to do some work you would prefer to avoid – just work through an intermediate step using an index with an extra column. Here’s an example where I start with a non-unique index supporting the PK and end up with a unique index.
create table t1 as select * from all_objects where rownum <= 10000; create index t1_pk on t1(object_id); alter table t1 add constraint t1_pk primary key(object_id); create index t1_i1 on t1(object_id, 0) online; alter table t1 modify primary key using index t1_i1; drop index t1_pk; create unique index t1_pk on t1(object_id) online; alter table t1 modify primary key using index t1_pk; drop index t1_i1;