One of the largest tables in our system has its primary key setup as a globally ranged partitioned (in huge, useless, multi-billion value ranges) non-unique index. Its values are just a sequence value and they are definitely unique. I am wanting to change the index to just a normal global (the table is partitioned), unique index.
Can I do that without dropping the constraint and index and then rebuilding – during which time the table would be without this vital index?
Best Answer
You can do this with
DBMS_REDEFINITION
, but you'll need double the space occupied by the table.If I read your question correctly, this should do it:
Note that you'll have to deal with other indexes/objects etc too, using
DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS()
andDBMS_REDEFINITION.REGISTER_DEPENDENT_OBJECT()
.